Re: Page break macro based on cell content / formula

Have you considered sorting by AreaId, then using Data|subtotal. There's an
option for pagebreaks between groups.

And you'd get subtotals, too!

Espen Sortland wrote:


I'm trying to add page breaks for different values in the following macro
before printing.

Values i want to pagebreak is in column D from 12 to 311 (variable size)
Line#(A) - sku(B) - qty(C) - areaid(D) - locid(E) - desc(F) - etc

Want to page bread on areaid

Following is my existing macro for determining printarea.

Using If's and Find.Row lookups on sku number to display areaid(D) -
locid(E) - desc(F) - etc

Sub sorteringalfa()
If Range("B13") = "" Then
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("D12"), Order1:=xlAscending,
Key2:=Range("E12" _
), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
End If
If Range("I5") = True Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
End Sub

I have found some examples in other threads but i'm having trouble merging
their examples into my macro.

Also I do not have fixed headers in all the colums due to print formating.

Thanks for any assistance!


Dave Peterson

Relevant Pages

  • Re: Automatic page breaks and formulas
    ... This macro will take care of the pagebreaks. ... Sub InsertBreak_At_Change ... Gord Dibben MS Excel MVP ...
  • RE: print a new page for each group
    ... You can try out the below macro. ... --Set the Security level to low/medium in. ... Sub Pagebreaks() ...
  • Re: VBA to Remove PageBreaks from Between Tables
    ... The problem with the macro you posted is that each iteration of the For T ... Dim oRg As Range ... > After that I would just re-insert the pagebreaks ... > 'Boldface the row whoose first column has the text "Special Fee" ...
  • Page Break bug update query
    ... Excel 2002, WinXP ... that macro is shown below. ... I know that PageBreaks in VBA has a bug and I have some notes on that. ... Sub LocateFirstPB() ...
  • Re: Macros run slow after first run
    ... At the top of the macro put something like ... Since you printed I assume pagebreaks are being displayed and these can ... > Then the report gets printed and all the data deleted and it is run again ... > print reports for as many as 4 or 5 sets of data files. ...