Welcome to another #MacroMonday in my series. Have you ever needed to print only selected worksheets from within your Excel workbook? Maybe you want to keep some of your data hidden from view, or perhaps you have a lot of worksheets and don’t want to waste paper by printing them all. Whatever the reason, it’s pretty easy to print just the worksheets you need. In today’s blog post, we’ll show you how!
So, today I will share how to create a macro that will print selected Excel worksheets. This macro is an alternative to manually selecting and then printing them one at a time or holding down the CTRL key and selecting all of worksheets you want to print. Who wants to do that, right?. This macro is useful if you need to regularly print the same worksheet or worksheets. I do love this little macro, and find it especially useful when I have a regular report with a number of selected sheets I need to print every time. Let’s get started.
What Does The Macro Do?
Its prints only the selected Excel worksheets that you specify.
How Does It Work?
It is relatively simple. We need to specify the sheets we want printing using the PrintOut method. The PrintOut method triggers Excel to do the printing in an array. Excel prints the selected worksheets in one print job.
Step 1. Starting The VBA Code To Print The Selected Excel Worksheets
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
When you decide to write an Excel macro, you need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the macro in your Personal Macro workbook.
As I already know that this macro is a handy one I will store in in my Personal Macro workbook. This macro can be re used to print various selected or specified workjsheets So it is a keeper.
Step 2. Naming The Macro and Declare Variables.
First, a name is required for the Excel macro. In this example it is named PrintMySelection. Start by typing Sub then the name of the macro. Notice Excel will automatically insert the End Sub line of code. All other code is written between these two lines In the next line of code I declare variables. These declared variables allocate memory in Excel to store the values which are used later in the code. In this code the number of sheets to print is the variable. I have set this to three. There are three colleagues that require the selected Excel sheets. Makes sense.!
Step 3. Specify The Array To Conatin the Selected Excel Worksheets To Print.
The next step is to use an array to specify the names of the specified worksheets to print. I have contained these names in an array, a type of variable. However, creating the array to contain multiple worksheet names is more manageable than declaring each one as a variable, like the number of copies of the worksheets to print.
Step 4. Specify The Number Of Worksheet Copies.
So, this is the point in the code where the number of copies to print is set. As stated above, the number of copies is three.
Step 5. Ending The Macro.
That’s it. Once Excel prints the selected worksheets the code ends with the End Sub line of code. Ho simple is that?.Simple. Here is the code below. Feel free to copy and reuse it for yourself. Just change the named of the Excel worksheets to be printed.
Copt the code for free below.
Sub PrintMySelection()
'macro by HowToExcelAtExcel.com
Dim Copies As Integer
ActiveWorkbook.Worksheets(Array("MyTestSheet", "MyTestSheet2")).PrintOut
Copies = 3
End Sub
Even More Excel Tips.
