Invalid index after renaming sheets

Question

I did a small project consisting of 5 excel sheets, the code works fine, and I also get the exact result, but if I rename the sheets from sheet1 to some other name, I get an error message out of range.

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. You will get a Subscript out of range error if Excel cannot find a specific worksheet, which is pretty obvious since you renamed it. For example, if you rename your sheet "Sheet3" to "SheetXYZ", Excel cannot find it.

The only way to avoid such errors is to use CODENAME for sheets. See Snapshot

Here we have a sheet that has the 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 :)

NTN

Sid

Answer 2

The main problem is that you are referencing the sheets using their common names, not their code names. Whenever you refer to sheets ("sheet4"), you rely on a sheet that has this name in Excel. Codenames are names assigned in Visual Basic, so the end user does not interact with them / as a developer, you can change Excel names at any time

Using code names spans around 9:40 in this Excel reference video . You will notice that they are faster to type than Excel names, since the 'Sheets ()' qualifiers are not required.

I could not see the sheets ("Sheet1") in your sample code, but you can very quickly switch to the code names for all sheets by finding / replacing all the examples, for example. 'Sheets ("Sheet2").' with 'Sheet2.'

Answer 3

Refer to each sheet for their code name. They are set as Sheet1, Sheet2, etc. By default, but you can rename them in the Properties window for each sheet if you wish. That way, you can write your code as shown below, no matter what you call 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 struggling with this problem. Here is the error I made:

 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 resolved it.

Answer 5

Today I encountered this error, but could not use any solution above, but I still managed to solve it myself.

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

A little later in the code, I tried to select a newly created sheet using the code:

 Sheets(ValueVariable).Select 

I encountered a "Subcategory out of range" error, and I could not understand why. I used to use similar code with success. However, I solved it by exposing the variable as a string. Declaring a variable as a string does not seem to work for me.

So, if anyone else encounters this error and wants to try something, it might work for you:

 Sheets(Cstr(ValueVariable)).Select