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 https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/ 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 https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/ to learn how to insert and run this code.

Regards

teri_chow

Copper Contributor

Sep 26, 2019

Can this macro be done with the Name in $B$2? Haytham Amairah

Haytham Amairah

Silver Contributor

Sep 26, 2019

teri_chow

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

Rename all the sheets from a list of names.xlsm18 KB