Solved

Excel VBA print copies of sheets based on number in cell

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

867 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

16 Experts available now in Live!

Get 1:1 Help Now