change my 'run on open' macro to 'run before save' macro

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
Hi Guys
I have this macro that worked when it was set to run after the sheet opened.

But now I want to change it to run before save or run before print.

I tried to alter it myself but its not working. This is what I have:

Code:
Private Sub Workbook_BeforeSave()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Range("d1").Value = Sheets(1).Range("d1").Value + 1
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("d2")
    With ActiveSheet.PageSetup
        .RightFooter = r
        End With
    
    Next wsheet
End Sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Mr_Roscoe

Board Regular
Joined
Mar 28, 2006
Messages
200
oliviar - could not get your macro to run. I use:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

which is located in ThisWorkbook

HTH!
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Hi Guys
I have this macro that worked when it was set to run after the sheet opened.

But now I want to change it to run before save or run before print.

I tried to alter it myself but its not working. This is what I have:

Code:
Private Sub Workbook_BeforeSave()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Range("d1").Value = Sheets(1).Range("d1").Value + 1
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("d2")
    With ActiveSheet.PageSetup
        .RightFooter = r
        End With
    
    Next wsheet
End Sub

It works in the BeforeClose event. Don't know if that helps?

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Range("d1").Value = Sheets(1).Range("d1").Value + 1
    Dim r As Range
    Set r = Worksheets("Sheet1").Range("d2")
    With ActiveSheet.PageSetup
        .RightFooter = r
        End With
    
    Next wsheet

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,660
Messages
5,573,471
Members
412,531
Latest member
gkrishna788
Top