Personal.xlxs macro, BeforeSave event, how to apply to current workbook?

[mcsinfl]

I have the following macro to insert the path\filename\sheet into all sheets in a workbook. I would like to be able to automatically run the code whenever I save or print any active workbook. Can anyone help please?


Sub PathinFooter()

Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
Sheets(I).Select

With ActiveSheet.PageSetup
.RightFooter = "&6&Z&F\&F\&A"
End With

Next I

Sheets(1).Select

End Sub

 

[poolhall]

use ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'your code
End Sub

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

 

[mcsinfl]

Thank you, but won't I then have to put this code in each workbook that I use? If so, I'm hoping to avoid that step.

Mark

use ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'your code
End Sub

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

 

[poolhall]

I am not sure if it is possible. You may want to save your sub into your personal macro workbook and run it when you need to apply it to an active workbook.

 

[mcsinfl]

thanks, much appreciated. I was trying to avoid having to apply to the active workbook-as I get older my memory isn't what it used to be. Plus I often open workbooks other people created and this would automagically update those workbooks as well.

I am not sure if it is possible. You may want to save your sub into your personal macro workbook and run it when you need to apply it to an active workbook.

 

[mcsinfl]

It looks like it's doable based on this thread:

http://www.mrexcel.com/forum/showthread.php?t=445375

and the two links in the thread:

http://msdn.microsoft.com/en-us/library/aa159404(office.10).aspx
http://msdn.microsoft.com/en-us/library/aa160242(office.10).aspx

I'll have to play a bit when I get some time