Another Useful Print Macro

And it is recorded

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.

  1. Select the range.
  2. Click the File ribbon tab.
  3. Click the Print option on the right.
  4. Click the first option drop down under Settings and choose Print Selection.
  5. 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.

Related Posts

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.