Renaming Tabs by Cell Entry with Error Rename

jstanberry

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,662
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Is A11 on the unused sheets blank?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,301
Office Version
  1. 2010
Platform
  1. Windows
No need obviously any 'error statement' just checking if the worksheet name already exists …​
 

jstanberry

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,662
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,662
Office Version
  1. 365
Platform
  1. Windows
What is the error message? & what is in the cell when the error occurs?
 

jstanberry

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,662
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 
Solution

Forum statistics

Threads
1,141,863
Messages
5,709,053
Members
421,611
Latest member
Lisa W

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top