Jun 10 2021 08:01 PM
Hi,
I need to create a macro to rename the existing worksheets with the date values on the cells A4:A26. I know there is a way through VBA coding, but I am struggling on how to create it.
I have attached the spreadsheet.
Thanks, Vickie
Jun 11 2021 12:20 AM
Try this. Warning: you can run the macro only once.
Sub RenameSheet()
Dim i As Long
Dim n As Long
For i = 1 To 23
On Error Resume Next
Worksheets("Sheet" & i).Name = Format(Worksheets("Daily Totals").Range("A" & i + 3), "Short Date")
If Err Then
n = n + 1
Worksheets("Sheet" & i).Name = "NotUsed" & n
End If
Next i
End Sub
Jun 11 2021 05:22 AM
Hello @Hans Vogelaar:
Thank you for the code and it did work. But I do need to have the macro available every month so I could use it every month when I update the tabs to a new month.
Thank you!
Jun 11 2021 05:24 AM
SolutionIf you save the workbook with the original sheet names Sheet1, Sheet2 etc. as a macro-enabled template (.xltm), you can create a new workbook from the template each month.
(You could also save the workbook under a new name at the end of the macro, so that the original remains unchanged)
Jun 11 2021 03:09 PM
Jun 11 2021 11:43 PM
That will happen if the code tries to assign a name that has already been used for another sheet.
Jun 12 2021 12:13 AM
I hope the below code helps you out.
Public Sub CopySheetAndRename()
Dim newName As String
On Error Resume Next
newName = InputBox("Enter the name for the copied worksheet")
If newName <> "" Then
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = newName
End If
End Sub
Jun 12 2021 12:55 PM
Jun 12 2021 01:30 PM
Here is a new version. You should now be able to run the code multiple times.
Jun 11 2021 05:24 AM
SolutionIf you save the workbook with the original sheet names Sheet1, Sheet2 etc. as a macro-enabled template (.xltm), you can create a new workbook from the template each month.
(You could also save the workbook under a new name at the end of the macro, so that the original remains unchanged)