Subscript out of range Error after renaming sheets

Question

I have done a small project, which consists of 5 excel sheet in, code is working fine and I am getting exact result also, but if I rename sheets from sheet1 to some other name I am getting Subscript out of range Error.

What is the reason for this and what needs to be done to overcome this. Please help.

Below is the code

Public  Sub amount_final()

Dim Row1Crnt As Long
Dim Row2Crnt As Long


With Sheets("sheet4")
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With

Row1Crnt = 2
With Sheets("sheet3")
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With

Answer 1

There is nothing wrong with the code per se. You will get Subscript out of range error if Excel is not able to find a particular sheet which is quite obvious since you renamed it. For example, if you rename your sheet "Sheet3" to "SheetXYZ" then Excel will not be able to find it.

The only way to avoid these kind of errors is to use CODENAME of the sheets. See Snapshot

Here we have a sheet which has a name "Sample Name before Renaming"

So consider this code

Sheets("Sample Name before Renaming").Range("A1").Value = "Blah Blah"

The same code can be written as

Sheet2.Range("A1").Value = "Blah Blah"

Now no matter how many times you rename the sheet, the above code will always work :)

HTH

Sid

Answer 2

The basic issue is that you are referring to sheets using their common names and not their codenames. Whenever you refer to Sheets("sheet4"), you are relying on the sheet having that name in Excel. Codenames are the names assigned in Visual Basic so the end user does not interact with them/as a developer you can change the Excel names any time you like

Using code names is covered at around 9:40 in this Excel help video. You'll note they are quicker to type than the Excel names as do not require the 'Sheets()' qualifier

I couldn't see Sheets("Sheet1") in your code sample but you can switch to codenames for all sheets very quickly by finding/replacing all examples of e.g. 'Sheets("Sheet2").' with 'Sheet2.'

Answer 3

Refer to each sheet by their code names instead. They are set to Sheet1, Sheet2 etc as default, but you can rename them in the Properties window for each sheet if you want. This way you can write your code like below instead, regardless of what you name the sheets.

With Sheet1
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With

Row1Crnt = 2
With Sheet2
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With

etc...

Answer 4

I wanted to share my experience battling this problem. Here is the mistake I committed:

Dim DailyWSNameNew As String
lastrow = Sheets("DailyWSNameNew").Range("A65536").End(xlUp).Row + 1 -- This is wrong as I included a placeholder worksheet name in quotes

Correction:

lastrow = Sheets(DailyWSNameNew).Range("A65536").End(xlUp).Row + 1

This solved it.

Answer 5

I encountered this error earlier today but could not use any solution above, I did however eventually managed to solve it myself.

My situation was that I had a list contained in column A. For each cell with a value I stored the value in a variable, created a new sheet and named the sheet according to the value stored in the variable.

A bit later in the code I tried to select the newly created sheet by using the code:

Sheets(ValueVariable).Select

I encountered the "Subscript out of range" error and I couldn't figure out why. I've used similar code before with success. I did however solve it by casting the variable as a string. Declaring the variable as a string did not seem to work for me.

So, if anyone else encounter this error and want something to try, perhaps this will work for you:

Sheets(Cstr(ValueVariable)).Select