Hui’s Excel Report Printer

Posted on September 14th, 2011 in Excel Howtos , Huis , Posts by Hui , VBA Macros - 84 comments

Over a decade ago I was working on a very large and complex budget model, come to think of it I still am?

It involved 4 linked Excel workbooks, about 30 worksheets, all different, and multiple views of each worksheet.

There were regular Worksheets and Chart Sheets interspersed throughout.

Some of the Ranges had Outlined/Grouped Totals that were indented on some reports, but not on others depending on whom the various reports were going to.

It was a great budget model until you had to print a copy of it.

And of course the different levels of Managers all want different reports etc, etc.

 

The Solution

To solve this I developed a simple VBA routine which has evolved over the years to what is presented here.

The basic idea is to add a Printing Control sheet to your workbook.

This sheet has a list of print views, not Excel views, of various pages within the current workbook.

Each page can be setup as you wish and allows for a number of common parameters for each printed page.

Pages can be listed, multiple times if required, with different ranges or outlining selected each time

The Code handles Worksheets and Chartsheets, Normal and Named Ranges, Page Orientation, Page Size, Page Grouping and Headers/Footers.

As a user you setup the sheets as a list in the order you want them, with appropriate parameters.

The code then:

  • Loops through the list,
  • Obtain the parameters,
  • Sets up the print page and
  • Prints it.

You just need to sit back and wait for the printer to jam.

HOW DO I USE IT

Download the sample file here Excel 97-03, Excel 2007/10

You can use the sample file as is, for demo purposes or read on later where I describe how to use this in your workbooks.

Open the workbook and Goto the “Print_Control” worksheet.

Browse through the various Headings in Row 4 and field values below them.

Note that some of the Row 4 cells have comments in which explain what options are available.

Each field is described below:

No.

The Row No. in the list of page layouts available.

This has no use except when someone says the 5th page should be…

Description/Header

A text field that is used as a Reminder of the layout of the Page Setup also serves as a Centred Header.

Status

Print = On

Don’t Print = Off

The code only prints the pages marked as On.

Sheet

The name of the Worksheet or Chartsheet you want to print

Area

The Range on the Sheet that you want printed

Ignored for Chartsheets.

Land/Port

Specify if the page should be printed Landscape or Portrait

Ignored for Chartsheets.

Chartsheets are printed in Landscape.

Pages Wide

How many pages wide should the Range be printed on

This is fixed at 1 for Chartsheets.

Pages Tall

How many pages tall should the Range be printed out on

This is fixed at 1 for Chartsheets.

Copies

How Many Copies do you want of that individual page.

Rows & Columns

If outline/grouping is used specify what level of Indentation should be used for the Rows and Columns.

0 – Leave as is

1 – Indent 1 level

8  – Indent 8 levels

The maximum indentation is 8

Ignored for Chartsheets.

Footer (Left)

A description field printed as lower left footer.

No. of Copies

This specifies the Number of Copies of the Whole Report you want

Print All “On” Areas

The Print All “On” Areas Button executes the code and prints out a number of copies of the report as specified in the various page setups.

The printing is done on the default printer on your PC,

Important: Ensure that the printer you want to use for the job is set as the default before you start Excel.

You can print to a PDF file by specifying your Adobe or other PDF Printer as the Default Printer.

I’m sorry, This doesn’t fix the printing multiple pages to multiple files when printing to PDF issue.

 

Warning ! I maybe old school but I still recommend saving before printing !

 

HELP

There is limited help built into the system, That’s what this Post is doing.

Some of the field headings have comments which show what values are acceptable in those fields.

HOW DO I ADD THIS TO MY WORKBOOK ?

To add this to your workbook, copy the Print_Control worksheet to your workbook

  1. Open your workbook.
  2. Open the Demo File
  3. Copy the Print_Control worksheet by Right Clicking on the Print_Control tab, and copy to your workbook.
  4. Run the VBA Code using the “Setup Print Control Named Formula” Button

That’s it.

All the code required for the printing is part of the Print_Control page.

 

HOW DOES THE VBA WORK ?

The following describes the VBA Code driving this worksheet.

To examine this goto VBA (Alt F11)

Select the workbook and double click on Sheet0 (Print_Control)

The code should appear in the right hand window

If you are unfamiliar with VBA it may be worth going through Chandoo’s Crash Course in VBA

There are 2 Subroutines and a Function in this system which are documented below

 

Print_Reports

This is the main subroutine that drives the printing

It is called by the Print All On Button and when finished returns the user to the Print_Control worksheet.

All the VBA code is in RED,

Comments and notes are in BLACK before the line or section they refer to.

= = = = = = = = = = = = = = = = = = =

At the start of the Print_Reports subroutine, setup variables for later use

Option Explicit

Public Sub Print_Reports()

Dim PrintArea As Variant

Dim i As Integer

Dim j As Integer

Dim sht As Long

Dim Orientation As String

Dim NCopies As Integer

Dim PWide As Integer

Dim PTall As Integer

Dim Footer As String

Dim Header As String

Dim Sheets As String

Dim gRow As Integer

Dim gCol As Integer

Dim PaperSize As String

Dim msg As String

Dim tmp As String

