Use Excel VBA to automate the printing

msofficeshare logo

Requirement:  Visual Basic Knowledge

File to download:  PrintFile.xlsm

 

A “paper-less” office has been advocated for many years but you probably still see many people are printing tons of paper every day.  Many people are still more accustomed to see things on papers rather than on computer monitors.  It is also more convenient to use paper reports in a meeting.  Many people like to write down notes on everything they come across so paper reports are still a preferable choice.  I think we still have a long way to go before reaching a “paper-less” status, if we ever achieve that.

To print a report from one Excel worksheet is a no-brainer for most people.  All you need to do is to identify the print area, define the header or footer if necessary, and decide what mode (portrait vs. landscape) to print.  There are additional printing options that a user can choose, but this is not the focus of this article.

If there is more than one Excel worksheet, most people will print them one-by-one.  I tried to automate this and could potentially save a few minutes here or there.  Time savings can add up easily if you need to do such printing every day.

This Printing Utility File allows the user to preset what worksheets to be printed out and how page numbers will be presented.

This Printing Utility File is created as a separate file from the file that the user needs to be printed.  This allow more flexibility and versatility of such Printing Utility File.  User can incorporate some of the VB codes in the file to be printed instead of having two separate files.  An alternative is to save such Printing Utility File as an Adds-in template, so everyone in the same office can access the VB codes through the Adds-in.

To use this file, please follow below steps:

Step 1:  Please "enable" the VB module to make it work.

Step 2:

Step 3:

Sub AddCheckbox(BoxName, BoxRow)

 

    Dim BoxLeft As Double

    Dim BoxTop As Double

    Dim BoxHeight As Double

    Dim BoxWidth As Double

 

    BoxLeft = Cells(BoxRow, "B").Left

    BoxTop = Cells(BoxRow, "B").Top

    BoxHeight = Cells(BoxRow, "B").Height

    BoxWidth = Cells(BoxRow, "B").Width

 

    ActiveSheet.CheckBoxes.Add(BoxLeft, BoxTop, BoxWidth, BoxHeight).Select

    With Selection

        .Characters.Text = BoxName

        .Value = xlOff

        .LinkedCell = "A" & BoxRow

        .Display3DShading = True

    End With

End Sub

 

A user may want to have all the printed worksheets with page numbers in consecutive orders.  For example, worksheet(1) has 3 pages, so its page number will be from p.1 to p.3.  Worksheet(2) has 4 pages.  The user wants it to start from p.4 to p.7.  Worksheet(3) has 2 pages and it will have page numbers from p.8 to p.9.  In this way, the user’s internal or external reports might look like it is from a seamless file and it is easy for the audience to see the sequence.

 

To generate consecutive page numbers for different worksheets, we need to first count the number of pages in each worksheet.  Below VB codes of subroutine “PageCount” will count the number of printed pages in each worksheet.

printer logo
pic1 print utility
pic2 print utility
pic3 print utility

Step 4:

pic4 print utility

Step 5:

pic5 print utility

Step 6:

pic6 print utility

Below is the VB Module for generating checkboxes.  It reads the worksheet name one-by-one and generate the same number of checkboxes.

Sub PageCount(ThisFile, FileChoice, n, WorksheetPage)

    Windows(FileChoice).Activate

    Worksheets(n).Activate

    ActiveSheet.DisplayAutomaticPageBreaks = True

    WorksheetPage = ActiveSheet.HPageBreaks.Count + 1

    ActiveSheet.DisplayAutomaticPageBreaks = False

    Windows(ThisFile).Activate

End Sub

Then we use the below subroutine “GetTotalPage” to sum up the result of subroutine “PageCount”.  A FOR loop is used to call the “PageCount” subroutine.

