Concatenate Excel Object Name in VBA

[Excel18]

Hello,
I am attempting to create a Microsoft Excel Object (Sheet Name) in VBA using concatenation. I would like to create a macro which in part loops through different sheets by Sheet Number.

For i = 1 To 10
SheetNumber = Sheet & i
...
SheetNumber.Activate

I would like to make "SheetNumber" become the sheet name in the Object panel. It should become Sheet1, Sheet2, Sheet3...

Currently, it concatenates to just i, because Sheet is blank. If I instead change it to:

For i = 1 To 10
SheetNumber = "Sheet" & i
...
SheetNumber.Activate

it becomes "Sheet1", "Sheet2", "Sheet3"... This also gives me an error.

Does anyone have any suggestions? I have to use the concatenation as opposed to Sheets(i).

Thanks!

 

[mikerickson]

You are creating strings, to use a string to refer to a sheet, you have to use the Sheets collection

Dim sheetName as String

For I = 1 to 10
    sheetName = "Sheet" & I

    ThisWorkbook.Sheets(sheetName).Activate

    MsgBox "I'm on sheet " & ActiveSheet.Name
Next i

 

[bhos123]

use Sheets("Sheet" & i).select

 

[Excel18]

Hello and thank you for rour response! This gives me a Run-time error for "Subscript out of range".

 

[mikerickson]

That error suggests that there isn't a sheet named "Sheet1" (or whatever depending on the value of I at the time) in the workbook.