Turn off the Automatic Calculation so that it is faster and isn’t as jerky

Application.Calculation = xlCalculationManual

This loads the entire array of the Print_Control page into an array called PrintArea

PrintArea = Worksheets(“Print_Control”).Range(“Print_Control”).Value

This sets up a loop for the No of Total Copies of the Whole report

For j = 1 To [Copies].Value ‘Loop through the No of Copies

This sets up a loop for the to check each line of the Print Control area

For i = 1 To UBound(PrintArea, 1) ‘Loop through the print area

If the Column Status is On print using that line of settings

If UCase(PrintArea(i, 3)) = “ON” Then ‘When On is enabled Print using the settings

Extract the settings from the stored array, row i

Header = PrintArea(i, 2) ‘Set Header variable

Orientation = PrintArea(i, 6) ‘Set Orientation variable

PWide = PrintArea(i, 8 ) ‘Set Pages Wide variable

PTall = PrintArea(i, 9) ‘Set Pages Tall variable

NCopies = PrintArea(i, 10) ‘Set No Copies variable

gRow = PrintArea(i, 11) ‘Set Row Group Expansion

gCol = PrintArea(i, 12) ‘Set Column Group Expansion

Footer = PrintArea(i, 13) ‘Set Footer variable

Check paper sizes against the built in page sizes

If PrintArea(i, 7) = “A4” Then

PaperSize = 9

ElseIf PrintArea(i, 7) = “A3” Then

PaperSize = 8

ElseIf PrintArea(i, 7) = “A5” Then

PaperSize = 11

ElseIf PrintArea(i, 7) = “Legal” Then

PaperSize = 5

ElseIf PrintArea(i, 7) = “Letter” Then

PaperSize = 1

ElseIf PrintArea(i, 7) = “Quarto” Then

PaperSize = 15

ElseIf PrintArea(i, 7) = “Executive” Then

PaperSize = 7

ElseIf PrintArea(i, 7) = “B4” Then

PaperSize = 12

ElseIf PrintArea(i, 7) = “B5” Then

PaperSize = 13

ElseIf PrintArea(i, 7) = “10×14” Then

PaperSize = 16

ElseIf PrintArea(i, 7) = “11×17” Then

PaperSize = 17

ElseIf PrintArea(i, 7) = “Csheet” Then

PaperSize = 24

ElseIf PrintArea(i, 7) = “Dsheet” Then

PaperSize = 25

Else

PaperSize = 9 ‘Defaults to A4

End If

Activate the relevant sheet

This checks that the sheet exists first

tmp = PrintArea(i, 4)

SheetExists(tmp) is a UDF that’s checks if the sheet exists and returns True or False

If Not SheetExists(tmp) Then

msg = “Sheet ‘” + PrintArea(i, 4) + “‘ not found.” + vbCrLf + “Check the sheets Name.”

msg = msg + vbCrLf + vbCrLf + “Processing will continue for remaining sheets.”

tmp = MsgBox(msg, vbExclamation, “Sheet not Found”)

Else

The sheet exists now process

Select the sheet

Application.Sheets(PrintArea(i, 4)).Select

Check if it is a Worksheet or a Chartsheet

If ActiveSheet.Type = -4167 Then ‘Its a worksheet

Turn off screen updating

Application.ScreenUpdating = False

Select the relevnt area of the sheet

ActiveSheet.PageSetup.PrintArea = PrintArea(i, 5) ‘Select the relevent Print Area of the Sheet

Set Outline levels

ActiveSheet.Outline.ShowLevels RowLevels:=gRow, ColumnLevels:=gCol ‘Set Outline Grouping

Apply print settings

With ActiveSheet.PageSetup ‘Set print settings

.PrintTitleRows = “”

.PrintTitleColumns = “”

.LeftHeader = “”

.CenterHeader = Header ‘User Defined Header (Shift to Left or Right as required)

.RightHeader = “”

.LeftFooter = Footer ‘User Defined Footer (Shift to Left or Right as required)

.CenterFooter = “”

.RightFooter = “”

.LeftMargin = Application.InchesToPoints(0.1)

.RightMargin = Application.InchesToPoints(0.1)

.TopMargin = Application.InchesToPoints(1.0)

.BottomMargin = Application.InchesToPoints(0.4)

.HeaderMargin = Application.InchesToPoints(0.1)

.FooterMargin = Application.InchesToPoints(0.3)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.CenterHorizontally = False

.CenterVertically = False

.Draft = False

.PaperSize = PaperSize ‘ User Defined Paper Size

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = False

.FitToPagesWide = PWide ‘User Defined No Pages Wide

.FitToPagesTall = PTall ‘User Defined No Pages Tall

.PrintErrors = xlPrintErrorsDisplayed

End With

Apply page orientation settings

If Orientation = “L” Then ‘User Defined Page Orientation

ActiveSheet.PageSetup.Orientation = xlLandscape

Else

ActiveSheet.PageSetup.Orientation = xlPortrait

End If

Turn Screen updating back on

Application.ScreenUpdating = True

Finished setting up Worksheet goto the Printing area

Else ‘Its a Chart page

Turn Screen updating off

Application.ScreenUpdating = False

Apply print settings

With ActiveChart.PageSetup