Sub GetTotalPage(ThisFile, FileChoice, n, WorksheetPage)

    Dim NumOfWorksheet As Integer

   

    Windows(FileChoice).Activate

    NumOfWorksheet = Worksheets.Count + 1

       

    Windows(ThisFile).Activate

    Range("BeginBox").Offset(0, -1).Select

    n = 1

 

    For n = 1 To NumOfWorksheet

        If ActiveCell.Value <> "True" Then

            ActiveCell.Offset(1, 0).Select

        Else

            Call PageCount(ThisFile, FileChoice, n, WorksheetPage)

        End If

    Next n

End Sub

Below subroutine “PrintWorksheet” will print each individual worksheet.  Please note that the user must first defines the Print Area of each individual worksheet.  Otherwise, the VB Module will generate an error message to prompt the user to define the Print Area for such worksheet.  The “PageNumChoice” variable determines if the user wants to print the file in “consecutive” mode or “separate” mode.

Sub PrintWorksheet(ThisFile, FileChoice, n, PageNum, TotalPrintPage, PageNumChoice)

    Windows(FileChoice).Activate

    NumOfWorksheet = Worksheets.Count

   

    Worksheets(n).Select

   

    If n > NumOfWorksheet Then

        Exit Sub

    End If

   

    WorksheetPage = Worksheets(n).HPageBreaks.Count + 1

    Worksheets(n).DisplayAutomaticPageBreaks = False

   

    If ActiveSheet.PageSetup.PrintArea = "" Then

        WorksheetName = ActiveSheet.Name

        MsgBox ("Please define Print Area for '" & WorksheetName & "' worksheet before printing.  '" & WorksheetName & "' worksheet will not be printed."), vbOKOnly

        Windows(ThisFile).Activate

        ActiveCell.Offset(1, 0).Activate

        Exit Sub

    End If

 

    If PageNumChoice = 1 Then

        With ActiveSheet.PageSetup

            .FirstPageNumber = PageNum

            .CenterFooter = "Page &P of " & TotalPrintPage

        End With

    Else

        With ActiveSheet.PageSetup

            .FirstPageNumber = xlAutomatic

            .CenterFooter = "Page &P of &N"

        End With

    End If

       

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _

    IgnorePrintAreas:=False

   

    PageNum = PageNum + WorksheetPage

   

    Windows(ThisFile).Activate

End Sub

Finally, the subroutine “PrintReports” is the main program that controls all other subroutines.

Sub PrintReports()

    Dim ThisFile As String, FileChoice As String

    Dim NumOfWorksheet As Integer, n As Integer

    Dim TotalPrintPage As Integer, WorksheetPage As Integer, PageNum As Integer, PageNumChoice As Integer

   

    ThisFile = ActiveSheet.Range("ThisFile").Value

    FileChoice = ActiveSheet.Range("FileName").Value

    PageNumChoice = ActiveSheet.Range("PageNumChoice").Value

   

    Windows(FileChoice).Activate

    NumOfWorksheet = Worksheets.Count

       

    Windows(ThisFile).Activate

    Range("BeginBox").Offset(0, -1).Select

    n = 1

 

    TotalPrintPage = 0

    WorksheetPage = 0

   

    If PageNumChoice = 1 Then

        For n = 1 To NumOfWorksheet

            If ActiveCell.Value = "True" Then

                Call PageCount(ThisFile, FileChoice, n, WorksheetPage)

                TotalPrintPage = TotalPrintPage + WorksheetPage

                WorksheetPage = 0       'Re-initialize variable

            End If

            ActiveCell.Offset(1, 0).Select

        Next n

    End If

   

    Range("BeginBox").Offset(0, -1).Select

    n = 1

    PageNum = 1

    For n = 1 To NumOfWorksheet

        If ActiveCell.Value = "True" Then

            Call PrintWorksheet(ThisFile, FileChoice, n, PageNum, TotalPrintPage, PageNumChoice)

        End If

        ActiveCell.Offset(1, 0).Select

    Next n

   

   

    On Error GoTo ErrorHandler

  

    Range("E7").Select

    MsgBox ("All selected worksheets have been sent to the printer."), vbOKOnly

       

ErrorHandler:

    Exit Sub

   

End Sub