Forum Discussion

Hursh Patel

Copper Contributor

Jun 15, 2018

Solved

I would like to rename all the sheets from a list of names I have

I have 112 sheets and 112 names, I would like to rename all of the sheets. How can I do this?

Formulas & Functions

Tutorial

Haytham Amairah
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 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

Haytham Amairah

Silver Contributor

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 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

mjohnston02090819

Copper Contributor

Jun 12, 2023

Haytham Amairah

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_TL
Copper Contributor
Jan 02, 2024
mjohnston02090819

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