Renaming Worksheets Using VBA

[Steve Belsch]

Hi VBA Experts,

I have a simple VBA code to rename worksheets. I listed it below. I created a list in the Range("C8:C272"). I do not have any duplicate names in the list. However, I am getting this message.

"Run-time error '1004':

That name already taken. Try a different one."

I used conditional formatting to check for duplicates and it doesn't find any. Any idea what could be going wrong?

VBA CODE:

Sub RenameSheets()


Dim c As Range
Dim J As Integer


J = 9
For Each c In Range("C8:C272")


J = J + 1

If Sheets(J).Name = "List" Then J = J + 1
Sheets(J).Name = c.Text

Next c


End Sub

[jolivanes]

It looks like you want to change all the sheets except a sheet names "List" Is that right.
It does not matter which sheet is renamed 1st, 2nd, 3rd etc etc?
How many sheets in the workbook? 266?

[SamT]

Option Explicit

Sub RenameSheets()
Dim c As Range
Dim J As Long

J = 9

For Each c In Range("C8:C272")
If Not Sheets(J).Name = "List" Then 'If the name is "List" then do nothing
J = J + 1
Sheets(J).Name = c.Text
End If
Next c
End Sub

[jolivanes]

It is always nice to hear if a suggestion works but in this case no such luck.