This For Loop works for the first sheet, but not for the subsequent sheets


Dim a As Integer
    Dim RNG As Range

    For i = 1 To 24
    Set RNG = wksht.Range("F10:F63")
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
     ' Move onto next tab, macro starts again from the beginning for 24 tabs
    Worksheets(ActiveSheet.Index + 1).Select
        Next i
' Goes back to the front sheet

End Sub


All those selects and activesheets make your code slow/fragile/verbose when it doesn’t need to be.

Here’s a version looping through the sheet index numbers.

Sub Example()

Dim StartWsIndex As Integer
Dim EndWsIndex As Integer
Dim CurrentIndex As Integer

StartWsIndex = ThisWorkbook.Sheets("IntangibleAssets").Index
EndWsIndex = WorksheetFunction.Min(StartWsIndex + 23, ThisWorkbook.Sheets.Count())

For CurrentIndex = StartWsIndex To EndWsIndex
    ThisWorkbook.Sheets(CurrentIndex).Range("E10:E63") = ThisWorkbook.Sheets(CurrentIndex).Range("F10:F63").Value
Next CurrentIndex
End Sub

Answered By – user7354796

Answer Checked By – Gilberto Lyons (AngularFixing Admin)

Leave a Reply

Your email address will not be published.