using a macro to rename a sheet based on date

[ajc623]

I have a workbook with a over 100 sheets and at the end I have a template where I have a macro that copies the template and does some work with pivot tables and it is working well. What I would like have happen is for the new sheet (Template(2)) to be named in mmddyy form one business day after the last dated sheet which would be the third from the end with the sheet named Template(2) and Template after it. So as an example right now I have the last sheet which is 081815 and when I run my macro I would like the template copied to Template(2) and named 081915 and if the last sheet was 082115 I would want it labeled 082415.

 

[JLGWhiz]

See if you can use this

Sub t()
Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 2)
dt = Left(sh.Name, 2) & "/" & Mid(sh.Name, 3, 2) & "/" & Right(sh.Name, 2)
    If Format(dt, "w") > 1 And Format(dt, "w") < 6 Then
        dt = CDate(dt) + 1
    ElseIf Format(dt, "w") = 6 Then
        dt = CDate(dt) + 3
    End If
Sheets("Template").Copy After:=sh
ActiveSheet.Name = Format(dt, "mmddyy")
End Sub

 

[ajc623]

Although I dont really follow what that is saying, it did just what I needed it to do with the dates. My only problem is that I now have the new sheet with the correct date and I still have a sheet named Template (2). I added to my macro to delete it but it pops up with a warning everytime, any ideas how to get rid of it?

Sub NewPage()'
' NewPage Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    
Sheets("Template").Copy Before:=Sheets("Template")
    Sheets("Template (2)").Select
    Range("K4").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Y:\Shipping\DAILY SHIPPING\[2015 DAILY SHIPPING macro test.xlsm]Template (2)!C3:C6" _
        , Version:=xlPivotTableVersion14)
        ActiveSheet.Shapes.Range(Array("TextBox 2")).Select
    Selection.delete
   Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 2)
dt = Left(sh.Name, 2) & "/" & Mid(sh.Name, 3, 2) & "/" & Right(sh.Name, 2)
    If Format(dt, "w") > 1 And Format(dt, "w") < 6 Then
        dt = CDate(dt) + 1
    ElseIf Format(dt, "w") = 6 Then
        dt = CDate(dt) + 3
    End If
Sheets("Template").Copy After:=sh
ActiveSheet.Name = Format(dt, "mmddyy")
ActiveSheet.Shapes.Range(Array("TextBox 2")).Select
    Selection.delete
    Sheets("Template (2)").Select
    ActiveWindow.SelectedSheets.delete
    Sheets("Template").Select
End Sub

 

[JLGWhiz]

See if this will do what you want, without throwing an error. If it does error, click the debug button and note the line of code it errors on, then post that information along with the error message.

Sub NewPage() '
' NewPage Macro
' Keyboard Shortcut: Ctrl+Shift+N
Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 2)
Sheets("Template").Copy After:=sh
    With Sheets("Template (2)")
        .PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Y:\Shipping\DAILY SHIPPING\[2015 DAILY SHIPPING macro test.xlsm]Template (2)!C3:C6" _
        , Version:=xlPivotTableVersion14)
        .Shapes.Range(Array("TextBox 2")).Delete
        dt = Left(sh.Name, 2) & "/" & Mid(sh.Name, 3, 2) & "/" & Right(sh.Name, 2)
        If Format(dt, "w") > 1 And Format(dt, "w") < 6 Then
            dt = CDate(dt) + 1
        ElseIf Format(dt, "w") = 6 Then
            dt = CDate(dt) + 3
        End If
        .Name = Format(dt, "mmddyy")
    End With
Sheets("Template").Select
End Sub

 

[ajc623]

It did return an error message that says
"Run time error 1004 - Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."

Line of code with error is in red below. Thanks for the help on this.

Andrew

Sub NewPage() '' NewPage Macro
' Keyboard Shortcut: Ctrl+Shift+N
Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 2)
Sheets("Template").Copy After:=sh
    With Sheets("Template (2)")
        .PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Y:\Shipping\DAILY SHIPPING\[2015 DAILY SHIPPING macro test.xlsm]Template (2)!C3:C6" _
        , Version:=xlPivotTableVersion14)
        .Shapes.Range(Array("TextBox 2")).delete
        dt = Left(sh.Name, 2) & "/" & Mid(sh.Name, 3, 2) & "/" & Right(sh.Name, 2)
        If Format(dt, "w") > 1 And Format(dt, "w") < 6 Then
            dt = CDate(dt) + 1
        ElseIf Format(dt, "w") = 6 Then
            dt = CDate(dt) + 3
        End If
[COLOR=#ff0000]        .Name = Format(dt, "mmddyy")[/COLOR]
    End With
Sheets("Template").Select
End Sub

 

[JLGWhiz]

It did return an error message that says
"Run time error 1004 - Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."

Line of code with error is in red below. Thanks for the help on this.

Andrew

Sub NewPage() '' NewPage Macro
' Keyboard Shortcut: Ctrl+Shift+N
Dim sh As Worksheet
Set sh = Sheets(Sheets.Count - 2)
Sheets("Template").Copy After:=sh
    With Sheets("Template (2)")
        .PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Y:\Shipping\DAILY SHIPPING\[2015 DAILY SHIPPING macro test.xlsm]Template (2)!C3:C6" _
        , Version:=xlPivotTableVersion14)
        .Shapes.Range(Array("TextBox 2")).delete
        dt = Left(sh.Name, 2) & "/" & Mid(sh.Name, 3, 2) & "/" & Right(sh.Name, 2)
        If Format(dt, "w") > 1 And Format(dt, "w") < 6 Then
            dt = CDate(dt) + 1
        ElseIf Format(dt, "w") = 6 Then
            dt = CDate(dt) + 3
        End If
[COLOR=#ff0000]        .Name = Format(dt, "mmddyy")[/COLOR]
    End With
Sheets("Template").Select
End Sub

That means that you already have a sheet in your workbook with the date that is generated by the code. Without seeing the workbook, I am afraid I can't help you very much on that one. What you can do is use F8 to step through the code line by line and when the dt variable is initialized, hover the mouse pointer over it to activate the tool tips and see what value shows to make sure it is looking at the right sheet name to calculate the new sheet name. Also be sure your sheets are in the correct sequence.

 

[ajc623]

I got it to work! Just changed Set sh = Sheets(Sheets.Count - 2) to Set sh = Sheets(Sheets.Count - 1) and it works just like I want it to. Thanks again for your help, I would never have made this work on my own

Andrew

 

[JLGWhiz]

I got it to work! Just changed Set sh = Sheets(Sheets.Count - 2) to Set sh = Sheets(Sheets.Count - 1) and it works just like I want it to. Thanks again for your help, I would never have made this work on my own

Andrew

You are welcome,
Regards, JLG

 

[ajc623]

Hi JLG, I have one more question for you. The coding you gave me is working great but I am wondering how to do one more thing. I would like to have the date that is associated with the sheet name to also be on the sheet in cell C49 and formatted as "Wednesday, August 19, 2015" or long date. Is there a way to accomplish this? Thanks again

Andrew