Report

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

Ask a question GingerT 2Posts Tuesday November 8, 2016Registration date November 9, 2016 Last seen - Last answered on Nov 10, 2016 07:44AM
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 
Helpful
+1
moins plus
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.
GingerT 2Posts Tuesday November 8, 2016Registration date November 9, 2016 Last seen - Nov 9, 2016 10:43AM
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
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
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.
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!