Subscript Out of Range - Update Sheet Name (VBA)

[BigBeachBananas]

I have a workbook that has 10 tabs, some are hidden, but I don't think it matters for my purpose. On the "SQL" tab, I have a mapping of what I want the new sheet name to be (see A14:B24). I gave cell A15 a named range SheetStart for offsetting purposes in the code. You can ignore everything else on the sheet. When I run my macro, the debug.print sheetName is $200,00 which is correct, but on the next line when I assign "Option 1", it says subscript out of range. Why?

Dim SheetStart As Range
        Dim wb2 As Workbook
        Dim sheetName As String
        Dim formattedCell As Range
        Set SheetStart = wb2.Sheets("SQL").Range("SheetStart")
        Set wb2 = ThisWorkbook
        
        wb2.Activate
        For i = 1 To 10
            ' Get the formatted value from the cell
            Set formattedCell = SheetStart.Offset(i - 1, 0)
            sheetName = formattedCell.Text
            ' Remove any invalid characters for sheet names
            sheetName = Replace(Replace(sheetName, "/", "_"), "\", "_")
            ' Check if the sheet name is too long (Excel limits sheet names to 31 characters)
            If Len(sheetName) > 31 Then
                sheetName = Trim(Left(sheetName, 31))
            End If
            Debug.Print sheetName
            Sheets(sheetName).Name = "Option " & i 'subscript error here
            Sheets("Summary").Range("Sheet" & i).Value = "Option " & i
            
        Next i

 

[kevin9999]

Deleted.

 

[BigBeachBananas]

I think the issue is with when I call formattedCell.Text
If I didn't use .Text , then the sheetName would just be 200000 , not formatted correctly. Is there a way around this?

Set formattedCell = SheetStart.Offset(i - 1, 0)
sheetName = formattedCell.Text

 

[bebo021999]

It may cause errors due to the non-existence of some sheets, so try:

On Error Resume Next
Sheets(sheetName).Name = "Option " & i 'subscript error here
On Error Goto 0

 

[BigBeachBananas]

It may cause errors due to the non-existence of some sheets, so try:

On Error Resume Next
Sheets(sheetName).Name = "Option " & i 'subscript error here
On Error Goto 0

Thanks for the reply. If you look at the screenshot in the OP, the sheet "$200,000" does exist. I don't think that's an issue here.

The issue here is why this works.

sheetName = "$200,000"
Sheets(sheetName).Name = "Option " & i

But this doesn't when using cell.Text() method?

sheetName = formattedCell.Text
Sheets(sheetName).Name = "Option " & i

 

[bebo021999]

i from 1 to 3 is OK, but I am not sure for i=4 to 10, whether the sheet name "Option 4" or "Option 10" exists?

 

[BigBeachBananas]

i from 1 to 3 is OK, but I am not sure for i=4 to 10, whether the sheet name "Option 4" or "Option 10" exists?

They're hidden but do exist. The code doesn't get past i=1.

 

[bebo021999]

Could you remove all sensitive data, then upload sample WB onto gg drive?

 

[kevin9999]

Strange. I don't get the error on the same line you do. Firstly, I had to swap these 2 lines around:

Set SheetStart = wb2.Sheets("SQL").Range("SheetStart")
Set wb2 = ThisWorkbook

because you were using the variable wb2 before you set what it was?!

Then when I ran your code, I got the error on the next line after the one you were getting the error on, this line:

Sheets("Summary").Range("Sheet" & i).Value = "Option " & i

which didn't surprise me because I didn't have a range on the Summary sheet called "Sheet1" (I can only assume that you do?)

The code renamed the $200,000 sheet to "Option 1" without any problems, but obviously when I tried to run the code again it errored on the same line as you did - because there was no longer a sheet called $200,000, having already been renamed on the first run...

 

[BigBeachBananas]

Strange. I don't get the error on the same line you do. Firstly, I had to swap these 2 lines around:

Set SheetStart = wb2.Sheets("SQL").Range("SheetStart")
Set wb2 = ThisWorkbook

because you were using the variable wb2 before you set what it was?!

Then when I ran your code, I got the error on the next line after the one you were getting the error on, this line:

Sheets("Summary").Range("Sheet" & i).Value = "Option " & i

which didn't surprise me because I didn't have a range on the Summary sheet called "Sheet1" (I can only assume that you do?)

The code renamed the $200,000 sheet to "Option 1" without any problems, but obviously when I tried to run the code again it errored on the same line as you did - because there was no longer a sheet called $200,000, having already been renamed on the first run...

Thanks for testing the code. Since it worked for you, it helped me figure out what was wrong. Must be somewhere in between so...

The error was due to an extra space in this line

sheetName = Replace(Replace(sheetName, "/", "_"), "\", "_")

This solves it.

sheetName = Trim(Replace(Replace(sheetName, "/", "_"), "\", "_"))