It’s that time of week for some Macro fun, I love starting off the week with some code!. In Macro Monday last week we looked at printing specified worksheets in an Excel workbook, which in itself is helpful. But, what if you have a large number of worksheets that you want to be printed except for just one?. In this quick and easy Excel tutorial, we’ll show you how to print all sheets in an Excel workbook – with specified exceptions. Whether you need to quickly print out the entire workbook for your boss, or just a few specific sheets, this handy trick will do the job in no time! So bookmark this page and give it a try today.
It makes more sense (and more efficient) to every worksheet in your Excel workbook and omit the one sheet you don’t want in this scenario. So let’s get started with writing a macro to do just that.
If you want to read last week’s Macro Monday blog post you can do so here.
What Does The Macro Do?
This Macro will print every worksheet in your Excel workbook, except any worksheets you specify and name yourself within the macro code.
How Does It Work?
This is again like last weeks Macro Mondays example. By using the PrintOut method to print all worksheets in the workbook but this time specify an exception using the does not equal to ‘<> ‘ piece of code. Lets get stuck into this macro!
Step 1. Create A New Module To Store the Code To Print An Excel Worksheet.
First, 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.
Before we begin to write any code, you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?. If you save the macro in your Personal Macro workbook, it will be available in any Excel workbooks. However, if you store it in the current workbook, then use is restricted to that workbook. I may want to reuse the code in this instance, so I hold it in my Personal Macro Workbook. When you select this option, Excel creates (if not already created) the Personal Macro Workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb.
Step 2. Name The Macro And Declare Variables.
Next, insert the new module. Type Sub and the then the name of the macro. In this example it is a long winded but you know what the macro is for. Notice Excel automatically inserts the End Sub code. Subsequent lines of code all need to be typed in between these two lines. Any variables also need to be declared. In this macro one variable is to be declared. IT is an object called ws. By declaring this as a variable Excel creates a memory container for each of the worksheets we loop through. These will be the worksheets Excel will print.
Sub PrintsheetsExceptMyTestSheet()
Dim ws as Worksheet
Step 3. Use The For Each Loop To Print Worksheets Not Named MyTestSheet.
Using loops in your Excel VBA usually help you reduce the number of lines in your code. They do the work in minutes which can take hours to a human being. Sounds like a plan right?. The syntax of the For Each Loop is simple.
For Each object-name In Range
So, we get Excel to begin looping though all of the worksheets to print in the active workbook, to see if the a sheet name is not equal to MyTestSheet. Following the syntax we can see the object name is ws in the active workbook worksheets being the range. Excel loops through all of the worksheet names. So, if the worksheet name is not equal to MyTestSheet then the worksheet is printed. This looping continues until all worksheet names are completed.
For Each ws In ActiveWorkbooks.Worksheet
If ws.Name is <> "MyTestSheet" then
ws.Printout
Step 4. Closing The Loop and Ending The Macro.
The process ends once all of the worksheets are looped. Excel prints all of the worksheets with the exception of the worksheet named MyTestSheet. The macro ends with the End Sub line of code. Excel automatically inserts this line of code when t
End If
Next ws
End Sub
Step 5. Test Your Macro and Copy The Full Code – the fun bit!
Finally, it is time to test your macro. Why not download the full code, change the name of a worksheet you do not want to print then give this Excel macro a go?.
Sub PrintsheetsExceptMyTestSheet()
Dim ws as Worksheet
For Each ws In ActiveWorkbooks.Worksheet
If ws.Name is <> "MyTestSheet" then
ws.Printout
End If
Next ws
End Sub
So, in this article learn how you can print all Excel worksheets with the exception of a few specified in the macro code.. This is one way to save time! It’s easy-to-follow and will allow you to avoid any confusion about whether or not you’re actually printing what you want. You’ll also be able to use this article as reference when it comes time again that you need to do this task! An Easy way way to print only specific Excel worksheets.
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
