Not all recorded macros are re-usable. This print selection one is.
If you highlight a range in Excel and want to print it out – maybe you need to do a quick check of values – it takes a few clicks to do it.
A recorded macro can reduce the number of clicks.
If you are unsure how to use macros check out this post.
When you use Print Selection it doesn’t affect the existing print range on the sheet.
To print a selection you need to.
- Select the range.
- Click the File ribbon tab.
- Click the Print option on the right.
- Click the first option drop down under Settings and choose Print Selection.
- Click the Print Icon.
To record a macro do step 1 and then start recording the macro give it a name and do the other steps and then stop recording.
By choosing the range before you start recording the macro, you make it work on whatever selection you have chosen when you run the macro again.
The code you end up with is.
Sub Print_Selection() Selection.PrintOut Copies:=1, Collate:=True End Sub |
Those four clicks are converted to single line of code.
Print on One Page
Let’s say you want to make sure the print of your selection prints on one page. You can tweak the code to force it to do that.
The extra code was recorded, but I extracted the useful parts and added it to the code above.
This code works the same – you select the range first and then run the macro.
This code that makes sure the selection prints on one page is.
Sub Print_Selection_OnePage() Dim z z = ActiveSheet.PageSetup.Zoom With ActiveSheet.PageSetup .FitToPagesWide = 1 .FitToPagesTall = 1 .Zoom = False End With Selection.PrintOut Copies:=1, Collate:=True ActiveSheet.PageSetup.Zoom = z End Sub |
This code captures the existing zoom percentage in the z variable.
It then changes the print settings to 1 page.
Then it prints.
Then it returns the sheet to its original zoom percentage.
Please note: I reserve the right to delete comments that are offensive or off-topic.