Compile Error : Procedure Too Large

[SATYON]

Merhaba, 17 worksheet içeren bir dosyam var. aşağıdaki kodu ve başka birkaç kodu daha dosyamdaki 15 worksheet için ayrı ayrı yazdım ve şimdi procedure too large hatası alıyorum. bu kodu workbooktaki 17 sayfanın yalnızca 15 için çalışacak şekilde sadece 1 kere yazarak nasıl uygulayabilirim.


Private Sub CommandButton1_Click()
a = 5
Sheet5.Cells(1, 1) = "PAZARLAMA-1"
Sheet5.Cells(2, 1) = "Genel Toplam"
Sheet5.Cells(4, 1) = "CARİ İL"
Sheet5.Cells(4, 2) = "CARİ İLÇE"
Sheet5.Cells(4, 3) = "PLASIYER"
Sheet5.Cells(4, 4) = "SATIŞ DESTEK"
Sheet5.Cells(4, 5) = "2007 TL"
Sheet5.Cells(4, 6) = "2007 USD"
Sheet5.Cells(4, 7) = "2007 EURO"
Sheet5.Cells(4, 8) = "2007-2008 Büyüme Oranı"
Sheet5.Cells(4, 9) = "2008 TL"
Sheet5.Cells(4, 10) = "2008 USD"
Sheet5.Cells(4, 11) = "2008 EURO"
Sheet5.Cells(4, 12) = "2008-2009 Büyüme Oranı"
Sheet5.Cells(4, 13) = "2009 TL"
Sheet5.Cells(4, 14) = "2009 USD"
Sheet5.Cells(4, 15) = "2009 EURO"
Sheet5.Cells(4, 16) = "2009-2010 Büyüme Oranı"
Sheet5.Cells(4, 17) = "2010 TL"
Sheet5.Cells(4, 18) = "2010 USD"
Sheet5.Cells(4, 19) = "2010 EURO"
Sheet5.Cells(4, 20) = "2010-2011 Büyüme Oranı"
Sheet5.Cells(4, 21) = "2011 TL"
Sheet5.Cells(4, 22) = "2011 USD"
Sheet5.Cells(4, 23) = "2011 EURO"
Sheet5.Cells(4, 24) = "2011-2012 Büyüme Oranı"
Sheet5.Cells(4, 25) = "2012 TL"
Sheet5.Cells(4, 26) = "2012 USD"
Sheet5.Cells(4, 27) = "2012 EURO"
Sheet5.Cells(4, 28) = "2012-2013 Büyüme Oranı"
Sheet5.Cells(4, 29) = "2013 TL"
Sheet5.Cells(4, 30) = "2013 USD"
Sheet5.Cells(4, 31) = "2013 EURO"
Sheet5.Cells(4, 32) = "2013-2014 Büyüme Oranı"
Sheet5.Cells(4, 33) = "2014 TL"
Sheet5.Cells(4, 34) = "2014 USD"
Sheet5.Cells(4, 35) = "2014 EURO"
Sheet5.Cells(4, 36) = "2014-2015 Büyüme Oranı"
Sheet5.Cells(4, 37) = "2015 TL"
Sheet5.Cells(4, 38) = "2015 USD"
Sheet5.Cells(4, 39) = "2015 EURO"
Sheet5.Cells(4, 40) = "2015-2016 Büyüme Oranı"
Sheet5.Cells(4, 41) = "2016 TL"
Sheet5.Cells(4, 42) = "2016 USD"
Sheet5.Cells(4, 43) = "2016 EURO"
Sheet5.Cells(4, 44) = "2016-2017 Büyüme Oranı"
Sheet5.Cells(4, 45) = "2017 TL"
Sheet5.Cells(4, 46) = "2017 USD"
Sheet5.Cells(4, 47) = "2017 EURO"
Sheet5.Cells(4, 48) = "2017 TL PROJEKSİYON"
Sheet5.Cells(4, 49) = "2017 USD PROJEKSİYON"
Sheet5.Cells(4, 50) = "2017 EURO PROJEKSİYON"
Sheet5.Cells(2, 5) = "=sum(E5:E1000)"
Sheet5.Cells(2, 6) = "=sum(F5:F1000)"
Sheet5.Cells(2, 7) = "=sum(G5:G1000)"
Sheet5.Cells(2, 8) = "=IFERROR(J2/F2,0)"
Sheet5.Cells(2, 9) = "=sum(I5:I1000)"
Sheet5.Cells(2, 10) = "=sum(J5:J1000)"
Sheet5.Cells(2, 11) = "=sum(K5:K1000)"
Sheet5.Cells(2, 12) = "=IFERROR(N2/J2,0)"
Sheet5.Cells(2, 13) = "=sum(M5:M1000)"
Sheet5.Cells(2, 14) = "=sum(N5:N1000)"
Sheet5.Cells(2, 15) = "=sum(O5:O1000)"
Sheet5.Cells(2, 16) = "=IFERROR(R2/N2,0)"
Sheet5.Cells(2, 17) = "=sum(Q5:Q1000)"
Sheet5.Cells(2, 18) = "=sum(R5:R1000)"
Sheet5.Cells(2, 19) = "=sum(S5:S1000)"
Sheet5.Cells(2, 20) = "=IFERROR(V2/R2,0)"
Sheet5.Cells(2, 21) = "=sum(U5:U1000)"
Sheet5.Cells(2, 22) = "=sum(V5:V1000)"
Sheet5.Cells(2, 23) = "=sum(W5:W1000)"
Sheet5.Cells(2, 24) = "=IFERROR(Z2/V2,0)"
Sheet5.Cells(2, 25) = "=sum(Y5:Y1000)"
Sheet5.Cells(2, 26) = "=sum(Z5:Z1000)"
Sheet5.Cells(2, 27) = "=sum(AA5:AA1000)"
Sheet5.Cells(2, 28) = "=IFERROR(AD2/Z2,0)"
Sheet5.Cells(2, 29) = "=sum(AC5:AC1000)"
Sheet5.Cells(2, 30) = "=sum(AD5:AD1000)"
Sheet5.Cells(2, 31) = "=sum(AE5:AE1000)"
Sheet5.Cells(2, 32) = "=IFERROR(AH2/AD2,0)"
Sheet5.Cells(2, 33) = "=sum(AG5:AG1000)"
Sheet5.Cells(2, 34) = "=sum(AH5:AH1000)"
Sheet5.Cells(2, 35) = "=sum(AI5:AI1000)"
Sheet5.Cells(2, 36) = "=IFERROR(AL2/AH2,0)"
Sheet5.Cells(2, 37) = "=sum(AK5:AK1000)"
Sheet5.Cells(2, 38) = "=sum(AL5:AL1000)"
Sheet5.Cells(2, 39) = "=sum(AM5:AM1000)"
Sheet5.Cells(2, 40) = "=IFERROR(AP2/AL2,0)"
Sheet5.Cells(2, 41) = "=sum(AO5:AO1000)"
Sheet5.Cells(2, 42) = "=sum(AP5:AP1000)"
Sheet5.Cells(2, 43) = "=sum(AQ5:AQ1000)"
Sheet5.Cells(2, 44) = "=IFERROR(AW2/AP2,0)"
Sheet5.Cells(2, 45) = "=sum(AS5:AS1000)"
Sheet5.Cells(2, 46) = "=sum(AT5:AT1000)"
Sheet5.Cells(2, 47) = "=sum(AU5:AU1000)"
Sheet5.Cells(2, 48) = "=IFERROR((AS2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Cells(2, 49) = "=IFERROR((AT2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Cells(2, 50) = "=IFERROR((AU2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Range("E4:G4").Interior.Color = RGB(255, 153, 153)
Sheet5.Range("I4:K4").Interior.Color = RGB(255, 204, 153)
Sheet5.Range("M4:O4").Interior.Color = RGB(255, 255, 153)
Sheet5.Range("Q4:S4").Interior.Color = RGB(204, 255, 153)
Sheet5.Range("U4:W4").Interior.Color = RGB(153, 255, 153)
Sheet5.Range("Y4:AA4").Interior.Color = RGB(153, 255, 204)
Sheet5.Range("AC4:AE4").Interior.Color = RGB(153, 255, 255)
Sheet5.Range("AG4:AI4").Interior.Color = RGB(153, 204, 255)
Sheet5.Range("AK4:AM4").Interior.Color = RGB(153, 153, 255)
Sheet5.Range("AO4:AQ4").Interior.Color = RGB(204, 153, 255)
Sheet5.Range("AS4:AU4").Interior.Color = RGB(255, 153, 255)
Sheet5.Range("AV4:AX4").Interior.Color = RGB(255, 153, 204)
Sheet5.Range("A4:AO500").Borders.LineStyle = xlDash
Sheet5.Range("A4:AO500").Borders.Color = RGB(153, 204, 255)
Sheet5.Rows(1).Font.Bold = True
Sheet5.Rows(2).Font.Bold = True
Sheet5.Rows(4).Font.Bold = True
Sheet5.Rows(2).HorizontalAlignment = xlCenter
Sheet5.Rows(4).HorizontalAlignment = xlCenter
Sheet5.Rows(4).WrapText = True
Sheet5.Range("A:XFD").VerticalAlignment = xlCenter
Sheet5.Range("E:G").NumberFormat = "#,##0.00"
Sheet5.Range("H:H").NumberFormat = "%#,##0.00"
Sheet5.Range("I:K").NumberFormat = "#,##0.00"
Sheet5.Range("L:L").NumberFormat = "%#,##0.00"
Sheet5.Range("M:O").NumberFormat = "#,##0.00"
Sheet5.Range("P").NumberFormat = "%#,##0.00"
Sheet5.Range("Q:S").NumberFormat = "#,##0.00"
Sheet5.Range("T:T").NumberFormat = "%#,##0.00"
Sheet5.Range("U:W").NumberFormat = "#,##0.00"
Sheet5.Range("X:X").NumberFormat = "%#,##0.00"
Sheet5.Range("Y:AA").NumberFormat = "#,##0.00"
Sheet5.Range("AB:AB").NumberFormat = "%#,##0.00"
Sheet5.Range("AC:AE").NumberFormat = "#,##0.00"
Sheet5.Range("AF:AF").NumberFormat = "%#,##0.00"
Sheet5.Range("AG:AI").NumberFormat = "#,##0.00"
Sheet5.Range("AJ:AJ").NumberFormat = "%#,##0.00"
Sheet5.Range("AK:AM").NumberFormat = "#,##0.00"
Sheet5.Range("AN:AN").NumberFormat = "%#,##0.00"
Sheet5.Range("AO:AQ").NumberFormat = "#,##0.00"
Sheet5.Range("AR:AR").NumberFormat = "%#,##0.00"
Sheet5.Range("AS:AX").NumberFormat = "#,##0.00"
Sheet5.Rows(4).HorizontalAlignment = xlCenter

sqlcumlepaz1 = " select * from VW_AKSELDOVIZEXFULL_OZET WHERE PLASIYER='HO' ORDER BY CARI_IL "
Set Sirket = Kernel.yeniSirket(vtMSSQL, Sheet1.Cells(3, 2), "TEMELSET", "", TextBox1.Text, TextBox2.Text, 0)

Set paz1 = Kernel.yeniNetRS(Sirket)
paz1.Ac (sqlcumlepaz1)

While Not paz1.EOF

Sheet5.Cells(a, 1) = paz1.Fields(1).AsString
Sheet5.Cells(a, 2) = paz1.Fields(2).AsString
Sheet5.Cells(a, 3) = paz1.Fields(3).AsString
Sheet5.Cells(a, 4) = paz1.Fields(4).AsString
Sheet5.Cells(a, 5).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 5) = paz1.Fields(5).AsFloat
Sheet5.Cells(a, 6).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 6) = paz1.Fields(6).AsFloat
Sheet5.Cells(a, 7).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 7) = paz1.Fields(7).AsFloat
Sheet5.Cells(a, 9).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 9) = paz1.Fields(8).AsFloat
Sheet5.Cells(a, 10).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 10) = paz1.Fields(9).AsFloat
Sheet5.Cells(a, 11).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 11) = paz1.Fields(10).AsFloat
Sheet5.Cells(a, 13).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 13) = paz1.Fields(11).AsFloat
Sheet5.Cells(a, 14).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 14) = paz1.Fields(12).AsFloat
Sheet5.Cells(a, 15).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 15) = paz1.Fields(13).AsFloat
Sheet5.Cells(a, 17).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 17) = paz1.Fields(14).AsFloat
Sheet5.Cells(a, 18).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 18) = paz1.Fields(15).AsFloat
Sheet5.Cells(a, 19).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 19) = paz1.Fields(16).AsFloat
Sheet5.Cells(a, 21).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 21) = paz1.Fields(17).AsFloat
Sheet5.Cells(a, 22).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 22) = paz1.Fields(18).AsFloat
Sheet5.Cells(a, 23).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 23) = paz1.Fields(19).AsFloat
Sheet5.Cells(a, 25).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 25) = paz1.Fields(20).AsFloat
Sheet5.Cells(a, 26).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 26) = paz1.Fields(21).AsFloat
Sheet5.Cells(a, 27).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 27) = paz1.Fields(22).AsFloat
Sheet5.Cells(a, 29).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 29) = paz1.Fields(23).AsFloat
Sheet5.Cells(a, 30).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 30) = paz1.Fields(24).AsFloat
Sheet5.Cells(a, 31).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 31) = paz1.Fields(25).AsFloat
Sheet5.Cells(a, 33).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 33) = paz1.Fields(26).AsFloat
Sheet5.Cells(a, 34).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 34) = paz1.Fields(27).AsFloat
Sheet5.Cells(a, 35).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 35) = paz1.Fields(28).AsFloat
Sheet5.Cells(a, 37).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 37) = paz1.Fields(29).AsFloat
Sheet5.Cells(a, 38).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 38) = paz1.Fields(30).AsFloat
Sheet5.Cells(a, 39).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 39) = paz1.Fields(31).AsFloat
Sheet5.Cells(a, 41).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 41) = paz1.Fields(32).AsFloat
Sheet5.Cells(a, 42).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 42) = paz1.Fields(33).AsFloat
Sheet5.Cells(a, 43).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 43) = paz1.Fields(34).AsFloat
Sheet5.Cells(a, 45).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 45) = paz1.Fields(35).AsFloat
Sheet5.Cells(a, 46).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 46) = paz1.Fields(36).AsFloat
Sheet5.Cells(a, 47).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 47) = paz1.Fields(37).AsFloat
Sheet5.Cells(a, 48).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 49).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 50).NumberFormat = "#,##0.00"

paz1.SonrakiK
a = a + 1
Wend

 

[SATYON]

Merhaba bana bu konu ile ilgili yardımcı olabilecek kimse yok mu acaba?