Save Workbook at End of Month with Macro

I have a workbook that needs to be archived at the end of every month. I figured trying to create new sheets etc would be more hassle than its worth, so I just created a macro that I assigned to a button that will save the file at the end of the month - I have that part figured out.

Right now I have the macro saving the current workbook on as "File_LastMonth" - it's all pre-programmed. Is there a way to do it where excel will put it in the actual past month ? IE "File_June2013" ?

Thanks.

Also, is there a way to keep excel from opening the newly saved file ? I would rather the save "happens in the background" and you never leave the "master" workbook

Thanks.

wfm007,

That works! Only one minor question:

This is my coding:

Please Login or Register  to view this content.

Which produces "File.xlsm_June2013"

Is there a way to do it "File_June2013.xlsm"? The way its done now is fine, i can simply rename it.

Apologies on the simple questions, I know just enough to get me deep into a project and in trouble!

Thanks.

- AF

Hi! I was looking for a similar macro to save my end of month sheet. Can you please share your VBA code? Thank you!!

This is the code I have used recently to archive files:

Private Sub CommandButton1_Click()
CurrentFile = ThisWorkbook.FullName
Dim sFile As String
sFile = "FILENAME " & Format(Date, "mmm-yy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:="FILEPATH" & sFile, FileFormat:=52
Set ActBook = ActiveWorkbook
Workbooks.Open CurrentFile
ActBook.Close
End Sub

This also answers your question regarding opening the new file - it will open but this code will close it and return to the "old" document.
Filename format is "Filename MMM-YY"
Hope this helps!