How to use VBA for loop

[alex0403]

Dear all,
I have very long excel VBA code below and I am just wondering how to use for loop or anything to make the code shorter and better. Thanks.

Sub S05_0ab_03()

Dim wb As Workbook, wbTemp As Workbook
Dim ws As Worksheet, wsTemp As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
Set ws3 = wb.Sheets("Sheet3")
Set ws4 = wb.Sheets("Sheet4")
Set ws5 = wb.Sheets("Sheet5")
Set ws6 = wb.Sheets("Sheet6")
Set ws7 = wb.Sheets("Sheet7")
Set ws8 = wb.Sheets("Sheet8")
Set ws9 = wb.Sheets("Sheet9")
Set ws10 = wb.Sheets("Sheet10")
Set ws11 = wb.Sheets("Sheet11")
Set ws12 = wb.Sheets("Sheet12")
Set ws13 = wb.Sheets("Sheet13")
Set ws14 = wb.Sheets("Sheet14")
Set ws15 = wb.Sheets("Sheet15")
Set ws16 = wb.Sheets("Sheet16")
Set ws17 = wb.Sheets("Sheet17")
Set ws18 = wb.Sheets("Sheet18")
Set ws19 = wb.Sheets("Sheet19")
Set ws20 = wb.Sheets("Sheet20")
Set ws21 = wb.Sheets("Sheet21")
Set ws22 = wb.Sheets("Sheet22")
Set ws23 = wb.Sheets("Sheet23")
Set ws24 = wb.Sheets("Sheet24")
Set ws25 = wb.Sheets("Sheet25")
Set ws26 = wb.Sheets("Sheet26")
Set ws27 = wb.Sheets("Sheet27")
Set ws28 = wb.Sheets("Sheet28")
Set ws29 = wb.Sheets("Sheet29")
Set ws30 = wb.Sheets("Sheet30")
Set ws31 = wb.Sheets("Sheet31")
Set ws32 = wb.Sheets("Sheet32")
Set ws33 = wb.Sheets("Sheet33")
Set ws34 = wb.Sheets("Sheet34")


'~~> Change path as applicable
Set wbTemp = Workbooks.Open("C:\Users\cl0106.UNT\Documents\Jill\0ab\S05_0ab_03.xlsx")
Set wsTemp = wbTemp.Sheets("Sheet1")


'copy file
wsTemp.Range("A3:A102").copy ws.Range("I3:I102")
wsTemp.Range("B3:B102").copy ws2.Range("I3:I102")
wsTemp.Range("C3:C102").copy ws3.Range("I3:I102")
wsTemp.Range("D3:D102").copy ws4.Range("I3:I102")
wsTemp.Range("E3:E102").copy ws5.Range("I3:I102")
wsTemp.Range("F3:F102").copy ws6.Range("I3:I102")
wsTemp.Range("G3:G102").copy ws7.Range("I3:I102")
wsTemp.Range("H3:H102").copy ws8.Range("I3:I102")
wsTemp.Range("I3:I102").copy ws9.Range("I3:I102")
wsTemp.Range("J3:J102").copy ws10.Range("I3:I102")
wsTemp.Range("K3:K102").copy ws11.Range("I3:I102")
wsTemp.Range("L3:L102").copy ws12.Range("I3:I102")
wsTemp.Range("M3:M102").copy ws13.Range("I3:I102")
wsTemp.Range("N3:N102").copy ws14.Range("I3:I102")
wsTemp.Range("O3:O102").copy ws15.Range("I3:I102")
wsTemp.Range("P3:P102").copy ws16.Range("I3:I102")
wsTemp.Range("Q3:Q102").copy ws17.Range("I3:I102")
wsTemp.Range("R3:R102").copy ws18.Range("I3:I102")
wsTemp.Range("S3:S102").copy ws19.Range("I3:I102")
wsTemp.Range("T3:T102").copy ws20.Range("I3:I102")
wsTemp.Range("U3:U102").copy ws21.Range("I3:I102")
wsTemp.Range("V3:V102").copy ws22.Range("I3:I102")
wsTemp.Range("W3:W102").copy ws23.Range("I3:I102")
wsTemp.Range("X3:X102").copy ws24.Range("I3:I102")
wsTemp.Range("Y3:Y102").copy ws25.Range("I3:I102")
wsTemp.Range("Z3:Z102").copy ws26.Range("I3:I102")
wsTemp.Range("AA3:AA102").copy ws27.Range("I3:I102")
wsTemp.Range("AB3:AB102").copy ws28.Range("I3:I102")
wsTemp.Range("AC3:AC102").copy ws29.Range("I3:I102")
wsTemp.Range("AD3:AD102").copy ws30.Range("I3:I102")
wsTemp.Range("AE3:AE102").copy ws31.Range("I3:I102")
wsTemp.Range("AF3:AF102").copy ws32.Range("I3:I102")
wsTemp.Range("AG3:AG102").copy ws33.Range("I3:I102")
wsTemp.Range("AH3:AH102").copy ws34.Range("I3:I102")




Application.CutCopyMode = False


'~~> Cleanup
wbTemp.Close savechanges:=True
Set wb = Nothing: Set wbTemp = Nothing
Set ws = Nothing: Set ws2 = Nothing: Set wsTemp = Nothing






End Sub

 

[wigi]

Sub S05_0ab_03()

    Application.ScreenUpdating = False


    With Workbooks.Open("C:\Users\cl0106.UNT\Documents\Jill\0ab\S05_0ab_03.xlsx").Sheets("Sheet1")
        For i = 1 To 34
            .Range("A3:A102").Offset(, i - 1).Copy ThisWorkbook.Worksheets("Sheet" & i).Range("I3:I102")
        Next
        .Parent.Close 0
    End With


    Application.ScreenUpdating = True


End Sub

 

[wigi]

PS: Please use code tags when you paste VBA code on the forum.
To add code tags, you can click the # icon and paste your code.


Or, a manual approach is, to type in your post:

then paste your code, and lastly, type:


['/code]


WITHOUT the single quotation mark ' in front of the /


Thanks for the consideration.