Menu
Ask a question Report

Excel - A macro to rename existing worksheets based on a list [Solved/Closed]

Posts
2
Registration date
Tuesday November 8, 2016
Last seen
November 9, 2016
 - - Latest reply: vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
 - Nov 10, 2016 at 07:44 AM
Excel Ask a question Report
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.
See more 

3 replies

Best answer
Report Respond to vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
 220
2
Thank you
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.

Say "Thank you" 2

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

CCM 3194 users have said thank you to us this month

Report
GingerT
Posts
2
Registration date
Tuesday November 8, 2016
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
Report Respond to vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
 220
0
Thank you
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.
Report Respond to vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
 220
0
Thank you
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