Excel Print Macro Tutorial

Excel Print Macros are a great start to automating printing out multiple worksheets and are a great place to get started with macros.

The syntax for Excel print macro sub routines is straight forward and it can save lots of mouse clicks and time when printing out multiple worksheets.

First I will give you the Excel Print Macro code , then I will explain how it works and finally I will explain how to modify the code to take your Excel Print Macro to the SuperStar level. Here is the code:

Sub Print_Monthly_Reports()

'/===============================================

'/ (c) SpreadSheet SuperStar, LLC

'/===============================================

ThisWorkbook.Worksheets("Sales").PrintOut

ThisWorkbook.Worksheets("Projection").PrintOut

ThisWorkbook.Worksheets("Sales Chart").PrintOut

End Sub

Excel Print Macro – How Does It Work?

There are two components to this Excel Print Macro:

  • first is connecting to each Worksheet and
  • second is the PrintOut method

ThisWorkbook refers to the workbook where the code module is located. In this case I am using ThisWorkbook instead of ActiveWorkbook because the intention is to drill down into a specific worksheet which is contained in ThisWorkbook. The ActiveWorkbook command connects to the workbook that is active and selected. In this case, I do not use ActiveWorkbook because I am never sure if the ActiveWorkbook contains a "Sales" worksheet for example.

ThisWorkbook.Worksheets("Sales") connects to the "Sales" worksheet within ThisWorkbook. You can modify this connection easily if you have a different worksheet name by changing the text string "Sales" to whatever the name of the worksheet you want to print out.

PrintOut Method. Anything you can do in Excel using menus and a mouse you can do via VBA. The act of doing (verb) in computer programming is called a Method. Most methods in the Excel VBA object model are logcially named. So if you want to "print out a worksheet" you would use the PrintOut method on a worksheet object.

EXCEL VBA EXERCISE #1:
Write one line of VBA code that prints a worksheet called "Monthly Sales"
The solution is at the end of the article…

Excel Print Macro – SuperStar Style

The Excel Macro featured above is basic: it prints out one worksheet per line to the default printer. However when printing out a worksheet manually there are many settings that allow the user to select the number of copies to be printed, the name of the printer where the print job is routed, collate options and pages to be printed. Here is a sample Excel Print Dialog:

Excel 2007 Print Dialog

Each one of the controls in the print dialog is accessible via parameters of the PrintOut method. A parameter is a setting of the method. Again I will give you some sample code and then explain how it works.

Sub PrintOut_Parameters()

'/===============================================

'/ (c) SpreadSheet SuperStar, LLC

'/===============================================

With ThisWorkbook.Worksheets("Sales")

'/ Prints 12 copies of "Sales"

.PrintOut Copies:=12

'/  Prints Pages 2 to 4 of Sales

.PrintOut From:=2, To:=4

End With

End Sub

The syntax of VBA parameters is the:=. So for example Copies:=12 sets the copies parameter to 12 and when you run this line of code the PrintOut method will print 12 copies of the worksheet. A more complex version is setting multiple parameters simultaneously as we did in this line of code: .PrintOut From:2, To:=4 Here we are setting the first page printed out to be page 2 and the last page printed out to be page 4.

What happens when you don't set a parameter? When you don't set a parameter VBA will use the method's "default" value. The simplest example is the PrintOut method's number of copies parameter: the default value is always 1. It gets a little trickier with values such as the printer where the last saved value is stored in Excel and this is used as the default value. It is always good to be aware if Excel is using a "last saved default parameter" or a default parameter that is continually reset to a value, such is the case for the number of copies parameter.

EXCEL VBA EXERCISE #2:
Print out 2 copies of the first page of a worksheet called "Monthly Sales"
The solution is at the end of the article…

Excel Print Macro – Exercise Solutions

Sub PrintOut_Macro_Exercises()

'/===============================================

'/ (c) SpreadSheet SuperStar, LLC

'/===============================================

'/ EXERCISE #1: Print Out "Monthly Sales"

ThisWorkbook.Worksheets("Monthly Sales").PrintOut

'/ EXERCISE #2: Print Out 2 Copies of Page 1

With ThisWorkbook.Worksheets("Monthly Sales")

.PrintOut Copies:=2, From:=1, To:=1

End With

End Sub

Connect With Excel SuperStar

Subscribe to our e-mail newsletter to receive updates.

, ,

