vba code to copy and paste the current workbook

[dpaton05]

I need some vba code that will make a copy of the current workbook. Can someone help me please?

 

[Michael M]

Copies the file to C:\Temp as copybook.xls

Sub MM1()
ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\CopyBook.xls"
End Sub

 

[dpaton05]

If I exclude the path, does it save to the same directory as the original file?

 

[Michael M]

Yes...

ActiveWorkbook.SaveAs "CopyBook.xls"

 

[dpaton05]

Thanks Michael. I have sheets that go from July 2018 to June 2019. The file name for instance is 2018-2019.xlsm. After June 2019 has gone and a new document needed, I want a button that will make a copy of the spreadsheet with the new name of the document for the new financial year. Do you have any ideas how I might go about doing that?

 

[Michael M]

Untested

Sub MM1()
ActiveWorkbook.SaveAs Format(Date, "yyyy") & "-" & Format(Date, "yyyy") + 1 & ".xlsx"
End Sub

 

[Michael M]

Update...
will save as a macro enabled workbook, so the code goes with it.

Sub MM1()
ActiveWorkbook.SaveAs Format(Date, "yyyy") & " - " & Format(Date, "yyyy") + 1 & ".xlsm", FileFormat:=52
End Sub

 

[dpaton05]

Thanks, that's good but I wanted a new workbook to open and have everything pasted into it so you have a separate file.

 

[Michael M]

you mean you wanted all the new months created once the workbook is saved ??
so June 2018 would become june 2019 ??

 

[dpaton05]

Eventually I will want that but I was talking about for now, I just want to press a button and for a new document to appear and for it to be an exact copy of the already open document. The filename of the new document needs to be set to June- July of the next financial year.

I will also want the sheet names to be updated accordingly, but I think I know how to do that one, but I guess, if you could also add it in here incase I can't work it out.

Thanks Michael.