0

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
Caleb
  • 1
  • 1

1 Answers1

1

try 

ActiveSheet.Copy After:=Sheets(ActiveSheet.Name)
Sheets(ActiveSheet.Index + 1).Name = dt
innomatics
  • 370
  • 1
  • 10
  • You should also rename the copied sheet as per OP request. – Jules Feb 04 '16 at 05:48
  • Look at this article to find the new sheet :) http://stackoverflow.com/questions/7692274/excel-vba-copy-sheet-and-get-resulting-sheet-object – Jules Feb 04 '16 at 05:55