Sheets("Sheet" & i).Select - Subscript Out of Range

[ransomedbyfire]

I am trying to convert all my sheet references in my code to the format "Sheet1", etc. instead of "Sheets(1)", etc. to avoid getting my sheet numbers mixed up when things change in the spreadsheet.

I wrote a simple piece of code to try to get the syntax right. It worked in the almost empty workbook where I was testing it; but then when I tried to copy it into the spreadsheet I am actually working on, it gave me a "Subscript out of range" error. So, I closed the spreadsheet and went back to my test spreadsheet and re-ran the code, and it didn't work there either.

What's going on here? And how can I make it work every time?

Here is my test code.

Sub test2()

For i = 1 To 3
Sheets("Sheet" & i).Select
ActiveSheet.Range("b1") = "Sheet" & i
Next i

End Sub

 

[Tom Urtis]

You don't need to select the sheets if you want to manipulate their cells or ranges, so you could do

For i = 1 to 3
with Sheets(i)
.range("B1").Value = .name
end with
next i

 

[ransomedbyfire]

Tom, I know that selecting the sheet is usually unnecessary; but this is what it took to get the code to work the first time. And, like I said, I am trying to get away from using the "Sheets(i)" syntax because it has started causing me some problems. (I found out earlier that my Sheets(10) is now Sheets(13) and that those numbers in the parnetheses can change. This is causing me a lot of annoyance; so, I am trying to find a more dependable syntax.

 

[Tom Urtis]

What exactly are you after? If you want to select the sheet whose tab is named Sheet1, then just write
Sheets("Sheet1").Select

or, refer to a sheet in a more reliable way by referring to its code name as seen in the Explorer window of the VBE.

If you are wanting to refer to sheet tab names using an integer variable, you can use a string variable to help, like this which works for me. Run this and watch your sheet tabs become activated at each message box pop-up:

Sub test2()
Dim xSheet As String, i As Integer
For i = 1 To 3
xSheet = "Sheet" & i
Sheets(xSheet).Activate
MsgBox "You are on the sheet whose tab name is " & xSheet
Next i
End Sub

If you cannot activate a sheet because it is hidden, use this:

Sub test3()
Dim xSheet As String, i As Integer
For i = 1 To 3
xSheet = "Sheet" & i
If Sheets(xSheet).Visible = False Then
MsgBox xSheet & " is hidden and cannot be activated."
Else
Sheets(xSheet).Activate
MsgBox "You are on the sheet whose tab name is " & xSheet
End If
Next i
End Sub

If you cannot activate a sheet because it is either hidden or does not exist, use this catch-all:

Sub test4()
Dim xSheet As String, i As Integer, x As Variant
For i = 1 To 3
xSheet = "Sheet" & i
On Error Resume Next
x = Sheets(xSheet).Name
If Err.Number <> 0 Then
Err.Clear
MsgBox xSheet & " does not exist."
ElseIf Sheets(xSheet).Visible = False Then
MsgBox xSheet & " is hidden and cannot be activated."
Else
Sheets(xSheet).Activate
MsgBox "You are on the sheet whose tab name is " & xSheet
End If
Next i
End Sub

 

[ransomedbyfire]

Oh - okay. That first block of code looks very promising. I hadn't thought of the whole idea of making an extra variable (xsheet) - partly because I didn't realize Excel would freak out over me putting its contents directly into the code . Thank you very much!