.LeftHeader = “”

.CenterHeader = Header

.RightHeader = “”

.LeftFooter = Footer

.CenterFooter = “”

.RightFooter = “”

.LeftMargin = Application.InchesToPoints(0.1)

.RightMargin = Application.InchesToPoints(0.1)

.TopMargin = Application.InchesToPoints(1#)

.BottomMargin = Application.InchesToPoints(0.4)

.HeaderMargin = Application.InchesToPoints(0.1)

.FooterMargin = Application.InchesToPoints(0.3)

.ChartSize = xlScreenSize

.PrintQuality = 600Change to 300 for Excel 97-03

.CenterHorizontally = True

.CenterVertically = True

.Orientation = xlLandscape

.Draft = False

.OddAndEvenPagesHeaderFooter = False ‘Removed from 97/03 Ver

.DifferentFirstPageHeaderFooter = False ‘Removed from 97/03 Ver

.EvenPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.RightHeader.Text = “” ‘Removed from 97/03 Ver

.EvenPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver

.EvenPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver

.EvenPage.RightFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.RightHeader.Text = “” ‘Removed from 97/03 Ver

.FirstPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver

.FirstPage.RightFooter.Text = “” ‘Removed from 97/03 Ver

.PaperSize = PaperSize

.FirstPageNumber = xlAutomatic

.BlackAndWhite = False

.Zoom = 100

End With

Turn Screen Updating back on

Application.ScreenUpdating = True

End If

Now Print the active sheet using user defined No. Copies

ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True

End If

End If

Next i

Next j

Clear PrintArea array, just in case

PrintArea = Null

Turn Auto Calculation back on

Application.Calculation = xlCalculationAutomatic

Go back to the Print Control sheet

Application.Sheets(“Print_Control”).Select

End Sub

= = = = = = = = = = = = = = = = = = =

The SheetExists Function

This is a Function that is used by the Print_Reports subroutine to check if a sheet exists.

= = = = = = = = = = = = = = = = = = =

Function SheetExists(SheetName As String) As Boolean

‘ This function Returns TRUE if the sheet exists in the active workbook

SheetExists = False ‘Set default value of SheetExists

On Error GoTo NoSuchSheet ‘Set error trapping such that if the sheet doesn’t exist it will exit

Check length of sheet name, if the sheet exists it will return a value, otherwise an error

If Len(Sheets(SheetName).Name) > 0 Then

The sheet exists so set SheetExists = True and exit

SheetExists = True

Exit Function

End If

NoSuchSheet:

The sheet doesn’t exists so use default SheetExists = False and exit

End Function

= = = = = = = = = = = = = = = = = = =

The Setup_Print_Control_Named_Formula Subroutine

This is a simple subroutine that sets up the 2 named formula for use the first time a sheet is used.

= = = = = = = = = = = = = = = = = = =

Sub Setup_Print_Control_Named_Formula()

Setup Named Formula “Print_Control” which is the table of settings

ActiveWorkbook.Names.Add Name:=”Print_Control”, RefersToR1C1:= _

“=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))”

ActiveWorkbook.Names(“Print_Control”).Comment = _

“Used by the Print_Reports Subroutine”

Setup Named Formula “Copies” which is the No of Copies of the Whole Report

ActiveWorkbook.Names.Add Name:=”Copies”, RefersToR1C1:= _

“=Print_Control!R26C13”

ActiveWorkbook.Names(“Copies”).Comment = “Specifies the No. of Copies for the Print_Reports Subroutine”

End Sub

= = = = = = = = = = = = = = = = = = =

NAMED FORMULA

The code relies on two Named Formulas

Copies:

=Print_Control!$L$27

Print_Control:

=OFFSET(Print_Control!$B$4,1,,COUNTA(Print_Control!$B$5:$B$24),COUNTA(Print_Control!$4:$4))

Automatically adjusts the Print_Control Named Formula for the number of Page Setup lines and Fields to be processed

If you have queries about how any of the above code works, please let me know in the comments below:

 

WHAT DOES THE ARRAY “PrintArea” DO ?

The print area array stores the values of the Print_Control range in a 2 dimensional array which represents the Print_Control range.

This is done for a few reasons, but simply it is faster as it results in less reading of the worksheet

It also allows more flexibility in the subsequent processing as all the data is in one area.

 

DOWNLOADS

Download the sample file here Excel 97-03, Excel 2007/10

 

WHAT’S NEXT

There are a number of parameters used in the Print Setup area which are not used or not used in the 97/03 version.

The code above is easily extended to include these if you desire.

One day when I have a spare moment (Most likely in 2025!) I will add the option for automatic incremental Page Numbers.

 

CLOSING

This code has saved, my staff and I, hundreds and hundreds of hours over the past decade whilst printing complex Excel workbooks.

This functionality was also one of the more requested issues from our poll of 3 months ago We Want Your Ideas!

I hope you enjoy it as much as I have ?

 

Updates

I will be extending the functionality of this in the future and so if you have any suggestions, lets hear them in the comments below:

 

How have you tackled large print jobs ?

I look forward to your comments below:

 

Hui…

For a list of my other contributions at Chandoo.org please visit; Hui.

Written by Hui...
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

