Dynamic Print Area

[Ray]

I searched for 'Dynamic Print Area' and, to my surprise, got
absolutely no results. I can't imagine that I'm the first to ask
this, but I've no idea how to re-word it to get results ...

I have a worksheet that users need to print every day -- problem is
that some days, the sheet could have 3015 lines (or more) and other
days, only have 1005 (or less). The number of rows is dictated by how
many cashiers worked the previous day, with each cashier entry being
67 lines long.

My specific questions are:
1) How can I set the print_area to recognize what the bottom row is
and set the print_area accordingly?

2) Each page needs to contain all data for just one cashier ... that
is, 67 lines per page. Entries start in row 3 ...

Good to know info:
A) this sheet is actually one of many in the workbook (all will be
printed) ... it's just the only one that needs a dynamic print_area.

B) users may print from a Button I've inserted OR the default Excel
Print button -- ideally, the solution here will work either way.

Thanks alot for your time on this ... a successful solution will help
save multiple REAMS of paper each day (FYI, I'm attempting to move
this to a paper-less solution but our auditors aren't keen on that).

Regards, Ray

 

[Don Guillett]

Can probably be done with a defined name???

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Rick Rothstein]

I **think** this macro will do what you want..First, set the appropriate
values into the first 5 Const statements (hopefully the constant names are
self-explanatory enough), then run the macro. I believe it will put
horizontal page breaks every 67 (the value assigned to the RowsInRecord
constant) and make those 67 rows fit on one page.

Sub SetDynamicPrintArea()
Const RowsInRecord As Long = 67
Const StartRow As Long = 3
Const SheetName As String = "Sheet2"
Const StartColumn As String = "A"
Const EndColumn As String = "E"
Dim X As Long, LastRow As Long, FirstBreak As Long
With Worksheets(SheetName)
.Cells.PageBreak = xlPageBreakNone
FirstBreak = .HPageBreaks(1).Location.Row
LastRow = .Cells(.Rows.Count, StartColumn).End(xlUp).Row
For X = StartRow + RowsInRecord To LastRow Step RowsInRecord
.Cells(X, "A").PageBreak = xlPageBreakManual
Next
.PageSetup.PrintArea = Range(Cells(StartRow, StartColumn), _
Cells(LastRow, EndColumn)).Address
With .PageSetup
.Zoom = 100 * (FirstBreak - StartRow + 1) / RowsInRecord
End With
End With
End Sub

Note: This is my first attempt at writing code to do this, so I'm not
totally sure this works under all circumstances... if it doesn't, just let
me know what went wrong and I'll try to fix it.