5 Responses to Excel Print Macro Tutorial

  1. Miss Shaz Jones December 17, 2012 at 7:44 am #

    Hey Adrian,

    Another great post. Thanks!

    If I want to print pages 1-7, 9-11, 13-14 (i.e. everything except pages 8 & 12) how do I do that?

    • Adrian Pask December 18, 2012 at 3:13 pm #

      Great question. The way I would do this is to have multiple lines of code for each continuous print area. So for example:

      Sub PrintOut_Parameters()

      '/===============================================

      '/ (c) SpreadSheet SuperStar, LLC

      '/===============================================

      With ThisWorkbook.Worksheets("Sales")

      '/ Prints Pages 1 to 7 of Sales

      .PrintOut From:=1, To:=7

      '/ Prints Pages 9 to 11 of Sales

      .PrintOut From:=9, To:=11

      '/ Prints Pages 13 to 14 of Sales

      .PrintOut From:=13, To:=14

      End With

      End Sub

      One question: Is there a way to structure this report so that all the pages print out continuously? I recommend having a continuous print range because what often happens is the report expands and then page 8 is now page 11 and you have to modify the macro. If you have all the data in one continuous range and summaries in a second range or worksheet then you will have a much more robust structure. If you have any additional clarifications please comment below. Thank you – Adrian

  2. Davis December 20, 2012 at 4:31 pm #

    Hi Adrian,

    I am getting close to launching my website, so that I am not "Sitting on the Dock of the Bay". However, I am in the final phase of the puzzle for what I am trying to do. I have a form at the attached website. This form is submitted from users off the net and drops into an excel file. So I could of course have hundreds of rows of data from this form. The fields from this form populate cells in another workbook where calculations are done. I would like to have a print-output of a specific worksheet, that have the results saved to a filename associated with the email address for each form submitted. And then, bulk email the saved print-output filename (associated with email) out to the clients with the results of there input form.

    Any suggestions on this idea?, I think I am close.
    Thanks

    • Adrian Pask January 5, 2013 at 2:53 am #

      Hello,

      I checked out your site, nice job. It looks like it is coming along well.

      Great question, which has many parts. To me the deeper question is what are you attempting to do with the spreadsheet / email combination. One of the founding principles of SpreadSheet SuperStar is using the most effective tool for the task. When I started out in '95 that was spreadsheets for calculations, databases for data and Word for reports. Now the landscape is much bigger with the interwebs and the cloud giving us far more programming tools and expectations on the part of users are much, much higher. Generally I would advise using spreadsheets for in house analysis of data you get from the web and web server technologies like PHP to generate dynamic web pages for things like online quotes which react to user input.

      From you comment I'm not sure if the worksheet generation was for in-house use or you are emailing a quote to prospective client.

      The short answer is that yes for in house analysis for you can do it but it requires tons of VBA skills. Using Excel VBA you will need to connect to a web database with the user submitted data, extract that data into an Excel spreadsheet, generate the spreadsheet, and email the spreadsheet, probably using Outlook. For SpreadSheet SuperStar all of my internal dashboards and management reports are generated using Excel and VBA that automatically connect to web databases, pull down analytic data and format them. For internal use, the solution is very possible but the post explaining it would be the length of a book (or several books).

      If your goal is to generate automatic quotes that are automatically emailed to a client I would strong advise against using Excel. First, Excel is not designed to operate on a autonomous server, it is designed to be operated by a user. Pick a web technology that is designed to operate on a server. Secondly is more of a legal / email marketing problem, sending bulk emails from a individual/personal account, you should be using a bulk email service provider that compiles with the CAN-SPAM act and has tracking capabilities, for many reasons. Again automating Outlook with Excel is possible but I wouldn't advise using it in an autonomous server mode. Having said that I am playing around automating my email provider using web API calls from inside Excel, so this automation is also possible.

      Hope that give you somethings that gudie your thinking and answers your question a little bit – Adrian

      • Davis January 6, 2013 at 4:22 pm #

        Hi Adrian,

        I did manage to get the excel/email idea to work. It works very nicely with the help of some VBA. I really would like to dive into this idea some more. Thanks for the supportive feedback. I have to see how to use Word in my efforts some more. I have not tried that yet. I would like to basically have a system that is like a vacuum cleaner of information, and then process the data to send letters, pay bills and Analise results (ex. Trends). I will keep you posted.

Leave a Reply