Why is this VBA Code repeating itself?

[valmir]

Hello everyone! Trying to learn the basics of VBA codes. This one, I expected to run on the first 16 sheets of this worksheet, instead, it keeps repeating itself on the first one. Can anyone please correct it and give me a little explanation on what is wrong? Thanks!

Sub Copy_Paste_Apps_CA()
Dim i as Long
For i = 1 to 16
    Windows("zStats test.xlsx").Activate
    Range("G5:G42").Select
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("G5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("I5:I42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("I5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("K5:K42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("K5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("M5:M42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("M5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Windows("zStats test.xlsx").Activate
    Range("G48:P52").Select
    Range("P52").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Stats 2010.xlsx").Activate
    Range("G48").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Range("F5:Q42").Select
    Range("Q42").Activate
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("G5:G42,I5:I42,K5:K42,M5:M42,O5:O42,Q5:Q42").Select
    Range("Q5").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("C44").Select
Next i
End Sub

 

[Joe4]

Hi Joe! Happy new year! I hope you can help me with this one. I made this macro recording for this particular workbook named "Stats 2021-22". However, I need to have it work in multiple worksheets and I have to keep changing the worksheet name in order to make it work. So, my question is: is there a way to change the worksheet name so that it works in any worksheet regardless of the name? The worksheet name appears two times:

Since this is a new question, you should post it to a new thread. That way it will appear as a new unanswered question and show up on the "Unanswered threads" listing, where is where a lot of people go to look for new questions to work on.

 

[valmir]

Since this is a new question, you should post it to a new thread. That way it will appear as a new unanswered question and show up on the "Unanswered threads" listing, where is where a lot of people go to look for new questions to work on.

Ok thanks!