Solved

Excel VBA print copies of sheets based on number in cell

Posted on 2011-09-20
5
372 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 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

740 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