controlling the number of pages printed

jimmywanna

Board Regular
Joined
Mar 18, 2002
Messages
182
HELP!
I have been given a project at work, I have an excel sheet in a workbook which is a page of labels.
I have made the previous sheet contain fields for the user to input things like expiry date and batch number, these are linked to cells on the "label sheet"

It all works fine, BUT:
I need to be able to print a defined number of "sheets" of labels.
I have used a control button which runs a macro in the past to print a defined number of sheets using the following code:
Sub LABELPRINT()
'
' LABELPRINT Macro
'
Sheets("LAYOUT").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet1").Select
ActiveWorkbook.Close SaveChanges:=False
End Sub

I didn't write this as i don't have a great knowledge of VBA, I just recorded the macro

Is there any easy way of allowing the user to define hown many "copies" of the selected sheet that are printed.
I have tried to make a userform but i don't know quite how to use them.
Can i link a user input from a cell to define the number of copies?

I would really appreciate any help on this as it is driving me mad.
Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

This is how I would suggest that you do this:

Begin recording a macro.
Highlight all of the cells that you want printed.
Go to Print and choose the SELECTION option.
Print.
Stop recording the macro.

Now check to see if it printed the way that you want it to. Some options to manipulate the pages would be to hide rows/columns, insert page breaks, or even have the data show up on a different sheet formatted the way you want.

Does this help?
 

jimmywanna

Board Regular
Joined
Mar 18, 2002
Messages
182
Thanks for your reply, but have recorded macro's before to print just one copy of a sheet, I need the actual number of copies of the whole sheet to be variable.
Am going out of my mind now!!!!!
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962

ADVERTISEMENT

Here's what you can do to edit your existing macro:

Sub LABELPRINT()
'
' LABELPRINT Macro
'
Dim VarCopies as Integer
VarCopies = InputBox("How many copies would you like to print?","Enter Number of Copies",1)
Sheets("LAYOUT").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=VarCopies, Collate:=True
Sheets("Sheet1").Select
ActiveWorkbook.Close SaveChanges:=False
End Sub

Does that help?
 

Forum statistics

Threads
1,143,921
Messages
5,721,549
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top