[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA print copies of sheets based on number in cell

Posted on 2011-09-20
5
Medium Priority
?
420 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 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 feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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