Excel VBA print copies of sheets based on number in cell
Posted on 2011-09-20
I have a workbook with multiple sheets. I want a macro to print a copy of MOST sheets and I want several copies of some sheets. All sheets I want to print have pre-defined print areas.
So to achieve this, in cell G3 on each sheet I have a number which indicates the number of copies of that sheet I want printed. In sheets I don't want printed, I've put a 0.
I've written this VBA which successfully prints each sheet once.
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.PrintOut Copies:=1, Collate:=True, _
How do I edit this so the number of copies of each sheet is retrieved from cell G3?
While I'm at it, this code above sends the prints ot the default printer. Can it be changed so the user can choose a printer at the start, then it goes about it's business of printing each sheet the correct number of times?
I hope this is clear.