Which event trigger to call for macro?

[albertc30]

Hi all.

I was wondering how to optimize this spreadsheet I have to which I must save with a diferent name every week and then run macros to clear all data in the new saved sheet.

How would one go about running my macro when the current file is saved as but so that the macro will run on the new file saved and NOT on the old one as data must remain intact.

Regards and many thanks.
Albert

 

[mrmmickle1]

If you want the user to be able to name the workbook maybe try adding something like this to the end of the macro which gives a prompt with the days date and your file name:

 Application.Dialogs(xlDialogSaveAs).Show Format(Date, "mm-dd-yyyy") & " Your File Name"

It may be beneficial to use these lines of code with the above save prompt. They will set the drive and directory the dialog opens to:

ChDrive ("M")
ChDir ("M:\Your Directory\Your Directory\")

If you want to save the workbook as an .xlsm use this:

FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

If you want to simply save the workbook use something like this:

ThisWorkbook.Save

If you want to use an event in conjunction with your macro then it would be the BeforeSave Event. Hope this helps.