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

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

On Sat, 24 May 2008 14:38:00 -0700, BLW <(E-Mail Removed)> wrote:

>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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"BLW" <(E-Mail Removed)> wrote in message
news:56CAB910-17B1-4973-BE75-(E-Mail Removed)...
>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