Automatically run Macro before printing

[BLW]

I have written this macro to set page breaks once all info is added to the
spreadsheet. Is there anyway to have the Macro run automatically when the
user prints or previews? Or is it better is attach it to a button for them
to click before printing?

Thanks for any help - BLW

Sub PageBreak()

Range("A1").Select
Do While ActiveCell.Value <> "End"
ActiveCell.Offset(20, 0).Select
value1 = ActiveCell.Value
If value1 = "ReMax" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Else
Do While ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
value1 = ActiveCell.Value
If value1 = "ReMax" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End If
Loop
End If
Loop
End Sub

 

[Gord Dibben]

Private Sub Workbook_BeforePrint(Cancel As Boolean)

your code or macroname here

End Sub

Which is better? That would be your preference but clicking a button to
activate a macro then clicking on the Print Icon is not as easy as clicking on
the Print Icon to achieve the same thing.


Gord Dibben MS Excel MVP

 

[Don Guillett]

Look in the ThisWorkbook module for the "before print" event. You probably
want to restrict to a single sheet.
Also, your macro could stand some pruning. Exactly what are you trying to
do? Something like this maybe that sets a pagebreak before each instance of
remax

Sub setpagebreaks()
With Worksheets(1).Range("a1:a500")
Set c = .Find("ReMax", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
' MsgBox c.Row
ActiveSheet.HPageBreaks.Add Before:=c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub