Excel Macro : Excel VBA code to Print the Sheet

.

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

21 Comments

  1. Sam

    I have nearly 50 staffs in company with different names create a macros from 1 to 50 automatically (VBA CODE PLEASE)

    Reply
  2. Asoka

    Dear Vishwamitra

    Your WEBSITE has been an eye opner to me. Pl help me to do the following. – print one B & W worlsheet and one Colour print.

    When I tried printing with RECORD NEW MACRO this came up, & it works.
    “PRINT(1,,,1,,,,,,,,2,””Canon iP2700 series on Ne05:””,,TRUE,,FALSE)” ‘second 1 is for no. of copies. Notice- printer is after 2 and a comma
    ExecuteExcel4Macro “PRINT(1,,,2,,,,,,,,2,,,TRUE,,FALSE)” ‘First 2 is for No of Copies made for Color print.

    So how to change this code to get one B & W print and thereafter get one Colour print?

    I am a self study man and have never done an EXCEL course.
    Excell is a wonderful program and I get MACROs to ease my most work.

    Reply
  3. Andrea

    I have a spreadsheet with 32 worksheets.

    Sheet 1 and 2 will not need to be printed.

    Sheets 3, 4, 5 will be printed with current print ranges.

    I need Sheets 6 through 32 printed only if the formula in cell B5 is greater than 0.

    Can you help me with setting up a VBA code for this?

    Thank you!

    Reply
  4. David Dubinsky

    I am looking to just print out only selected items from a menu that includes over 500 items, hence all items will not be selected.
    The items on the menu run from columns A1 to F1 down to line 500.

    Goal: I am looking to write and then run a print macro that prints out only those menu items selected for a given day which is tied to values (the total number of meals / menu items selected in Column H5 to H500). This way the print out will only include items that were selected and will compress the printout to exclude items not selected.

    Can you guide me where to place the macro on my spreadsheet and in writing this macro?

    Thanks.

    David

    Reply
  5. Josean

    I want to select few sheets, but I want to write in a cell the sheets that i want to select. Is it possible?

    I´ve written the following:

    Public Sub SelecHojas()
    Dim HojaSelecionadas As String
    Dim HojaActiva As String
    Dim CeldaSelecionada As String

    *Here the programma fail
    HojaSelecionadas = Hoja1.Range(“B5”).value
    HojaActiva = Hoja1.Range(“B7”).Value
    CeldaSelecionada = Hoja1.Range(“B9”).Value

    Sheets(HojaSelecionadas).Select
    Sheets(HojaActiva).Activate
    Range(CeldaSelecionada).Select

    Could you help me?

    Reply
  6. rob

    this is very useful …
    i was wondering if you can help me with something …
    i have a standard sheet that i need to print it 100 times … but in that sheet , i have 2 cells .. 1st cell represents ” username ” the second cell represents password … so i have to change the username and password in each printed paper … how to do it ?
    i have usernames and passwords in another sheet …

    Reply
    • Jobo

      This is a possible method to solve your problem. Ill try to explain as I go to help out.

      Sheet1 will be the print page. Name of the username in A1 and name of the password in B1
      Sheet 2 will have the usernames and passwords
      Usernames will be in A:A and passwords in B:B

      Sub PrintDoc()
      ‘Declare your variants
      Dim rng as Range
      Dim cll as Range

      ‘rng is the range of the usernames (Given that the passwords are beside the usernames)
      ‘(eg sheet2 in cells A1:A100)

      ‘cll is the cell within the range that will contain the username.
      ‘One cell beside that is will contain the password.

      Set rng = Sheets(“Sheet2”).Range(“A1:A100”)

      ‘Will set the value in sheet 1 to each username and password in turn then print the page. Will only print 1-100 because of the range set.

      For each cll in rng
      With Sheets(“Sheet1”)
      .Range(“A2”).value = cll ‘ Username
      .Range(“B2”).value = cll.Offset(0,1) ‘Password.
      .PrintOut
      End with
      next cll
      End Sub

      If the password is not directly beside the username then change the offset by the number of cells away it is.
      Eg. If username is in B:B and password is in E:E change the offset to (0,3) because there is 3 columns between B and E.

      Before you run any macro’s make sure you save your document first. But I hope this solves your problem.

      Let me know how you went or if you need more clarification let me know. 🙂

      Jobo

      Reply
    • Jan smuts

      How do you combine “print multiple sheets” and the “range”of each sheet to be printed in one macro?
      with the the range being different in each sheet

      Reply
  7. Zul

    can we write a code to let excel print duplex (double-sided)?

    Tq

    Reply
  8. Sedarta

    Hi Josean

    I get my VBA print a different view from different excel version. Please be advice the tips for yielding a same printout from different excel version.

    Thanks

    Reply
  9. gulmat

    how do pages print at Excel active worksheet?

    for example

    From 2 to 5 and from 7 to 9

    Pages : 2-5,7-9

    how to?

    Reply
  10. Kevin

    Thanks for the tip. I’m trying to create a macro that will only print specific sheets using logic. Example: IF a cell contains X or Y, then print Sheet 1. Can you share a sample of what that code would look like (assuming it’s possible).

    Reply
    • Vishwamitra Mishra

      Dear Kevin,
      Yes, ofcourse it is possible to achieve this. all you need to do it put a condition in your print function: (Please read the comment i have provided in the comment.
      [code language=”vb”]
      Sub printConditionally()
      ‘Assumptions:
      ‘ Cell where X ot Y value are to be compared
      ‘ in Sheet1 (this is the name of the sheet) in
      ‘ cell (1,2) means B1 cell
      ‘ Change your sheet name and cell address according
      ‘ to your need
      ‘ Also, I have put condition with small letter or x or y
      ‘ you can ofcourse change it to capital, if you want

      With Sheets("Sheet1").Cells(1, 2)
      If .Value = "x" Or .Value = "y" Then
      ‘ your print statement goes here..
      Sheets("Sheet1").PrintOut
      End If
      End With
      End Sub
      [/code]

      Let me know if this was helpful. You can ask if you have any further question.

      Reply
  11. Jayant Parmar

    I wish to create a excel macro to print only section of sheet1 on each day text is entered.
    On 25th November 2017, section of text is created in column A from Row1 to Row7. It is printed on 25th November 2017.

    On 26th November, New section of text is added in Column A from Row9 to Row15.
    On giving print command on 26th November, printer should print only text added on 26th November leaving top print margin upto Row8 blank.

    Idea is to print only the text added on following day without printing text of above sections.

    Is there anyway, we can create a print macros to print section/sections with print command button autosetting top print margin.

    This is just like Bank passbook printing done by banking software printing records from start date to end date, autosetting top margin.

    Reply
  12. ASHUTOSH YADAV

    hello the explanation is very usefull. please tell me code to save pdf file in desired location.

    Reply
  13. Michael

    I looking to create VBA Coding in Excel that will allow me to Clear Contents, Print Worksheet 2, and Log contents clear to Worksheet 3. Is this possible??

    Reply
  14. habeeb

    i want to

    print the pay slips for each staff separately. the sheet is prepared by joining different sheet in the file using the volookup.

    Reply
  15. Louise

    Thankyou. I have been struggling for hours to record a print workbook macro for a college assignment in Excel for Mac. I can record a macro to do almost anything other than print for some reason. Finally a workaround and my assignment is complete. Many many thanks.

    Reply
  16. Jan Jacobs

    Good day.

    Wonder if you can please assist.

    I have multiple sheets in a workbook. I need to only print selected sheets based on a value in a specific cell (this differ page to page).
    My Cover page must always print, followed by the pages with a value in a specific cell.

    I have tried all sort of code but can not get it right.
    Can you possible help?

    Reply
  17. Qamar Aqil

    I have recorded mention micro.in this micro he is printing a page but i want to prevent it.i have tried all method but fail.
    please help me anyone.

    Sub TRP_Print()

    ‘ TRP_Print Macro


    Sheets(“Trucks Wise Report”).Select
    Range(“G11:H11”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“TRP WORK”).Select
    Range(“B3”).Select
    ActiveSheet.Paste
    Range(“D3”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Range(“D3:D117”).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets(“Travel Report Printing”).Select
    Range(“H3”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
    Selection.ClearContents
    Sheets(“TRP WORK”).Select
    ActiveSheet.ShowAllData
    Range(“B3:C3”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range(“B3”).Select
    Sheets(“Trucks Wise Report”).Select
    Range(“C10”).Select
    Selection.End(xlDown).Select
    End Sub

    Reply
  18. Abdul-Mubarik Sulemana

    hello bosses,
    i created a workbook for my students class assessment and i have a sheet where i can generate individual terminal report from the database sheet.i need help to create a print macro where i can automatically print select every student ID with its records and print the whole class report after terminal exams for each parent.

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Excel Macro : Excel VBA code to Protect and UnProtect Sheet - [...] Like .PrintOut Method this method has all Optional parameters too. WorkSheet.Protect(Password, DrawingObjects, Contents, [...]
  2. Complete VBA Guide to Interact with Text Files with Examples - […] VBA Guide to Interact with Text Files – Part – 2 of 2 Excel Macro : Excel VBA code…
  3. Excel Macro : Excel VBA code to Protect OR UnProtect Sheet - Let's excel in Excel - […] Like .PrintOut Method this method has all Optional parameters […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest