September 10, 2021

Print Specific Multiple Sheets Automatically

How to print specific multiple sheets automatically with VBA. Watch the video below:

Print Specific Sheets Automatically

Here’s the complete VBA code to automate the process of printing many worksheets in an Excel workbook:

Sub PrintSheets()

Dim SheetNames as Variant
Dim SheetName as String

SheetName = Application.InputBox (“Enter Sheet names separated by a comma”, “Print Now”

SheetNames = Split (SheetName, “,”)

Dim i as Long
For i = LBound(SheetNames) To UBound(SheetNames)
Debug.Print SheetNames(i)
Next i
Sheets(SheetNames).PrintOut Preview:=True

End Sub

Let’s imagine that we have a number of worksheets in a workbook and we wish to print a few specific worksheets quickly. We can attach the above macro to a form button.

When we click on the button, the macro code displays an input-box with the title ‘Print Now’. In the input area we can enter the names of the worksheets. These names must be separated by a comma. Let’s enter the worksheets names like capex, manpower and expenses separated by a comma. Next we click on the OK button. We are shown the print preview of the 3 worksheets. This is because we have set the property ‘Preview’ to true in our VBA code.

Now let’s have a look at the macro code. It is pretty simple. We have two variables: SheetNames and SheetName. When we enter the sheet names into the inputbox and click the OK button, the sheet names separated by a comma are split into an array of worksheet names using the ‘Split’ function. This array of sheet names is communicated to the printer. If we omit ‘Preview’, then our worksheets will be printed one by one. To understand how the ‘SPLIT’ function works we can display the data of the sheet names in the immediate window using Debug.Print. The image below shows how the data is displayed in the ‘immediate’ window. The immediate window can be accessed via the menu item ‘View’ or by pressing Ctrl + g in the Visual Basic for Applications window.

Data displayed in the immediate window

Further Reading: Print Multiple Sheets