?
Solved

Excel VBA print copies of sheets based on number in cell

Posted on 2011-09-20
5
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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