Iterate through sheets with for loop incrementing

Question

I am new to VBA and having some trouble with my code. I have sheets named 2001, 2002, 2003... 2010. I want to pick each of these sheets in turn and perform some simple operations on each worksheet. I have tried to use a for loop, incrementing from 2000 to 2010 but it is getting stuck. I think the problem is I don't know how to specify the sheet name that it should pick using the for loop.

Sub Compile_data_from_worksheets()

Dim i As Integer   ' declare i as integer, this will count the number of Manufacturers
Dim y As Long      ' declare y as long, this will capture the number of rows in a worksheet
Dim x As String    ' declare x as string, this will capture the name of each Manufacturer

Application.StatusBar = "running macro"    ' places message on the statusbar while macro runs

ThisWorkbook.Activate

Application.ScreenUpdating = False    'stop the screen updating as the macro runs
Application.CutCopyMode = False       'clears the clipboard


For i = 2000 To 2002              ' for each of the years in turn

Sheets(i).Select          ' activate worksheet 2000
Range("A17").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Worksheets("Combined_data").Activate

y = ActiveSheet.UsedRange.Rows.Count        'count how many rows of worksheet "AllData_Raw" have been used so far.

Cells(y + 1, 1).Select          'use the Cells command to select data and then paste selection

ActiveSheet.Paste

Next i                          ' next year...

End Sub
}

score 0 · Accepted Answer · edited May 23 '17 at 11:44

The issue is that if an integer is placed inside the Sheets(), it assumes you are looking for the index, as in the 2000th sheet not Sheet 2000.

Change it to

Sheets(cstr(i)).Select

That being said, you really should work through your code and remove the .select by referencing the cells directly. A good resource is here. It will greatly speed up and help ensure fewer errors.

So your For loop would be as such

For i = 2000 To 2002              ' for each of the years in turn
    With Sheets(CStr(i))
        .Range(.Range("A17"), .Range("A17").End(xlDown).End(xlToLeft)).Copy
    End With
    With Worksheets("Combined_data")
        y = .UsedRange.Rows.Count        'count how many rows of worksheet "AllData_Raw" have been used so far.
        .Cells(y + 1, 1).Paste
    End With
Next i

Iterate through sheets with for loop incrementing

1 Answers1