Sometimes, I need to update some data or to do a specific action each time I save my report, for instance, putting the current time and/or to do a specific action before to close the file, for instance, save a copy without any macro.

 

When I use the macro ?

Each time that I want to do something automatically when I save and/or just before to close my report.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How to use the macros ?

No way to put a customized name, it should be:

• For saving: Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
• For closing: Sub Workbook_BeforeClose(Cancel As Boolean)

And the most important, it should be put in "thisworkbook" if not, it will not work.

 

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Do some actions each time I save the file :

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' put your code
code
End Sub

Do some actions only when I close the file:

Sub Workbook_BeforeClose(Cancel As Boolean)
' put your code
code
End Sub