Excel - A macro to rename existing worksheets based on a list [Solved/Closed]
GingerT2
Posts Tuesday November 8, 2016Registration date
November 9, 2016
Last seen
-
Nov 8, 2016 at 12:56 PM - Latest reply: vcoolio1169
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 10, 2018
Last seen
- Nov 10, 2016 at 07:44 AM
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.
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.
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):-
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.
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:-
(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.
has helped 1870 users this month
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