Automatically run Macro before printing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BLW - 24 May 2008 22:38 GMT

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

Reply to this Message

Gord Dibben - 24 May 2008 23:46 GMT

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

>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
[quoted text clipped - 24 lines]
> Loop
>End Sub

Reply to this Message

Don Guillett - 25 May 2008 00:02 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>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
[quoted text clipped - 26 lines]
> Loop
> End Sub

Reply to this Message