SOLVED

How to rename the worksheet tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-2437769%22%20slang%3D%22en-US%22%3EHow%20to%20rename%20the%20worksheet%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2437769%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20macro%20to%20rename%20the%20existing%20worksheets%20with%20the%20date%20values%20on%20the%20cells%20A4%3AA26.%20I%20know%20there%20is%20a%20way%20through%20VBA%20coding%2C%20but%20I%20am%20struggling%20on%20how%20to%20create%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Vickie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2437769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2438087%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20rename%20the%20worksheet%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2438087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076337%22%20target%3D%22_blank%22%3E%40VickieMoody777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this.%20Warning%3A%20you%20can%20run%20the%20macro%20only%20once.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20RenameSheet()%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20For%20i%20%3D%201%20To%2023%0A%20%20%20%20%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20%20%20%20%20Worksheets(%22Sheet%22%20%26amp%3B%20i).Name%20%3D%20Format(Worksheets(%22Daily%20Totals%22).Range(%22A%22%20%26amp%3B%20i%20%2B%203)%2C%20%22Short%20Date%22)%0A%20%20%20%20%20%20%20%20If%20Err%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20n%20%3D%20n%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Sheet%22%20%26amp%3B%20i).Name%20%3D%20%22NotUsed%22%20%26amp%3B%20n%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439161%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20rename%20the%20worksheet%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439161%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20code%20and%20it%20did%20work.%20But%20I%20do%20need%20to%20have%20the%20macro%20available%20every%20month%20so%20I%20could%20use%20it%20every%20month%20when%20I%20update%20the%20tabs%20to%20a%20new%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439180%22%20slang%3D%22es-ES%22%3ERe%3A%20How%20to%20rename%20the%20worksheet%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439180%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076337%22%20target%3D%22_blank%22%3E%40VickieMoody777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20save%20the%20workbook%20with%20the%20original%20sheet%20names%20Sheet1%2C%20Sheet2%20etc.%20as%20a%20macro-enabled%20template%20(.xltm)%2C%20you%20can%20create%20a%20new%20workbook%20from%20the%20template%20each%20month.%3C%2FP%3E%0A%3CP%3E(You%20could%20also%20save%20the%20workbook%20under%20a%20new%20name%20at%20the%20end%20of%20the%20macro%2C%20so%20that%20the%20original%20remains%20unchanged)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2441260%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20rename%20the%20worksheet%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2441260%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CBR%20%2F%3EOk%2C%20I%20am%20not%20sure%20what%20is%20happening%2C%20but%20now%20all%20the%20sheets%20are%20naming%20not%20used.%20Is%20it%20because%20of%20the%20For%20i%20%3D%201%20To%2023%20command%3F%20What%20am%20i%20doing%20wrong%3F%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.