Loop through worksheet names using variables

Hi,

I have an excel workbook containing 50 worksheets. The names are "Scenario51", "Scenario52"...."Scenario100". Each worksheet contain the same number of data and same format, column/row numbers. All I want to do is write an macro to copy those data from each sheet (from B2 to AA32 in each sheet) and paste transpose to a new worksheet (already created in this workbook called "99AA") from top to bottom and each separate by 6 rows high. My coding is:

-------------------------------------------------------------------------------
Sub Test()
Dim Start As Long
Dim Sht As String
For Start = 51 To 100 Step 1

nwRow = (Start - 51) * 26 + 6 * (Start - 51)
Sht = "Scenario" & Start
Worksheets("Sht").Activate
ActiveSheet.Range("B2:AA32").Copy
Worksheets("99AA").Range("D2").Offset(nwRow, 0).PasteSpecial xlPasteValues, Transpose:=True

Next Start

End

End Sub
---------------------------------------------------------
It shows an runtime error 9" subscript out of range. I suspect it might be the incorrect way using the variable to loop through different worksheets? Appreciate for any insights.

Hi Joseph, I tries your suggestion and delete the activate but somehow still showing the runtime error 9....

There is no space in between, the sheet
name shall be Scenario51 without a quotation

There you go! Thanks for help.

Sub Test()
Dim Start As Long
Dim Sht As String
For Start = 51 To 100 Step 1

nwRow = (Start - 51) * 26 + 6 * (Start - 51)
Sht = "Scenario" & Start
Worksheets(Sht).Range("B2:AA32").Copy
Worksheets("99AA").Range("D2").Offset(nwRow, 0).PasteSpecial xlPasteValues, Transpose:=True

Next Start

End

End Sub


Te debugging highlighting staying at Sht = "Scenario" & Start

It works! Sorry, I don's know why it didn't but your instruction is right! Thanks so much for this!!!!!

Alternative setup for looping through all sheets - 1 in a workbook:

Please Login or Register  to view this content.

Alf