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

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

CodePudding user response：

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