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.

 

[jstanberry]

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

That is working it names everything. Though it leaves the first unused 1 at 0 but each one after is named until it errors out.

 

[Fluff]

it leaves the first unused 1 at 0

In what way?

until it errors out.

What error do you get?

 

[jstanberry]

In what way?

What error do you get?

It gets gives the same error.

 

[Fluff]

You should only get error if two of the sheets have the same value in A11

 

[jstanberry]

You should only get error if two of the sheets have the same value in A11

I am. Its not a big deal. This will work.

 

[Fluff]

Ok, if your happy