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.
Step 4:
Step 5:
Step 6:
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