84 Responses to “Hui’s Excel Report Printer”

  1. Cyril Z. says:

    Woa!

    This is a really impressive macro, I will probably use it in one of my KPI Dashboard I'm currently building in my company

    Many, many thanks to you Hui. You're a invaluable contributor !!! (have you ever think of a fork of Chandoo.org ??? 🙂

  2. Oli says:

    We have a colour printer at work which defaults to B&W (company policy), however, changing this from Excel would be handy, but I suspect this aspect of the print setup is outside of the Excel object...

  3. bill says:

    this comprehensive tutorial is so unexpectedly wonderful. thank you. i particularly appreciated the insight into the use of "With ActiveSheet.PageSetup" and being able to pick up anything required for footers and headers. this will save me me a ton of time. thank you.

    you could copy this formula down the rows in column "O":
    =IF(D5="Off","-","Page: "&COUNTIFS($D$5:D5,"On")&" of "&COUNTIFS($D$5:$D$14,"On")&" -Print date: "&TEXT(NOW(),"mmm dd, yyyy")&" Copyright 2011 - Hui Corporation")
    and then reference the appropriate cell in the VBA call. This formual does not take into consideration of "pages" more than 1 page wide or 1 page tall. converting more of this formula to VBA and making the formula in column "O" a simple count of pages could probably take care of that. probably doing all the math in VBA is even better. i do not know how to write this formula in VBA.

    also, Hui, do you know of links to more information about pdf's in general? i find myself wanting to print a package of reports into the same pdf file from Excel. i can't figure out how to do that. at times. i want to take a pdf file and add the pages to a Word document (images are fine as long as there is some automated way to split the pdf into Word document "pages"). sometimes i just want to add a few of the pages of a pdf (as images) to a Word document. as far as i can tell this can't be done.

  4. Sluggy says:

    Chandoo and Hui: as we say in the US: Providence! I was just today sitting down to start creating a excel report writer and lo behold, what pops into my Inbox? A beautiul Excel report writer! Again, Providence!

    Thanks so much, this is super sweet and tasty.

  5. Sluggy says:

    Super sweet and tasty! A real time saver. thanks so much

  6. Chris says:

    This is very cool Hui.

    For me the fact that many "non Excel" users can't understand why a report may print, but the graphs don't print at the same time or why just the graph prints without the data tables etc, also the fact that each work sheet may have separate print formatting.

    It is a real confidence builder when people can use your files without having little issues or bugs that even printing a file can create for non frequent Excel users.

    It allows quick, easy & user friendly use of your files.

    Thanks

  7. Sid says:

    Hey, could Hui or someone else give a short description of exactly what steps to follow to use this in a different workbook.Seems really useful but I'm not able to use it by just copying over the the sheet Print_Control.What do i need to add or replace to the code?

  8. Hui... says:

    If you've copied the sheet to a new workbook
    Did you select the small button described in the instructions above?
    .
    If it didnt work keep reading below
    .
    Run tha VBA subroutine called
    Setup_Print_Control_Named_Formula
    .
    or
    .
    Right click on the small button
    Assign Macro
    Select Setup_Print_Control_Named_Formula
    Apply
    .
    or
    .
    Manually add the two named formulas described in the post above

  9. Rich says:

    neat. another thing as i was looking at the page size comment is that you could have drop down lists in various places that would limit choices

    On/Off; Landscape/Portrait; Page Size: Letter/Legal/etc. and Rows: 1-8

  10. Hui... says:

    @All, Thanx for the appreciative comments
    @Rich, I'm a minimalist, So don't like controls, but you can add them if you desire.
    @Bill, I do have a version that does allow you to include auto-incrementing page numbers into comments using a code #Pg#, But I stripped it out of this to keep it simple and functional and just demonstrate the techniques.

  11. Simon says:

    @Bill, in VBA you can use
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
    to print a package of Excel reports into one PDF (as long as all the reports are in the same Excel workbook).

    • Mia Celest says:

      Please help me also.... i need to put page # of # in printing excel...
      i already has a code
      Sub Button2_Click()
      ActiveSheet.Range("A1:K45").Select
      ActiveSheet.PageSetup.Orientation = xlLandscape
      Selection.PrintOut Copies:=1, Collate:=True
      MsgBox "Successful!"
      End Sub

  12. Pavel S says:

    “=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))” piece has a problem.

    In Russian locale formula parameters need to be separated by semicolons. So if the code throws error in Setup_Print_Control_Named_Formula subroutine, you would want to change commas to semicolons.

  13. Pavel S says:

    Pls discard my previous comment. Not true.

  14. Paul says:

    Great tool. Appears that the VBA code currently limits the number of worksheets it will print to 20 (sheets listed on rows 5 to 24 (Excel 2010 version). How do I modify so it will print more than 20? Also, it you have a version that allows for more flexibility with headers and footers, I would appreciate seeing it. One last item - are paper margins set in the normal Excel fashion or somewhere in the Print_Control worksheet? Again, wonderful tool - just made my life much easier - thanks.

  15. [...] Automatically Generate Report Variations using Excel [...]

  16. Barbara says:

    I have a workbook that includes 18 sheets with 160 named ranges.  My desire is to print to a pdf with each range showing up on it's own sheet.  I inserted your sheet into my workbook, removed your information and inserted mine pertaining to the workbook.  Some of my named ranges contain graphs, by the way, and I still included them.  When clicking the "Setup Print Control" button, I get the message that because of my security settings macros have been disabled.  I get the same message when clicking the "Print all on areas" button.  I saved and reopened the file, enabling macros and still get the error message. 

    I don't use macros very much, and your VBA code discussion is greek to me.  I saw the one comment about using "ActiveWorkbook.ExportasFixedFormat Type-xltype.pdf" and will try to incorporate that - if I can get this macro to run.  Any suggestions?  Thank you - PS - does your hair really look like that?

  17. Ninad says:

    Hi Hui,

    This is great. I am amending the code to add more page setup parameters and also to automatically populate the "visible" worksheets. When done, I shall post a link back at this thread for others. Thanks to you for this and it is indeed saving on tons of hours.

    I have retained the credit to you in the code.

    Regards,

    Ninad.    

  18. bret says:

    This macro works for Excel 2011 for MAC, with the following caveats:

    In the print macro:
    The EvenOddHeaderFooter errored out for me, deleting those lines (numerous) caused the rest of macro to work perfectly

    in the SetupPrintControl macro, the lines attempting to "insert a comment" error out.  Deleting those permitted the macro to work as intended.

    (i'm not familiar with VBS, so others feel free to correct anything unclear, or erroneous) 

    • Hui... says:

      @Bret
      Thanx for the feedback
      I don't have access to Excel 2011 Mac to test these type of things
      Unfortunately MS keep adding features to different versions which can result in small inconsistencies between versions

      I'm glad you worked it out in this case

  19. I thought about you and this blog post last night! I was tinkering around with linked images (via the Camera) when it occurred to me that they could be used to give the user a "print preview" of another spreadsheet. Since this spreadsheet program you've posted allows the user to define a print area - do you think a linked image (ie a "print preview" window) that automatically updates based on the print area supplied is useful feature? or, do you think it might just get in the way? 

  20. denis says:

    As you say This doesn’t fix the printing multiple pages to multiple files when printing to PDF issue
    I wonder if I could ask how ou do overcome this problesm. Is it caused ourely and simply by having different formatting/page setup settings oer worksheet?

  21. John says:

    This is not working for me when I got to install it.  I have may worksheets. 
     
    Any advice?

  22. Marc says:

    Hello Hui,

    Been looking forward using your print-areas macro, but I encounter a problem; When I open the 'Print-Areas.xlsm' file and then hit the 'Setup Print Control Named Formula' I get an error (visual basic 400 error). I looked at the code but can not solve it. I tried it on another PC as well, but the same error.

    As all people above do have it working, can it be a version problem of my software? I use Windows 7 and Excel 2010 professional Plus.

    Hope you can help me, the print feature will save me lots of work printing all the different pages in my excel file.

    Thanks,

    Marc.

    • Hui... says:

      @Marc
      can you email me the file
      Click on Hui...
      email add at bottom of page

      • Daniel says:

        Hui,

        This sounds almost to good to be true but I am having the same 400 error. Sadly I cannot upload the file (company policy) can you still help me?

        Also I was wondering if I can define multiple print areas on the same sheet with this.

        Many thanks in advance!

        • Hui... says:

          @Daniel

          The macro only sets up 2 named Formula
          These need to be

          Copies: =Print_Control!$M$26
          ie: The Number of Copies cell

          Print_Control: =OFFSET(Print_Control!$B$4,1,,COUNTA(Print_Control!$B$5:$B$24),COUNTA(Print_Control!$4:$4))
          This is the range from B5 to the lower right corner of the populated data area
          in the download file it is N14

          If these named Formula exist there is no need to run the "Setup print controls named Ranges" macro

          "Can define multiple print areas on the same sheet with this" - Absolutely
          Including collapsed or expanded groups

          This is all described in the post.

        • Daniel says:

          BTW I am using Excel 2013 if that changes anything

  23. arik edelman says:

    nice code very halpful
    thanks

  24. Brad says:

    Hi All,
    Using the 2003 version i receive a VB error, "Object doesn't support this property or method". If I rem out the following lines of the Setup_Print_Control_Named_Formula sub routine it will run but does nothing:
    'ActiveWorkbook.Names("Print_Control").Comment = _
    ' "Used by the Print_Reports Subroutine"
    'ActiveWorkbook.Names("Copies").Comment = _
    ' "Specifies the No. of Copies for the Print_Reports Subroutine"

    Not good with VBA so any ideas would be appreciated.
    Printing side of things is working fine if I manually enter the sheet names.
    Cheers,
    Brad

    • Hui... says:

      @Brad
      Those two lines only add comments to two Named Ranges and so aren't required

      The macro adds two Named Ranges

      If they are there the main macro will now run ok

      If it doesn't can you email me and I'll check

  25. Brad says:

    @Hui
    Thanks for the fast reply mate, yeah the named ranges are there. A bit of confusion by me, I thought it populated the table with the sheet names automatically. Pretty sure I have a code snipet around that can do that for me though 😉
    Cheers,
    Brad

  26. Brad says:

    Hi All,
    I have added a button and the following macro to populate the sheet names on the Print_Control table............ However it runs backwards, so it pulls the last sheet name and adds it to the first slot of the table.
    Anyone know how to reverse the order in which it pulls the sheet names?
    Cheers,
    Brad

    CODE:
    Sub PopSheets()
    'Populates Print_Control with workbook sheet names
    Dim Counti
    Dim SheetName As Variant
    Dim Cell
    Dim i As Integer
    Dim r
    r = "E"
    i = 3 + 1
    Counti = 1
    Worksheets("Print_Control").Range("E5:E24") = ""
    Application.DisplayAlerts = False
    For Counti = Sheets.Count To 1 Step -1
    If Sheets(Counti).Name "" And Sheets(Counti).Name "Print_Control" Then
    SheetName = Sheets(Counti).Name
    Worksheets("Print_Control").Activate
    With ActiveSheet
    i = i + 1
    Cell = r & i
    ActiveSheet.Range(Cell) = SheetName
    End With
    End If
    Next
    Application.DisplayAlerts = True
    End Sub

  27. Brad says:

    @Hui
    Sorry, I didn't see your post before I put up my code. Thanks for the assist though 😉
    Geez I should have seen that, as soon as i read your reply it clicked.
    Cheers,
    Brad

  28. Mani says:

    Hello Hui & Chandoo, it is wonderfull, amazing. I was just trying to find out a way for printing solution like this. It is really interesting, but you have contributed with your very hard work and dedicative effort. I thank you so much for sharing this one.

    With warm regards,
    Mani

  29. Nancy says:

    Hi All,

    I am using Excel 2010 and need the Page & P of & N to show in footer, where P and N changed based on the number of pages turned on? Also my print areas are variable so the number of pages printed for each sheet is not consistent.

    Any help is appreciated.

  30. Rahul Kumar says:

    Thanks sir This Is very Help full but i want to ask one thing suppose i Have an Excel worksheet C08 long than how can i print that sheet in one page or ..can i print long excel sheet one by one on different Pages..an excel Sheet having 30 wide Length than can i print 6 column in one page and 6 another page automatically..

    Thanks
    Rahul Kumar

  31. Marc says:

    I just created 100 pages of daily lesson plans for my son's homeschooling this year using your routine.

    Thank you Jesus!

  32. Toni says:

    I've never been comfortable with excel cause it's math-we're not a very good mix. But I am learning a lot from your newsletters, thank you. I have a workbook with 10 worksheets that I tried this formula on. It does work but it prints out 2 pages onto one. I also can't figure out how to make it print with different headings, ie: sheet b, section one is cars, sheet b section two is trucks. each has to include the headings but print out on separate sheets. Maybe this isn't the formula to use?

  33. Carolyn says:

    I'm curious as to whether the whole printing multiple pages to multiple files when printing to pdf thing ever got a work around? Is there a way to have all of them automatically save in numerical order to a specified folder? That way you could just ctrl - a and put them in a pdf merger and do it quickly or do I actually have to sit there and name each pdf manually for them to save? I have a 70 sheet monthly board package that has to be ordered in certain ways for sum(start:ends) - index match add ups for the 6 companies bogs the speed down too much. The on/off report is great for when I print, but ultimately I need that fresh pdf.

    Thanks,

    Carolyn

    • Hui... says:

      @Carolyn
      It would be fairly simple to change the code to save each sheet to a Sequentially numbered PDF file
      Give me a few minutes

      • Carolyn says:

        awesome! thank you!

      • mikro says:

        Hello,
        As Carolyn, I too want to send all the ("ON" status) worksheets to a .pdf file but I want them in one file.
        In other words, I want to run your macro and end up with all the same sheets in a single .pdf file rather than printed.

        Any insights would be appreciated.
        Regards,

        PS. Thank you for such a useful macro!

      • Ben says:

        Hi Hui,

        Sign me up for this solution...this would save a good amount of time in consolidating a monthly 50 pg pdf report that we generate. Could you send me the solution you came up with for Carolyn?

        Much thanks!
        Ben

  34. david breaux says:

    thank you SO MUCH - - so kind of you to share this with us, and it's SO HELPFUL!!!!!

  35. david breaux says:

    i get an error when trying to run that says:

    ActiveWorkbook.Names("Print_Control").Comment = _

    Any thoughts?

  36. Chris says:

    Hello Hui. Looking to set up just the same thing and found your post. Cant quite get the Print_Range macro to work. When I copy this tab from your demo into my workbook when I first ran, it opened your original workbook again. No matter how I try I just can't seem to save the VBA into my local workbook. Now after trying for a while I get the VBA 400 error code that some others describe. I must be doing something really silly...

    Any help welcomed

  37. Mia Celest says:

    Please help me also…. i need to put page # of # in printing excel…
    i already has a code
    Sub Button2_Click()
    ActiveSheet.Range(“A1:K45?).Select
    ActiveSheet.PageSetup.Orientation = xlLandscape
    Selection.PrintOut Copies:=1, Collate:=True
    MsgBox “Successful!”
    End Sub

  38. Turner says:

    Hui -

    This is great stuff. One question, what about fitting the print area to size of the paper? I am printing legal, letter, legal. They are printing on the correct paper types, but the data is not adjusting.

  39. Ginger says:

    Hello. Thank you so much for an awesome post and routine!
    I would love it if you could please send me the solution you did for Carolyn to print multiple pages to a single PDF. I have a large workbook and need to print several pages as one print job to both a printer and PDF.
    It seems from reading the VBA code that there could be the case where another's print job can get pages inserted since your printing is done with a loop. Is that true?
    I had read that by using the Sheets.Printout method multiple sheets are sent out as one job, however I have the problem that it only uses the first sheet’s layout, zoom, etc for all the other sheets and ignores the printer settings manually put on. I really need to meet both requirements: one print job, multiple orientation, zoom, etc. Do you think this is possible? I appreciate so much your comments. Thank you!

  40. Nick says:

    Hello, i have a range and i named as Front_PrintArea and another range named Back_PrintArea. Those areas are in the same sheet of excel. Can i have in first area 75% scale for printing and in second 55%?

    • Hui... says:

      @Nick
      Yes
      But you will need to add another column to the Table with Print Size
      The adjust the code accordingly

      • Nick says:

        Please can you help me with this? I record a macro but isnt too clear for me..
        Sub PrintDouplexPages()
        '
        ' PrintDouplexPages ?a???e?t???
        '

        '
        Range("B2:AF44").Select
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$44,$AH$2:$AR$64"
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.078740157480315)
        .RightMargin = Application.InchesToPoints(0.078740157480315)
        .TopMargin = Application.InchesToPoints(0.21)
        .BottomMargin = Application.InchesToPoints(0.15)
        .HeaderMargin = Application.InchesToPoints(0.07)
        .FooterMargin = Application.InchesToPoints(0.11)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlOverThenDown
        .BlackAndWhite = True
        .Zoom = 75
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        Range("AH2:AR64").Select
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$44,$AH$2:$AR$64"
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.078740157480315)
        .RightMargin = Application.InchesToPoints(0.078740157480315)
        .TopMargin = Application.InchesToPoints(0.21)
        .BottomMargin = Application.InchesToPoints(0.15)
        .HeaderMargin = Application.InchesToPoints(0.07)
        .FooterMargin = Application.InchesToPoints(0.11)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlOverThenDown
        .BlackAndWhite = True
        .Zoom = 55
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        End Sub

        I dont know if i was clear for what i want so, in the same sheet i have the range B2:AF44 and i named this range "Front_PrintArea" and the range AF2:AR59 i named "Back_PrintArea". Can i have for Front 75% scale and for Back 55% scale? Because i want to print with duplex mode.
        Thanks

  41. Phil says:

    For some reason whenever I use this when it goes to print anything beyond page 2 I get a VBA error 400 warning. Any thoughts as to why?

    Thanks for any help you can provide. This is going to be incredibly helpful when it gets up and running.

    Thanks.

  42. Jeff Weir says:

    Hi Hui. Late to the party, but just wanted to say This is cool!.

    Funnily enough I arrived here after finding the code in a spreadsheet I'm helping someone troubleshoot, and the previous owner of the sheet had removed your name from the comments at the top and inserted his own. I googled some of the code, which led me here.

    Sooo busted!

    Cheers, Jeff

  43. Rajeev says:

    This is so very wonderful. The only request is to make it compatible with duplex printing.

    Thanks Hui

  44. Grant Hammond says:

    Hi

    I'm also getting the VB 400 error. I am running Excel 2013 so had to save my spreadsheet as an xlsm. I also note when I run the "setup print control" button, it opens your demo file and is not creating the named ranges in my spreadsheet because the button is linked to your spreadsheet name not mine. Have manually re-assigned macro and its now created named ranges, but still giving VB400 error. Will see if I can track it down with a few breakpoints

    .

  45. Grant Hammond says:

    ok sorted. VB400 error is because I had an invalid print range. Also had to manually reassign the macro for the print button as it was also still referencing to your demo spreadsheet macro.

  46. Grant Hammond says:

    Hmm...VBA for Excel is pretty clumsy compared to Access!

    Does not seem to be any way to assigned macros specifically to the current spreadsheet. If you copy the print_control worksheet into another spreadsheet the command buttons are still linked to the macro in the source spreadsheet!@#$ so the user then has to manually reassign the macros.That is just plain dumb!

  47. Grant Hammond says:

    ok here is modified code to fix the macro reassignments
    --------------------------------------------------------
    Sub Setup_Print_Control_Named_Formula()

    ' reassign macros to this spreadsheet
    ActiveWorkbook.Activate
    ActiveWorkbook.Sheets("Print_Control").Shapes.Range(Array("ButtonPrint")).Select
    Selection.OnAction = "'" & ActiveWorkbook.Name & "'!sheet0.Print_Reports"
    ActiveWorkbook.Sheets("Print_Control").Shapes.Range(Array("ButtonSetup")).Select
    Selection.OnAction = "'" & ActiveWorkbook.Name & "'!sheet0.Setup_Print_Control_Named_Formula"

    ' Setup Print Control Named Range
    ' not sure if this is necessary as named ranges come accross when worksheet is copied
    GoTo xx
    ActiveWorkbook.Names.Add Name:="Print_Control", RefersToR1C1:= _
    "=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))"
    ActiveWorkbook.Names("Print_Control").Comment = _
    "Used by the Print_Reports Subroutine"

    ActiveWorkbook.Names.Add Name:="Copies", RefersToR1C1:= _
    "=Print_Control!R26C13"
    ActiveWorkbook.Names("Copies").Comment = _
    "Specifies the No. of Copies for the Print_Reports Subroutine"

    ActiveWorkbook.Names.Add Name:="PrintToPDF", RefersToR1C1:= _
    "=Print_Control!R27C13"
    ActiveWorkbook.Names("PrintToPDF").Comment = _
    "Specifies wheter to print to pdf or paper"

    ActiveWorkbook.Names.Add Name:="OpenWithPDF", RefersToR1C1:= _
    "=Print_Control!R28C13"
    ActiveWorkbook.Names("OpenWithPDF").Comment = _
    "Specifies whether to open pdf after being created"

    xx:
    MsgBox ("Macros reassigned ...your ready to rock!")

    End Sub
    --------------------------------------
    you can see I have remmed out the named ranges as you dont need to create these in new workbook as they come over when the print_control worksheet is copied across.

    I've also changed following code to print routine so can choose print or pdf
    ------------------------------------------
    If Worksheets("Print_Control").Range("PrintToPDF").Value = "Yes" Then
    ' save to pdf
    Set ws = ActiveSheet

    'check/make directory
    strPDF = ThisWorkbook.Path & "\" & "PDF\"
    'check if tmp folder exists
    If Dir(strPDF, vbDirectory) = "" Then
    MkDir (strPDF)
    End If

    'define pdf filename
    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
    & ".PDF"
    '& Format(Now(), "yyyymmdd\_hhmm") _
    '& ".pdf"
    strFile = ThisWorkbook.Path & "\PDF\" & Format(i, "00") & "-" & strFile

    If Worksheets("Print_Control").Range("OpenWithPDF").Value = "Yes" Then
    bnOpenWithPDF = True
    Else
    bnOpenWithPDF = False
    End If

    'create pdf's
    ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=bnOpenWithPDF
    'Debug.Print strFile
    'MsgBox strFile & " created"
    Else
    'print to paper
    ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True
    End If
    -----------------------------------------
    This will create a subfolder called pdf and drop each print range in there as separate pdf, and open them after. I use Bluebeam pdf editor, which has a combine function so its only a couple of clicks to combine all the pdfs into one file.

    There are a few free pdf utilities that support VBA so could in theory write code to append all the pdf's into one pdf as part of this process.

    Hui...great printing tool here. This has been in the back of my mind for a number of years now, so great to finally find your site and solution

    Cheers

    Grant

    • Hui... says:

      @Grant

      Many Thanx Grant

      Fixing these issues has been on my to do list for a while
      I will be incorporating your code into the sample files with the appropriate recognition very soon

      Hui...

  48. Grant Hammond says:

    Its really humming now.

    I've added defaults for margins, 4 row header, 2 row footer that accepts formating and & codes for filemane, sheet name date page no etc and printing to PDF. PDF option prints each sheet to temp PDF file, then shells out to PDFTK.exe to combine the PDF's into one PDF with same name as spreadsheet, then deletes temp PDF's

    About then only thing I'd still like to add would be to be able to list a number of external pdf files to also combine as part of the single pdf doc....hmm I can probably do that too. Will report back if I can get that to work.

  49. Steve says:

    Hi Hui

    Just found your Excel Report Printer which could really help me produce a monthly invoice and statement run with a few mods if possible. Saving to individual PDF files is good but could they automatically save each file as the names listed in column C (Description Header) to a single pre determined file folder?

    Thank you!

    • Hui... says:

      @Steve

      This happens because of the way Microsoft Excel sends the print job. Excel assumes that all your individual sheets have different page setups, so it sends them as multiple print-jobs.

      You can override this using the technique here:
      http://www.tracker-software.com/know...n-a-single-PDF

      If the pages do have different setups, some PDF drivers allow printing of multiple pages to a single PDF file
      Have a read of http://www.novapdf.com/kb/printing-a...-file-135.html
      particularly Pt 5

      Another technique is that discussed here:
      http://www.clearlyandsimply.com/clea...d-version.html

      • Steve says:

        Hui

        I have looked at the links but they are not like your routine in that they offer little control or flexibility like your sheet does. Thanks anyway for your input.

        • Jason says:

          I Think I have a solution for the pdf's. I use PDFCreator. It can be selected as a printer. After you press the print button it starts a print action for al the sheets. PDFCreator lines up the prints in a que and it also has an option to merge the que.

          I hope it helps.

  50. Jason says:

    Hui,

    I have a different problem. When I input a number in the copies field in the "K" column it only prints one copie of that sheet. I used your Demo as it is.

    • Jason says:

      I have created a solution.

      I placed the line "ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True" in a for loop.

      It looks like this:

      End With
      Application.ScreenUpdating = True

      End If

      For c = 1 To NCopies
      ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True
      Next c

      End If
      End If
      Next i
      Next j

      I hope others will benefit from this too.

Leave a Reply