SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-204784%22%20slang%3D%22en-US%22%3EI%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204784%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20112%20sheets%20and%20112%20names%2C%20I%20would%20like%20to%20rename%20all%20of%20the%20sheets.%20How%20can%20I%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-204784%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETutorial%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277100%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277100%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20would%20change%3C%2FP%3E%3CPRE%3ESheet.Name%20%3D%20Cells(r%2C%201).Value%3C%2FPRE%3E%3CP%3Eto%3C%2FP%3E%3CPRE%3ECells(r%2C%201).Value%20%3D%20Sheet.Name%3C%2FPRE%3E%3CP%3EPlease%20note%3A%26nbsp%3B%20This%20code%26nbsp%3B%20will%20overwrite%20any%20data%20that%20is%20in%20the%20cells%20in%20column%20A%20of%20the%20worksheet%20that%20is%20active%20when%20you%20run%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276907%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276907%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20would%20you%20do%20the%20reverse%3F%20Capture%20the%20list%20of%20all%20the%20worksheet%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204848%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204848%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204814%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204814%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hursh%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20this%20by%20using%20a%20VBA%20code!%3C%2FP%3E%3CP%3EBut%20you%20need%20to%20have%20these%20name%20listed%20in%20a%20worksheet%20stating%20from%20cell%20A1%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36121iF8E1B55680F1FDD3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22List%20of%20names.png%22%20title%3D%22List%20of%20names.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20can%20depend%20on%20this%20code%20to%20rename%20all%20worksheets%20at%20once%3A%3C%2FP%3E%3CPRE%3ESub%20rename()%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20r%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20r%20%3D%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20Sheet%20In%20Sheets%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Sheet.Name%20%3D%20Cells(r%2C%201).Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20r%20%3D%20r%20%2B%201%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20code%20will%20loop%20through%20each%20worksheet%20and%20rename%20the%20Sheet1%20with%20the%20name%20of%20cell%20A1%2C%20Sheet2%20with%20the%20name%20of%20cell%20A2%2C%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2013%2F12%2F06%2Fadd-run-vba-macro-excel%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E%20to%20learn%20how%20to%20insert%20and%20run%20this%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877701%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877701%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20this%20macro%20be%20done%20with%20the%20Name%20in%20%24B%242%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878451%22%20slang%3D%22en-US%22%3ERe%3A%20I%20would%20like%20to%20rename%20all%20the%20sheets%20from%20a%20list%20of%20names%20I%20have%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415925%22%20target%3D%22_blank%22%3E%40teri_chow%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20mean%20that%20the%20list%20of%20names%20starts%20from%20cell%20B2%2C%20you%20can%20change%20the%20code%20as%20follows%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-css%22%3E%3CCODE%3ESub%20rename()%0A%20%20%20%20'Set%20the%20starting%20cell%0A%20%20%20%20Dim%20cell%20As%20Range%0A%20%20%20%20Set%20cell%20%3D%20Range(%22B2%22)%0A%20%20%20%20%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20r%20%3D%200%0A%20%20%20%20%0A%20%20%20%20For%20Each%20Sheet%20In%20Sheets%0A%20%20%20%20%20%20%20%20Sheet.Name%20%3D%20cell.Offset(r%2C%200)%0A%20%20%20%20%20%20%20%20r%20%3D%20r%20%2B%201%0A%20%20%20%20Next%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Hursh Patel
Occasional Contributor

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

6 Replies
Solution

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:

List of names.png

 

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

Highlighted
Highlighted

How would you do the reverse? Capture the list of all the worksheet names.

Highlighted

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.

Highlighted

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

Highlighted

@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

Related Conversations
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
9 Replies
Multi-value fields display no separator
Florian Hein in SharePoint on
1 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Rename device name for Surface Hub
Petri X in Surface Hub on
1 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies