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

Question

Dim a As Integer
    Dim RNG As Range


    For i = 1 To 24
    
    Sheets("IntangibleAssets").Select
    Set RNG = wksht.Range("F10:F63")
    
    Selection.Copy
    Range("E10").Select
    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

    Sheets("Summary").Select
   
    
    
   
End Sub

That is because you make `IntangibleAssets` the active sheet at the start of every loop. — Scott Craner, Mar 29 '22 at 15:12
This should not work at all, unless we are missing some code. `wksht` is not declared or set and as such `Set RNG = wksht.Range("F10:F63")` should error. — Scott Craner, Mar 29 '22 at 15:16
Required reading: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Mar 29 '22 at 15:21

score 1 · Accepted Answer · answered Mar 29 '22 at 15:32

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

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

1 Answers1