Cannot rename a sheet to the same name as another sheet error

[yerromnitsuj]

I am trying to loop through a list of state names to create new sheets that are templates for each individual state for something I will do later. I get through a lot of states but when I get to Nevada I get the error, "Cannot rename a sheet to the same name as another sheet, a referenced object library, or a workbook referenced by Visual Basic". I don't have any other worksheets in this workbook entitled "NV", which is where the problem comes up, so I'm guessing the issue has to do with a referenced object library or a workbook referenced by Visual Basic, but I'm not sure where to look. Any ideas of how I can find the problem? Here is the code:

Sub Make_State_sheets_second_half_m()
Dim New_sheet As Worksheet
     
For i = Worksheets(SheetA).Range("B3").Value / 2 + 1 To Worksheets(SheetA).Range("B3").Value
    Sheets("X").Select
    Sheets("X").Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = Worksheets(SheetB).Cells(i + 1, 1)
    
Next i
    
Application.DisplayAlerts = False
Sheets("X").Delete
Application.DisplayAlerts = True
End Sub

 

[cstimart]

Maybe revise NV to be "Nev", or use "Nevada"...and manually change later?

 

[yerromnitsuj]

Interesting. I tried changing NV to a few different names and the macro still stops in the same spot, so apparently it's not an issue with the name NV. Well now I'm pretty lost. By the way, when I hit the debug button this is the line it highlights. Don't know if that helps.

ActiveSheet.Name = Worksheets(SheetB).Cells(i + 1, 1)

 

[cstimart]

Interesting. I tried changing NV to a few different names and the macro still stops in the same spot, so apparently it's not an issue with the name NV. Well now I'm pretty lost. By the way, when I hit the debug button this is the line it highlights. Don't know if that helps.

ActiveSheet.Name = Worksheets([COLOR=Red]SheetB[/COLOR]).Cells(i + 1, 1)

Is SheetB a variable? If not, try changing to "SheetB"

 

[yerromnitsuj]

SheetB is a variable, so it shouldn't be that.

 

[cstimart]

SheetB is a variable, so it shouldn't be that.

I dropped your code into excel and the debugger didn't say a word, but the code stops at the For i = line because it believes SheetA to be a variable that is empty.

 

[yerromnitsuj]

Someone else actually wrote the code and I'm just trying to update it. I'm still a bit new to VBA but it shouldn't be the SheetA and SheetB issue since the code works great through all the states until I reach Nevada.

 

[cstimart]

Someone else actually wrote the code and I'm just trying to update it. I'm still a bit new to VBA but it shouldn't be the SheetA and SheetB issue since the code works great through all the states until I reach Nevada.

I'm confused...sorry to ask, but are the SheetA and SheetB and any others declared as Public variables or Constants at the top of the code/module?

 

[yerromnitsuj]

No, I don't see anything. I am not sure how it knows what SheetA, SheetB, etc. are, but it does since all of this person's code in this workbook was written like that and I have been able to get everything to work besides this.

 

[yerromnitsuj]

I figured it out. I'm not sure why I was getting that error for this problem, but i should have been a whole number and since I was dividing B3 on SheetA by 2 and adding 1 and B3 was odd, I was not getting a whole number. I rounded it and everything is working smoothly.