Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Area
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. -- Rick (MVP - Excel) "Ray" wrote in message ... 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Print Area | Excel Programming | |||
Dynamic Print Area | Excel Programming | |||
Dynamic Print Area | Excel Programming | |||
Dynamic Print Area | Excel Discussion (Misc queries) | |||
dynamic print area | Excel Worksheet Functions |