Macro for multiple prints

L

Leporello

I have a simple three sheet workbook which contains the macro below to print
one part of one of the sheets. I wish to amend it to print several copies,
depending on the user input in cell G16 of sheet "Input". Can I alter the
Selection.Printout line of the macro or should I use a loop structure? In
either case some help on the syntax would be appreciated.

Sub Button6_Click()
'
' Button6_Click Macro
' Macro recorded 03/08/2009 by
'
Sheets("Mark sheet").Select
Range("A1:H56").Select
Range("H56").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Input").Select
Range("A1").Select
End Sub
 
J

JLGWhiz

This is one way:


Sub Button6_Click()
'
' Button6_Click Macro
' Macro recorded 03/08/2009 by
'
Sheets("Mark sheet").Select
Range("A1:H56").Select
Range("H56").Activate
numCpy = Application.InputBox("Enter number of Copies")
Selection.PrintOut Copies:=numCpy, Collate:=True
Sheets("Input").Select
Range("A1").Select
End Sub
 
L

Leporello

Thank you.

Your solution does work, but it creates a dialogue box which appears after
the macro has been called and requires a user input. The number of copies,
together with various other information, has already been entered into the
sheet "Input", and I really want a way of taking the existing value from the
"Input" sheet and using it directly to control the print operation.
Something like numCpy = Input!G16 but with the correct syntax to allow it to
work!
 
J

JLGWhiz

Then this should do it.

Sub Button6_Click()
'
' Button6_Click Macro
' Macro recorded 03/08/2009 by
'
Sheets("Mark sheet").Select
Range("A1:H56").Select
Range("H56").Activate
numCpy = Range("G16").Value
Selection.PrintOut Copies:=numCpy, Collate:=True
Sheets("Input").Select
Range("A1").Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top