Page break macro based on cell content / formula

[Espen Sortland]

Hi.

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
Range("B12:I12").Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Else
Range("B12").Select
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:= _
xlSortNormal
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("B12").Select
End If
If Range("I5") = True Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Range("C4").Select
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]

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]

Hi Dave.

Thanks for your suggestion, but I dont have a complete header row due to
formating restraints for printing. The columns i want do not have headers
which subtotals need.

For this particular application I do not need or want subtotals displayed.


I found another piece of code on the boards that do what i want for column
A, but does not work for column D

I have added some support information in hidden columns and copy the range i
want sorted to column A, then run the script, and copy the fixed data from
the hidden lines back to column A. Would prefer to have that work for Column
D.

Its an accident waiting to happen when other people begin to "abuse" the
file and start deleting stuff, instead of using the propper macro buttons.

Best regards,
Espen

 

[Dave Peterson]

You only need a header row for data|subtotals to work. You don't need a value
in each cell in that header row--it's not like a pivottable.

And I don't have a guess how to change your code to work on column D.