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.
 

jstanberry

New Member
Joined
May 5, 2021
Messages
8
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
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,669
Office Version
  1. 365
Platform
  1. Windows
You should only get error if two of the sheets have the same value in A11
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,669
Office Version
  1. 365
Platform
  1. Windows
Ok, if your happy (y)
 

Forum statistics

Threads
1,141,869
Messages
5,709,092
Members
421,615
Latest member
RAB29

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