Solved

Excel VBA print copies of sheets based on number in cell

Posted on 2011-09-20
5
332 Views
Last Modified: 2012-08-13
Hi Experts.

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.

Sub book2test()
   Dim sht As Worksheet
   For Each sht In ThisWorkbook.Worksheets
       sht.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
   Next sht
End Sub

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.

Thanks,

Will


0
Comment
Question by:willnjen
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36570180
Sub book2test()
   Dim sht As Worksheet
   For Each sht In ThisWorkbook.Worksheets
       sht.PrintOut Copies:=sht.Cells(3,7).Value, Collate:=True, _
        IgnorePrintAreas:=False
   Next sht
End Sub

Open in new window


Should work
0
 

Author Comment

by:willnjen
ID: 36570230
Hi Andrewssd3

That sort of worked.  The VBA halted when it found a zero in G3 on a sheet I don't want printed.  
I suppose it needs to look in G3 first and if it's zero then move on but if it's a positive number, then print.

Cheers,

Will
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36570255
Sub book2test()
   Dim sht As Worksheet
   Dim numCopies As Integer
   For Each sht In ThisWorkbook.Worksheets
       numCopies = sht.Cells(3,7).Value 
       If numCopies > 0 Then
           sht.PrintOut Copies:=numCopies, Collate:=True, _
              IgnorePrintAreas:=False
       End If
   Next sht
End Sub

Open in new window

0
 

Author Comment

by:willnjen
ID: 36570285
Great, that works well.  

Any idea how I get it to allow the user to choose a printer at the start?
0
 

Author Closing Comment

by:willnjen
ID: 36570347
Actually, I don't it's fair of me to add the question about choosing printers as that's a whole new can of worms.  I'll spend some time looking into it myself.  

Thanks for your help.

Will
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now