Calculate Number Of Sheets To Print In VBA
 

Hello

I have a pivot table on a sheet, which can at any time require upto
pages to print. Along side the pivot report there are also VLOOKU
formular retreiving data from another sheet based on the results of th
pivot report, so as a result I cannot simply allow excel to decide th
number of sheets, as it allways selects 5 to print out the VLOOKU
data, which is empty, but surrounded by borders. I use a simple COUNT
formula to calculate how many sheets will be required.
The result of this COUNTA is placed into cell G5 and then rounded up i
cell G6.

Can I alter the code below to make the To:=X, where X is the figur
found in cell G6?


ActiveWindow.SelectedSheets.PrintOut From:=1, To:=5, Copies:=1
Collate:=True

Could I simply alter the To:=5 part to:

To:=(Range.("G6))

or something similar, or will I need to define a range name?


Thanks in advance!

Celtic_Avenger
:confused: :confused: :confused: :confused: :confused

--
Message posted from http://www.ExcelForum.com

Calculate Number Of Sheets To Print In VBA
 

I would qualify it with a the sheet on which it is located.

worksheets("Sheet1").PrintOut From:=1, _
To:=Worksheets("Sheet1").Range("G6").Value, _
Copies:=1, _
Collate:=True


--
Regards,
Tom Ogilvy

"Celtic_Avenger " wrote in
message ...
Hello

I have a pivot table on a sheet, which can at any time require upto 5
pages to print. Along side the pivot report there are also VLOOKUP
formular retreiving data from another sheet based on the results of the
pivot report, so as a result I cannot simply allow excel to decide the
number of sheets, as it allways selects 5 to print out the VLOOKUP
data, which is empty, but surrounded by borders. I use a simple COUNTA
formula to calculate how many sheets will be required.
The result of this COUNTA is placed into cell G5 and then rounded up in
cell G6.

Can I alter the code below to make the To:=X, where X is the figure
found in cell G6?


ActiveWindow.SelectedSheets.PrintOut From:=1, To:=5, Copies:=1,
Collate:=True

Could I simply alter the To:=5 part to:

To:=(Range.("G6))

or something similar, or will I need to define a range name?


Thanks in advance!

Celtic_Avenger
:confused: :confused: :confused: :confused: :confused:


---
Message posted from http://www.ExcelForum.com/