SOLVED

How to rename the worksheet tabs

New Contributor

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

8 Replies

@VickieMoody777 

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

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!

best response confirmed by VickieMoody777 (New Contributor)
Solution

@VickieMoody777 

If 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)

Hello @Hans Vogelaar
Ok, I am not sure what is happening, but now all the sheets are naming not used. Is it because of the For i = 1 To 23 command? What am i doing wrong?

@VickieMoody777 

That will happen if the code tries to assign a name that has already been used for another sheet.

@VickieMoody777 

 

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
 

Hello @AlwinAlexander

Thank you for replying and giving me the code, but that is not what I am trying to accomplish. All I need is a macro to automatically to rename existing worksheet tabs each month from the date values A4:A24 on the worksheet named "Daily Totals". Also the format should be "m.d". I attached the worksheet on my first post. I hope you can retrieve it.

@VickieMoody777 

Here is a new version. You should now be able to run the code multiple times.