Welcome, Excellers to another macro blog in my #MacroMonday #Excel 2019 series. Do you ever wish you could just highlight the selected range of cells in your Excel worksheet, then just print them?. With this small Excel VBA macro, you can. No more selecting a print range or going into print options to just the print the selected range. This code does it automatically for you. Sound like it would be useful and save time?. Good, read on and let’s get writing some simple code.
Preparing To Write The Code.
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. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. As you can see this code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro.
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply PrintSelection. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’] Sub printSelection End Sub [/stextbox]
Printing The Selection.
The next line of simple code simply takes the user-selected range and send it directly to the printer. Be sure to specify how many copied and if you want pages collated if you are printing no
[stextbox id=’info’] Selection.PrintOut Copies:=1, Collate:=True [/stextbox]
Ending The Code.
Once the selection has been printed the code ends. We already have the End Sub piece of code which was already inserted automatically when we started the Macro.
[stextbox id=’info’] End Sub[/stextbox]
Using The Print Selection Macro In Excel.
We have the Excel macro written, but if we want to be able able to use it super effectively then we can either
- Assign a shortcut key
- Add the macro to the Quick Access Toolbar
1. Assign A Shortcut Key.
To assign a shortcut key to your macro selecting
Tools | Macro | Macros and selecting the “Options” button at the bottom. An important point to note is that any key combinations you assign to macros will take precedence over any built-in shortcut keys. Shortcut keys are case sensitive and you can use just the Ctrl key or a combination of both the Ctrl key and Shift key if your letter is uppercase.
2. Add The Macro To The Quick Access Toolbar.
Once you have created your Macro, there are just a few short steps to get attached to a button on the Quick Access Toolbar.
- File – Options- Quick Access Toolbar
- In the Choose Commands From – select Macro
- Select the Macro you want to attach to
- Click Add to move the Macro to a list of buttons on the Quick Access Toolbar
- If you want to replace the Icon then click Modify
- Under Symbol, select the one you want
- Under Display Name – change the name if you want to use a more friendly name
href=”http://www.howtoexcelatexcel.com/newsletter-sign-up/”>
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
