Excel - A macro to rename existing worksheets based on a listSolved/Closed

Question

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.

vcoolio · Accepted Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

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

3 replies

Subscribe To Our Newsletter!