The PrintOut method applies to a lot of different objects. You can print a Workbook, Worksheet, Chart, Collection of Worksheets, Collection of Charts, Window object and even a Range.
PrintOut has some useful arguments including the From and To arguments. These let you define which pages will print. If, for instance, you want to print every worksheet in your workbook, but only want to print the first page, you might use a sub like this:
Sub PrintPage1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PrintOut From:=1, To:=1
Next ws
End Sub
This will create a separate print job for every page it prints. If you want to print a lot of pages in one print job, you can use this method. You won’t be able to specify pages, though. If you tried to specify only the first page using that method, it would only print the first page of the whole print job, not the first page of every worksheet.
Hello Dick,
I often use the “.select Replace:=false” code to selectively send individual sheets to the printer as one print job. Here’s an example of how to print all of the visible sheets in a workbook as one print job:
Sub PrintSheets()
For Each sht In Worksheets
If sht.Visible Then
sht.Select Replace:=False
End If
Next sht
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
The code can be modified to pick and choose what sheets you want to print.
John Mansfield
How can you determine how many Printable pages are there in a excel workbook? Workbook.count only tells you how many sheets there are?
Thank You
You have to use the old XL4Macro commands to get this information.
GET.DOCUMENT(50)
tells you how many printed pages your worksheet has. In VBA you have to construct it like this:
Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”)
– Jon
And how do I know about GET.DOCUMENT(50)? Aside from having programmed extensively in XLM in the days before the WWW, I have a “modern” help file which gives all the old XLM commands as adapted for Excel 2000. It’s downloadable from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyID=C09BF7F7-D30E-4CE9-8930-5D03748CA5CD&displaylang=en
(That ugly URL is all one line.)