Print macro

This article describes different print methods in order to print pages via a button or by simply using the macro menu in Excel. Assigning VBA codes/ macros to a button is described in this article.

Print active page  

This macro prints the sheet that is currently active, i.e. you are seeing/ working on. This code will not ignore print areas.

Sub Macro_print()

ActiveSheet.PrintOut

End Sub

If your current active sheet consists out of more than 1 page and you want to print out specified pages you could use the following code. Excel will print pages 4 to 6.

Sub Macro_printSpecifiedPages()

ActiveSheet.Printout From:=4, To:=6

End Sub

To print multiple copies:

Sub Macro_printCopies()

ActiveSheet.Printout From:=4, To:=6, Copies:=2

End Sub

Print defined page(s)  

Perhaps, if you have a dashboard with multiple buttons, you wish to print a specified worksheet. Therefore, ‘ActiveSheet’ has to be changed into the following:

Sub Macro_printSpecifiedSheet()

Sheets(“Name of the sheet to print”).PrintOut

End Sub

Or in case of multiple worksheets:

Sub Macro_printMultipleSheets()

Worksheets(Array(“AAA”, “BBB”, “DDD”)).PrintOut

End Sub

Or in case of the whole workbook:

Sub Macro_printWorkbook()

ActiveWorkbook.PrintOut

End Sub

Print a page, fit on one page. 

This code scales the page to fit on one page and prints.

Sub Macro_PrintOnOnePage()
On Error Resume Next

With ActiveSheet.PrintOut
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1

End With
End Sub

‘Change .FitToPagesTall = 1 to = False if you want to change the fit to width only.

‘Change .FitToPagesTall = 1 to = False if you want to change the fit to Wide only.

Print Preview 

If you want to edit the print preview menu (select printers and other options) you have to change the ‘PrintOut’ command:

Sub Macro_print()

ActiveSheet.PrintPreview

End Sub

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s