Previous Topic Next Topic

Excel - A macro to rename existing worksheets based on a list

Solved/Closed
Report
Posts
2
Registration date
Tuesday November 8, 2016
Status
Member
Last seen
November 9, 2016
 -
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
 -
I have a P.O. spreadsheet with a summary page. Every year we need to create a new file but I would like to create a template that would just renumber the spreadsheets based on whatever number we finished at the previous year.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

This sort of works except it creates new spreadsheets instead of changing existing ones.

Please advise.
Related:

3 replies

Answer 1 / 3
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
 241
Hello GingerT,

Try the following code:- 


Sub RenameWkSheets()

        Dim shtName As Variant
        Dim rng As Range, ws As Worksheet
        Dim i As Long

With Sheets("Summary")
        Set rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address)
        shtName = Application.Transpose(rng)
        i = LBound(shtName)
End With

For Each ws In Worksheets
        If ws.Name <> "Summary" Then
          ws.Name = shtName(i)
            i = i + 1
      End If
Next ws

End Sub


It will rename sheets from a list that you may have in Column A of your Summary sheet starting at A1.

I hope that this helps.

Cheerio,
vcoolio.
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report
Posts
2
Registration date
Tuesday November 8, 2016
Status
Member
Last seen
November 9, 2016
Thanks Vcoolio!

This worked perfectly except I am getting a run-time error '9' saying subscript out of range at the end. Referencing

ws.Name=shtName(i)

Also, is there a way to change the hyperlinks on the summary page that refer to these tabs using the original list?

GT
Answer 2 / 3
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
 241
Hello GingerT,

That error is brought about by the fact that the code cannot find a sheet to re-name or you have more sheets than names. Here's an example at the following link (same code as above):-

https://www.dropbox.com/s/s3qks3rq0biroov/Re-name%20work%20sheets.xlsm?dl=0

If you click on the "RUN" button, you'll see that sheets 2 - 5 are re-named (Index sheet is left alone). You'll also note that the Index sheet does not have headings. Overwrite "Bob" in Column A with say, Names, then change the A1 in this line of code:- 

Set rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address)


to A2.

(row one is usually reserved for headings so the code range should start at A2 to allow for this).

You'll see that three sheets are re-named (Tim, Tom & Carol) but you'll receive the error message still. That's because there is still a sheet to re-name but the code doesn't have a name to give it. So, if you delete the sheet that can't be re-named (sheet5 in this case), no error message will arise. Try it again for yourself: change A1 to A2 in the code, overwrite "Bob" with "Names" and delete sheet5 then run the code again.

Next check your sheet for a similar scenario.

I hope that this enlightens you.

Cheerio,
vcoolio.
Answer 3 / 3
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
 241
Hello again GingerT,

Apologies. I completely missed the last part of your last post.

I would say that the easiest way to create new hyperlinks for the new sheet names would be via a separate button with the following codes assigned to it:- 


Sub CreateHLink()

ChangeNames

Dim i As Long

    With Sheets("Summary")
        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        .Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:= _
        "'" & .Range("A" & i).Value & "'!A1", TextToDisplay:=.Range("A" & i).Value
        Next i
    End With
    
End Sub

Sub ChangeNames()

    For i = 2 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
    Next i

End Sub


The ChangeNames macro will list the new sheet names in Column A of the Summary sheet with the CreateHlink macro creating the new hyperlinks to each newly named sheet. The CreateHLink macro calls the ChangeNames macro.

Following is the link to my test work book for you to peruse. Click on the "Create HLink" button to see it work:-

https://www.dropbox.com/s/92plvbbwj7dvnxh/Create%20hyperlinks%20from%20list.xlsm?dl=0

(You could, of course, combine all three codes to simultaneously do the required task with just one click of a button).

FYI. Excel has an inbuilt function which takes you directly to any sheet. Simply right click on the Command Bars arrows at the bottom left of any sheet then select the required sheet from the list that appears.

I hope that this helps.

Cheerio,
vcoolio.
Ask a question