How to auto set print range or number of copies based on a cell value

[Divindunk]

Hi All

I have a workbook which users enter data for labels in one sheet this data is linked to another sheet which has the layout for the labels. the user enters the number of individual labels required and then this is converted to the number of pages required as there are 8 labels per printed page the label sheet has the print area set to cover the maximum number of labels ever likely to be needed. Can I set the Print Range in the print dialogue box (i.e. 1 to "X") using this calculated value (X) and ultimately carry out the print operation in 1 button click in the data entry page. I have a little experience with VBA (more with Access) but am not an expert. Would it be easier to change label layout to just one page of labels and set number of copies from data value(X) instead?

Thanks in advance for any advice offered.

 

[Hui]

Dividunk

Firstly, Welcome to the Chandoo.org Forums

Yes, What you want can be done

The Print area is controlled by a named Formula called Print_Area

Normally it is set manually as a range
Print_Area: =Sheet1!$B$1:$I$60

But it can also be set by using a formula which in your case will be based on the data
Something like
Print_Area: =Offset(Sheet1!$A$1:$I$1,,,No Rows)
where No Rows is how many rows you need to use

If you want more help can you post a sample file with some data please

 

[Divindunk]

Hi Hui

Thanks for the reply, unfortunately my plans have changed a little. The user enters data for labels and the last entry is the number of pallets requiring labels, this data is transferred to the sheet where the labels are and tells the user how many sheets to print. What I really would like to do is have a button that when pressed takes this calulation and completes the print operation for the user ie print a "N" number of copies based on the result "N" of a calulation in another sheet in the same workbook. There are occasions when only red or green labels are required so ideally option buttons for Red, Green or both would be ideal (which would set the pages to "1 to 1", "2 to 2" or "1 to 2").

These new plans are instead of changing the print area as originally anticipated. Labels will be printed on 10 per sheet eventually ie 1 sheet per pallet but at present we only have 8 per sheet labels but all above is still relevant!

However the formula you sent before maybe useful in future but I have a couple of queries - would it be pasted into tha VBA box or is it a macro? This question probably gives you an idea as to my level of knowledge in excel programming! Also, you say that "No Rows" is how many rows are to be used, does that have to be a pre-set number or can I reference a cell value to populate that part of the formula?

Sorry it's a bit wordy!

I have attached the workbook in question as it stands at the moment

Thanks again