Setting Print Area
 

I have a number of similar worksheets that I want to open through a script
and print only the last page of data. Each worksheet has varrying amounts of
data that change each month so I have a formula in each (in cell G1) that
displays the range equal to the last page of data (formula evaluates to
A125:E156 for instance).

As I open each worksheet with the script, how do I set the Print Range equal
to the range the formula returns?

Thanks,

Pete

Setting Print Area
 

Figured it out.

ActiveSheet.PageSetup.PrintArea = Range("g1").Value

"dolppm" wrote:

I have a number of similar worksheets that I want to open through a script
and print only the last page of data. Each worksheet has varrying amounts of
data that change each month so I have a formula in each (in cell G1) that
displays the range equal to the last page of data (formula evaluates to
A125:E156 for instance).

As I open each worksheet with the script, how do I set the Print Range equal
to the range the formula returns?

Thanks,

Pete

Setting Print Area
 

Another solution would be to use a dynamic print area. Since Excel uses a
named range to define the print area of a worksheet, you could redefine this
range as dynamic. To do this go to menu "Insert - menu - define" and look
for range Print_Area. Set it to something like this:

=OFFSET(Sheet1!$A$19,0,0,COUNTA(Sheet1!$A:$A),COUN TA(Sheet1!$19:$19))

Formula explanation:
"Sheet1!$A$19" is the upper left starting cell of the print area (this
usually does not change for most users)
"COUNTA(Sheet1!$A:$A)" returns the number of non-empty cells in column A
"COUNTA(Sheet1!$19:$19)" returns the number of non-empty cells in row 19

Hope it helps !

DaVinci

"dolppm" wrote:

I have a number of similar worksheets that I want to open through a script
and print only the last page of data. Each worksheet has varrying amounts of
data that change each month so I have a formula in each (in cell G1) that
displays the range equal to the last page of data (formula evaluates to
A125:E156 for instance).

As I open each worksheet with the script, how do I set the Print Range equal
to the range the formula returns?

Thanks,

Pete