Renaming Tabs by Cell Entry with Error Rename

[jstanberry]

Ok. I have a work spreadsheet I use for estimating construction jobs. I have to price based on Item numbers. I currently have a work book in Excel that will rename the tabs base off of a Cell entry for that worksheet. What I want to do is if I don't use all of the tabs to name the unused ones something else but generic.

Lets say the cell I name the tab from pulls data from a Bid Items sheet. So I have 100 tabs currently and if I use all 100 numbering items uniquely it is fine works with out a problem. But if I clear the data in the Bid Items Sheet and just say use 10 items. I am only naming the 1st 10 work sheet tabs 1-10, I want to name the other 90 using an error code because the Macro errors out because it sees multiple tabs with same name so it ends up leaving the tabs what ever was last used to name it. I want to be able to do the following

1. Name any where from 1-100 tabs based on a cell value
2. Rename an unused tabs Not Used with a number to make it unique. So and unused tabs that have no value in the reference cell will Get name Not Used 1, Not Used 2, Not Used 3 and so on until all tabs are named.

This is the code I am working with:

Sub RenameSheet()

    Dim rs As Worksheet

    For Each rs In Sheets

    rs.Name = rs.Range("A11")

    Next rs

    On Error GoTo Badname
   
    Exit Sub

Badname:

    For ws = 1 To Worksheets.Count

    Sheets(ws).Name = "NotUSed" & nmbr

    nmbr = nmbr + 1

    Next ws

    End If

End Sub

Edit: Last note code before BADNAME works on its own until a tab produces a duplicate name. This is why I want to have an error statement to rename the remaining tabs.

 

[Fluff]

Hi & welcome to MrExcel.
Is A11 on the unused sheets blank?

 

[Marc L]

​

No need obviously any 'error statement' just checking if the worksheet name already exists …​

 

[jstanberry]

Hi & welcome to MrExcel.
Is A11 on the unused sheets blank?

Yes. So after the last "unique" name all A11s are blank. So i am looking for a way to name them and I found a code that would name them using a "name" + a number and the number would increase by 1 for each tab so no tab has same name.

 

[jstanberry]

​

No need obviously any 'error statement' just checking if the worksheet name already exists …​

I get that but I want it to name the unused tabs but by a set name and increasing number. Example. Once all tabs I use are named when the macro gets to a cell that is blank on the worksheet it will name the worksheet "Not_Used_1" and then each unused tab will increase the number by 1 so the next will be "Not_Used_2" and so forth on down the line.

 

[Fluff]

Ok, how about

Sub jstanberry()
   Dim Ws As Worksheet
   Dim i As Long
   
   For Each Ws In Worksheets
      If Ws.Range("A11") = "" Then
         i = i + 1
         Ws.Name = "Not used " & i
      Else
         Ws.Name = Ws.Range("A11").Text
      End If
   Next Ws
End Sub

 

[jstanberry]

I get that but I want it to name the unused tabs but by a set name and increasing number. Example. Once all tabs I use are named when the macro gets to a cell that is blank on the worksheet it will name the worksheet "Not_Used_1" and then each unused tab will increase the number by 1 so the next will be "Not_Used_2" and so forth on down the line.

Ok, how about

Sub jstanberry()
   Dim Ws As Worksheet
   Dim i As Long
  
   For Each Ws In Worksheets
      If Ws.Range("A11") = "" Then
         i = i + 1
         Ws.Name = "Not used " & i
      Else
         Ws.Name = Ws.Range("A11").Text
      End If
   Next Ws
End Sub

Gives me an error and debug points to
Ws.Name = Ws.Range("A11").Text

 

[Fluff]

What is the error message? & what is in the cell when the error occurs?

 

[jstanberry]

What is the error message? & what is in the cell when the error occurs?

Run-time error "1004":
That name is already taken. Try a different one.

Cell A11 is blank.

 

[Fluff]

Ok, how about

Sub jstanberry()
   Dim Ws As Worksheet
   Dim i As Long
   
   For Each Ws In Worksheets
      i = i + 1
      Ws.Name = "azd " & i
   Next Ws
   i = 0
   For Each Ws In Worksheets
      If Ws.Range("A11") = "" Then
         i = i + 1
         Ws.Name = "Not used " & i
      Else
         Ws.Name = Ws.Range("A11").Text
      End If
   Next Ws
End Sub