Forum Discussion
I would like to rename all the sheets from a list of names I have
- Jun 15, 2018
Hi Hursh,
You can do this by using a VBA code!
But you need to have these name listed in a worksheet stating from cell A1 as follows:
Then you can depend on this code to rename all worksheets at once:
Sub rename()
Dim r As Integer
r = 1
For Each Sheet In Sheets
Sheet.Name = Cells(r, 1).Value
r = r + 1
Next
End SubThis code will loop through each worksheet and rename the Sheet1 with the name of cell A1, Sheet2 with the name of cell A2, and so on.
Please check this link to learn how to insert and run this code.
Regards
Hi Hursh,
You can do this by using a VBA code!
But you need to have these name listed in a worksheet stating from cell A1 as follows:
Then you can depend on this code to rename all worksheets at once:
Sub rename()
Dim r As Integer
r = 1
For Each Sheet In Sheets
Sheet.Name = Cells(r, 1).Value
r = r + 1
Next
End Sub
This code will loop through each worksheet and rename the Sheet1 with the name of cell A1, Sheet2 with the name of cell A2, and so on.
Please check this link to learn how to insert and run this code.
Regards
- ShiffaDec 29, 2024Copper Contributor
This code worked for me perfectly.
- mjohnston02090819Jun 12, 2023Copper ContributorWhen I use this exact code with the list of values shown, I get Run-time error '1004': Application-defined or object-defined error.
- HansVogelaarJun 12, 2023MVP
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- mjohnston02090819Jun 12, 2023Copper Contributor
How do you begin renaming sheets at Sheet 3? The first sheet is a summary sheet with a list project numbers I want to rename each sheet to and the second sheet is for rates. The list begins in Cell A2 and progresses downward. In addition, there will be two sheets per project: one for man hours and one for cost. For example, if cell A2 on the summary sheet contains the project number 12345, then Sheet 3's name would be 12345 Hrs and Sheet 4's name would be 12345 Cost. If cell A3 on the Summary sheet had 67890, the Sheet 5 would show 67890 Hrs and Sheet 6 would show 67890 Cost.
- Br1anbaldwin_TLJan 02, 2024Copper Contributor
I wondered the same thing. I eventually figured out you just change the J value and assign it to the sheet as (J) so it picked the right one. It's about 6 months too late for your question, but hopefully it'll help others that look here.
Sub Rename()
Dim J As Integer
Dim r As Integer
r = 1
J = 2
For Each Sheet In Sheets
J = J + 1
Sheets(J).Name = "Sheet" & J
On Error Resume Next
Next
J = 2
For Each Sheet In Sheets
J = J + 1
Sheets(J).Name = Cells(r, 1).Value
On Error Resume Next
r = r + 1
Next
End Sub
- teri_chowSep 26, 2019Copper Contributor
Can this macro be done with the Name in $B$2? Haytham Amairah
- Haytham AmairahSep 27, 2019Silver Contributor
Hi,
If you mean that the list of names starts from cell B2, you can change the code as follows:
Sub rename() 'Set the starting cell Dim cell As Range Set cell = Range("B2") Dim r As Long r = 0 For Each Sheet In Sheets Sheet.Name = cell.Offset(r, 0) r = r + 1 Next End Sub
Regards
- Chi NguyenOct 23, 2018Copper Contributor
How would you do the reverse? Capture the list of all the worksheet names.
- BobOrrellOct 23, 2018Iron Contributor
You would change
Sheet.Name = Cells(r, 1).Value
to
Cells(r, 1).Value = Sheet.Name
Please note: This code will overwrite any data that is in the cells in column A of the worksheet that is active when you run it.
- Hursh PatelJun 15, 2018Copper Contributor
Thank you very much!!