Appending Date on end of WorkSheet

[caleb06]

Hi everyone,

This has been posted before, however, this has a slight twist on it. I am trying to set up a VBA macro so when I click on the "Save" button it copies the current excel sheet and saves it as a date.

I don't want it to save as a completely new excel file, I want it to save as a new excel worksheet inside the same file.

I believe I am close but can't seem to figure out why it won't work.

Sub Button1_Click()

    Dim dt As String
    dt = Format(Now(), "DD-MM-YYYY")
    Set wb = ThisWorkbook
    ThisWorkbook.Activate
    ActiveSheet.Copy After:=wb.dt
    wb.Activate
    MsgBox "Saved as " + dt
    
End Sub

 

[Smitty]

Welcome to the Board!

You don't really save a new worksheet as an individual element in a workbook as they're all part of the workbook, so you can only save the whole thing.

    Set wb = ThisWorkbook
    ThisWorkbook.Activate

Your current code has issues in that you don't need to set a reference to the current wb since you're not switching between a different one.

See if this gets you started:

Sub Button1_Click()
    Dim dt As String
        dt = Format(Now(), "DD-MM-YYYY")
            ActiveSheet.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = dt
        '   You haven't saved anything yet, so probably don't need the MsgBox
            MsgBox "Saved as " + dt
End Sub

Note there's no error handling in case the sheet name already exists, so it will blow up if there's a conflict.

 

[caleb06]

Welcome to the Board!

You don't really save a new worksheet as an individual element in a workbook as they're all part of the workbook, so you can only save the whole thing.

    Set wb = ThisWorkbook
    ThisWorkbook.Activate

Your current code has issues in that you don't need to set a reference to the current wb since you're not switching between a different one.

See if this gets you started:

Sub Button1_Click()
    Dim dt As String
        dt = Format(Now(), "DD-MM-YYYY")
            ActiveSheet.Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = dt
        '   You haven't saved anything yet, so probably don't need the MsgBox
            MsgBox "Saved as " + dt
End Sub

Note there's no error handling in case the sheet name already exists, so it will blow up if there's a conflict.

Thanks, this works a treat!
Won't have to worry about the error handling issue as this sheet will only be used once monthly.