Hello Friends,

Hope you are doing well !! Thought of sharing a small VBA code to help you writing a code to print the Workbook, Worksheet, Cell Range, Chart etc. .PrintOut () Method is used to print any Excel Object.

Syntax of .PrintOut Method

YourObj.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

Where:

  • YourObj (Required): It is a variable which represents your Object which you want to print. For example: Workbook, Worksheet, Chart etc.
  • From (Optional): Starting page number from which printing has to start. If this argument is omitted, printing starts from page 1.
  • To (Optional): End page number till which printing has to be done. If omitted, printing will be done till the last page.
  • Copies (Optional): This is the number of copies to be printed. If omitted, only one copy will be printed.
  • Preview (Optional): If passed as TRUE then Excel will invoke the print preview before printing the Object. If omitted, FALSE will be passed and hence excel will invoke the printing directly without showing the preview.
  • ActivePrinter (Optional): This sets the name of the active printer
  • PrintToFile (Optional): True is passed to print to a file. If it is not specified then user is prompt to enter an output file.
  • Collate (Optional): This is a Boolean type argument. TRUE is to collate multiple copies.
  • PrToFileName (Optional): If the above parameter PrintToFile is set to TRUE then you need to specify the name of the file you want to print to
  • IgnorePrintAreas (Optional): This is a Boolean type argument. If this argument is set to true then this function print the entire object.

Examples to Print Excel:

Based on above explanation and Syntax we will see examples of printing the Workbook, sheets, charts etc.

Example 1: VBA Statements to Print Objects with Default Options

In this set of examples, I am using all default options to print. This means I am not providing any other parameter to the method .PrintOut

1. VBA code to print ActiveWorkbook


Function PrintActiveWorkbook()
      ActiveWorkbook.PrintOut
End Function

2. VBA code to print Active Sheet


Function PrintActiveSheet()
      ActiveSheet.PrintOut
End Function

3. VBA code to print all WorkSheets


Function PrintAllWorkSheets()
      WorkSheets.PrintOut
End Function

4. VBA code to print a Single Sheet


Function PrintOneSheet()
      Sheets("Sheet1").PrintOut 'Sheet1 is the name of the Sheet which you want to Print
End Function

5. VBA code to print more than one Sheet


Function PrintMultipleSheets()
      Sheets(Array("Sheet1" , "Sheet2", "Sheet3").PrintOut 
End Function

6. VBA code to print Selected area of a Sheet


Function PrintSelectedArea()
      Selection.PrintOut 
End Function

7. VBA code to print Range of Worksheet


Function PrintRange()
      Range("A1:D5").PrintOut 
End Function

8. VBA code to print Excel Chart


Function PrintChart()
      Sheets("Sheet1").ChartObjects("Chart1").Chart.PrintOut 'Chart1 is name of the Chart
End Function

9. VBA code to print All Charts in a WorkSheet


Function PrintAllChart()
Dim ExcelCharts As Object
Set ExcelCharts = Sheets("Sheet1").ChartObjects
For Each Chart In ExcelCharts
    Chart.Chart.PrintOut
Next
End Function


10. VBA code to print All Charts in a Workbook


Function PrintAllChart()
	Dim ExcelCharts As Object
	For Each Sheet In Sheets
		Set ExcelCharts = Sheet.ChartObjects
		For Each Chart In ExcelCharts
			Chart.Chart.PrintOut
		Next
		Set ExcelCharts = Nothing
	Next
End Function

Example 2: VBA Statements to Print Objects with different parameters passed

1. VBA code to print From Page Number X to Page Number Y


'Below statement will print from Page No:2 to Page No:3
Worksheets("Sheet1").PrintOut From:=2, To:=3

2. VBA code to print more than 1 Copy


'Below statement will print 3 copy of the Sheet1 from Page 2 to Page no: 3
Worksheets("Sheet1").PrintOut From:=2, To:=3, Copies:=3

3. VBA code to Show Print Preview before actual printing


'Below statement will print 3 copy of the Sheet1 from Page 2 to Page no: 3
Worksheets("Sheet1").PrintOut From:=2, To:=3, Copies:=3, Preview:=True

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!