Macro 25: Print Specified Worksheets
If you want to print specific sheets manually in Excel, you need to hold down the CTRL key on the
keyboard, select the sheets you want to print, and then click Print. If you do this often enough, you
may consider using this very simple macro.
How it works
This one is easy. All we have to do is pass the sheets we want printed in an array as seen here in this
macro. Then we use the PrintOut method to trigger the print job. All the sheets you have entered
are printed in one go.
Sub Macro25()
‘Print Certain Sheets
ActiveWorkbook.Sheets( _
Array(“Sheet1”, “Sheet3”, “Sheet5”)).PrintOut Copies:=1
End Sub
Want to print all worksheets in a workbook? This one is even easier.
Sub Macro25()
‘Print All Sheets
ActiveWorkbook.Worksheets.PrintOut Copies:=1
End Sub
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded
macro and replace it with this one.
