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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 |
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
sh.Select True ‘ release grouped sheets
End Sub
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?
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
Andy
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
Regards,
Tom Ogilvy
how if I want to print only first pages of my 10 worksheets, how would i do that?
how if I want to print only first pages of my 10 worksheets, how would i do that?
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
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
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.
Avi
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?
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?
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)
j = 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
Im using MS EXCEL.
I prepared suppose 5 sheet.
Sheet 1
Sheet 2
Sheet 3
Sheet 4
Sheet 5
i want to get printout of Sheet 1,2,3,4,
How it works
How can i get printout of that sheets by using (only 1) button or one command
Can i any body guide me.
I m thankful to.
Im using MS EXCEL.
I prepared suppose 5 sheet.
Sheet 1
Sheet 2
Sheet 3
Sheet 4
Sheet 5
i want to get printout of Sheet 1,2,3,4, ( i don’t want to get printoout of Sheet 5)
How it works
How can i get printout of that sheets by using (only 1) button or one command
Can i any body guide me.
I m thankful to.
Thank you so much, very helpful!
I used the first version.
Gusman
Note to Osama: If you’re using the same workbook every time you print, I think you could create a macro to do the following:
1. Select all but the last sheet
2. Print
3. Select a single sheet (so that you won’t accidentally edit all sheets at once)
Then you could assign that macro to a macro button.
I have the following code to print a calendar by day. It’s done in a template in one worksheet. As such, each loop produces one print job for each calendar day. I want to do duplex printing. How do I structure the codes so that all 366 days can be printed in one print job.
Many thanks
Rebecca
Dim i As Integer
For i = 0 To 365
Range(“I1”) = DateAdd(“d”, i, “1/1/2010”)
ActiveSheet.PrintOut
Next i
End Sub
REDIRECT SELECTION IN CELL TO PRINT JOB
In spreadsheet_1 I’m creating several cells which contain drop-down menus so the user can select values from other spreadsheets in the same workbook (Excel 2003).
My issue is:
I would like the user to, once all data is selected, be able to print automatically the spreadsheet_1 AND all the spreadsheets selected in those cells with the drop-down menu when he selects print… Is it possible?
I’m new to programming and VBA, but I’m keen to learn!
Thanks in advance!
millawitch
I was trying to print 35 pages of a spreadsheet through the click of a vba macro on the first page. When I used the above code, it didn’t work :( Im very new at this, but I think I typed it in correctly. Could you explain why this wouldn’t work?
Sub PrintSheets()
Dim MyArray As Variant
MyArray = Array(“Title Page”, “Summary”, “Balance Sheet”, “Bal Sht Desc”, “Asset vs. Sales Trends”, “Asset vs. Liab & Equity Trends”, “Pct Bal Sht Desc”, “Pct Bal Sht”, “Current”, “Quick”, “Receivable turn”, “Inventory turn”, “Payable turn”, “Sales Fixed Assets”, “Sales Total Assets”, “Debt to Worth”, “Income Statemtent”, “P&L Trends”, “EBITDA Trend”, “Pct P&L Desc”, “Pct P&L”, “Depreciation as a & of Sales”,
“% Profit Assets”, “%Profit Net Worth”, “Times Interest Earned”, “Funded Debt to EBITDA”, “Cumulative Growth Rate”, “Cash Flow Statement”, “Cash Flow Desc”, “Cash Flow”, “Sales Working Capital”, “Debt Service Coverage”, “Z score”, “EM Score new”, “Conclusion”)
ThisWorkbook.Sheets(MyArray).PrintOut
End Sub
I look forward to your response, by the way these titles are the titles of each sheet I would like to print. Is this wrong?
hola para todos miren he creado un programa en base de formulas para algo aqui en colombia que se llama gota gota que son prestamos a corto tiempo tengo lobros con 110 clientes y un listado de cobro tambien con recibos pero en cada hoja tengo un recibo igual cada cliente es una hoja quiero una macro que selecione area de imprecion e imprima los 110 recibos ha y si tengo 60 clientes como hago aque el numero 61 no se imprima
gracias por su aayuda
hector
Whatever happened to the Print Report function for pre-2000 version excel??
Hi there,
I’m glad I found this post. The first code is exactly the one I was looking for.
However I need a small modification as I want to be able to print the sheets I want despite they are actually hidden.
How can include that in the following code? Many thanks
Sub PrintSheets()
Dim aShtLst As Variant
aShtLst = Array(“Sheet1”, “Sheet2”, “Sheet3”)
ThisWorkbook.Sheets(aShtLst).PrintOut
Something like this
I’m gaining a lot from this feed, but I’m at the next stage. My client has a spreadsheet with say 20 tabs in it and wants say 10 of those tabs to print to one PDF report, so 10 tabs are printed and a single PDF file is created. The issue is that the client knows they will be adding and removing tabs from the workbook which means the tabs they want to print will change from one month to the next. I noticed the line in the above code:
aShtLst = Array(“Sheet1”, “Sheet2”, “Sheet3”)
I have set up an area on a worksheet, say cells L21 to L40 where the user can specify which tabs they want in the report and what order they want them in – I’ve allowed 20 cells but realistically the client will not use all of these. Rather than using Array(“Sheet1”, etc. is there a way using VBA to read in the values in the cells L21 to L40 and generate one PDF report containing the spreadsheets on all of these tabs.
David: Something like this
Thanks Dick. A few tweaks needed but it worked well. Thanks. You filled the gap in my knowledge to get this done. Regards, David
This is great and very helpful! Thanks for the post, 15 years from the past Dick!