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