Printing Multiple Sheets
To print multiple sheets in the UI, you can use the Control and Shift keys to select multiple sheets, then print normally. In VBA, use an array of the sheets names as the argument to the Sheets property.
Below are two examples of how to do this. In the first, an array is assigned to a variant variable using the Array function. This allows you to specify which sheets are printed by identifying the sheet names as arguments to the Array function.
The second example loops through all the sheets and adds their names to an array. This particular example prints all the sheets in the workbook, but you could easily add an If statement to limit which sheets are added, and thus printed. In the end, you want an array of sheet names for only those sheet you want printed.
Sub PrintSheets()
Dim aShtLst As Variant
aShtLst = Array(”Sheet1″, “Sheet2″, “Sheet3″)
ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub
Sub PrintSheetsLoop()
Dim aShtLst() As String
Dim sh As Object
Dim lShCnt As Long
ReDim aShtLst(1 To ThisWorkbook.Sheets.Count)
For lShCnt = LBound(aShtLst) To UBound(aShtLst)
aShtLst(lShCnt) = ThisWorkbook.Sheets(lShCnt).Name
Next lShCnt
ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub
Andy Pope:
Hi Dick,
Here is an alternative that doesn’t require an array to store the sheetnames.
Sub PrintSheetsLoop2()
‘ No need for array of names
Dim sh As Object
Dim lShCnt As Long
Set sh = ThisWorkbook.ActiveSheet ‘ remember current
For lShCnt = 1 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(lShCnt).Select False
Next lShCnt
ThisWorkbook.PrintOut
28 May 2004, 6:10 amsh.Select True ‘ release grouped sheets
End Sub
shades:
Andy and Dick,
It looks as if both approaches might be necessary. If all the sheets needed printing, then Andy’s approach seems best. However, if only a select set (say 10 out of 25 worksheets) needed printing then the array approach seems best.
Have I understood both correctly?
28 May 2004, 8:51 amAndy Pope:
Hi shades,
Either approach will work for all or a subset of sheets.
You just have to add an IF THEN test within the loops to select or exclude sheets as required.
Although with the array method you will have to have a separate counter and redim the array as required.
Sub PrintSheetsLoop()
Dim aShtLst() As String
Dim sh As Object
Dim lShCnt As Long
Dim lCount As Long
‘ every other sheet
For lShCnt = 1 To ThisWorkbook.Sheets.Count
If lShCnt Mod 2 = 1 Then
lCount = lCount + 1
ReDim Preserve aShtLst(1 To lCount) As String
aShtLst(lCount) = ThisWorkbook.Sheets(lShCnt).Name
End If
Next lShCnt
ThisWorkbook.Sheets(aShtLst).PrintOut
End Sub
Cheers
28 May 2004, 9:26 amAndy
Tom Ogilvy:
If all sheets are to be printed out
Sub PrintAll()
Sheets.printout
End Sub
or
Sub PrintAllWorksheets()
Worksheets.Printout
End sub
for Andy’s first method, it assumes the currently selected sheet is to be printed out. Additional code would be needed if that were not the case. It is not difficult - a boolean flag would be sufficient.
Sub PrintSheetsLoop2()
‘ No need for array of names
Dim sh As Object
Dim lShCnt As Long
Dim bFlag As Boolean
bFlag = True
Set sh = ThisWorkbook.ActiveSheet ‘ remember current
For lShCnt = 1 To ThisWorkbook.Sheets.Count
If lShCnt Mod 2 = 1 Then
ThisWorkbook.Sheets(lShCnt).Select bFlag
bFlag = False
End If
Next lShCnt
ThisWorkbook.PrintOut
sh.Select True ‘ release grouped sheets
End Sub
–
28 May 2004, 6:32 pmRegards,
Tom Ogilvy
rec valera:
how if I want to print only first pages of my 10 worksheets, how would i do that?
14 September 2004, 11:46 pmrec valera:
how if I want to print only first pages of my 10 worksheets, how would i do that?
14 September 2004, 11:46 pmChristina:
I have multiple worksheets containing single charts. I need to print 4 charts to a PAGE, so I need to print 4 worksheets to one page. Any suggestions?!?!!? Thanks
21 January 2005, 12:02 pmCharles:
how do I make aq hyperlink to print multiple pages I specity?
Example: print sheet1,sheet3,sheet6,sheet8
so when I press the hyperlink it will print those 4 sheets.
Pleaese let me know if you can fgure this out
Thankyou
16 November 2005, 2:45 pmAvi:
Hi All,
I want to create a array of sheets with if condition.
Suppose in a excel file there are n number of sheets, I want to create a macro which select only those sheets name which are present in a file with if condition. I do not want to pass array manually and do not want to print all the sheets in a file.
I want to print selected sheets out of all the sheets present in a file.
Thanks in Advance.
30 January 2006, 2:39 amAvi
SleeplessInSomerset:
I’ve been trying to apply this and my code is as follows:-
Sub PrintComp()
Dim Printsheets As String
Dim APrintSheets As Variant
Printsheets = “”
If Worksheets(”Menu”).Cells(17, 4) = “Yes” Then Printsheets = Printsheets & “Comp” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(18, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Cap Allces” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(19, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Notes” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(20, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Q7″ & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(22, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Ind Bldg Allces” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(23, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Ag Bldg Allces” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(24, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Def Tax” & Chr(34)
‘End If
If Worksheets(”Menu”).Cells(25, 4) = “Yes” Then Printsheets = Printsheets & “,” & Chr(34) & “Proof”
‘End If
APrintSheets = Array(Printsheets)
ThisWorkbook.Sheets(APrintSheets).PrintOut
End Sub
I tried it with “ThisWorkbook.Sheets(Array(PrintSheets)).PrintOut” first of all but that failed as well. The error message I get is “Subscript out of Range”. The PrintSheets String comes out as “Comp”,”DefTax”,”Proof” with the current settings so that appears to be valid but I’m not sure why I’m getting the error message. Needless to say, I’m trying to select multiple but not necessarily consecutive sheets and based on user selection. Any reason why this doesn’t work when it appears to be similar to the first example shown on this thread?
My ultimate aim with this is to save paper when printing to a duplex printer ie when I used a macro to print selected sheets individually (based on user selection) I got each sheet on a separate sheet of paper (each sheet is just a single page) whereas when selecting manually it is possible to get the pages printed on both sides of the paper on a duplex printer. Will this macro achieve that objective or am I barking up the wrong tree anyway?
9 February 2006, 10:53 amProblemInProvo:
Did SleeplessInSomerset ever get an answer? I’m having the exact same problem. I’ve created a dynamic string that I want to pass into an Array so that I can print a number of worksheets at once. But the Array always barfs. What should I be generating with my If Thens?
13 July 2007, 1:43 pmJeremy:
Hi,
I love your blog and all the great relevant information I can found in it about Excel and VBA.
In order to “save” some paper I made a custom Macro Printing 4 Pages (selecting the print area from each page) in 1 Pages. The code is working for a Workbook with up to 24 pages:
Sub Print4To1()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(”PrintView”).Delete
Application.DisplayAlerts = True
‘Add a worksheet with the name “PrintView”
Set Basebook = ThisWorkbook
ActiveWorkbook.Worksheets.Add after:=Worksheets(Sheets.Count)
Worksheets(Sheets.Count).Name = “PrintView”
RowANbr = 1
RowBNbr = 1
ColANbr = 1
ColBNbr = 1
TotalSheet = ThisWorkbook.Worksheets.Count - 1
For i = 1 To TotalSheet - 1
Sheets(i).Select
myrange = Sheets(i).PageSetup.PrintArea
MyRangeRowNbr = Range(myrange).Rows.Count
MyRangeColNbr = Range(myrange).Columns.Count
Range(myrange).Copy
If i = 1 Then
Sheets(”PrintView”).Select
Cells(RowANbr, 1).Select
Sheets(”PrintView”).Pictures.Paste(Link:=True).Select
For k = 1 To MyRangeRowNbr
Sheets(”PrintView”).Cells(k, 1).RowHeight = Sheets(i).Cells(k, 1).RowHeight
Next k
For k = 1 To MyRangeColNbr
Sheets(”PrintView”).Cells(1, k).ColumnWidth = Sheets(i).Cells(1, k).ColumnWidth
Next k
‘—— Save Ligne and column where Sheet 1 stop stop in preview sheet
RowANbr = RowANbr + MyRangeRowNbr + 2
ColANbr = ColANbr + MyRangeColNbr + 1
Else
If xIsEven(i) = True Then
Sheets(”PrintView”).Select
If RowBNbr TotalRowB Then
TotalRow = TotalRowA
Else
TotalRow = TotalRowB
End If
Sheets(”PrintView”).Activate
ActiveWindow.View = xlPageBreakPreview
Sheets(”PrintView”).DisplayAutomaticPageBreaks = True
With Sheets(”PrintView”).PageSetup
.PrintArea = Range(Cells(1, 1), Cells(TotalRow - 1, TotalCol - 1)).Address
‘ Range(Cells(1, 1), Cells(TotalRow, TotalCol))
.PrintTitleRows = “”
.PrintTitleColumns = “”
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.196850393700787)
.BottomMargin = Application.InchesToPoints(0.196850393700787)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
‘ .PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = i
.PrintErrors = xlPrintErrorsDisplayed
End With
For NbrDePage = 1 To (i - 1)
12 November 2008, 7:14 amj = 1
myrange1 = Sheets(j * NbrDePage).PageSetup.PrintArea
TotalRow1 = Range(myrange1).Rows.Count
myrange2 = Sheets(j * NbrDePage + 2).PageSetup.PrintArea
TotalRow2 = Range(myrange2).Rows.Count
myrange3 = Sheets(j * NbrDePage + 1).PageSetup.PrintArea
TotalRow3 = Range(myrange3).Rows.Count
myrange4 = Sheets(j * NbrDePage + 3).PageSetup.PrintArea
TotalRow4 = Range(myrange4).Rows.Count
TotalRowPageOdd = TotalRow1 + TotalRow2
TotalRowPageEven = TotalRow3 + TotalRow4
If TotalRowPageOdd