Sub SetPrintArea()
'change this constant
'to point to the column
'that will Always have entries
'down to the last used row on
'the parts order sheet
'it should be a column that
'you type data into, not one
'with a formula.
Const keyColumn = "A"

ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" _
& Range(keyColumn & Rows.Count).End(xlUp).Row
ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub

Put this macro into your workbook:
Open the workbook, press [Alt]+[F11] to open the VB Editor then choose
Insert -- Module and copy and paste the code into the code module presented
to you. Change the letter for the 'keyColumn' as needed. Close the VB
Editor.

Add a button from the Forms toolbar or a text box to your worksheet and
assign the macro to it so that when it is clicked, the print area will be
reset and a copy of the sheet will be printed.


"Bruister via OfficeKB.com" wrote:

I have a basic excel template used for ordering parts. It runs from A1:N250
and I can print easily repeating rows 1:11 on each page (contains job details)

. Trouble is sometimes there may only be 10 part numbers requiring only first
page and I finish up throwing away trees by printing entire sheet! I know I
can reset print area each time am setting this up on Sharepoint for multi
users and need a macro/button to recognise number of items and print
accordingly. Any help greatly appreciated

--
Message posted via http://www.officekb.com

.