Use Excel VBA to automate the printing

use-excel-vba-to-automate-the-printing

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

More Posts