VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet

This posts explains how to print an Excel page using VBA and PrintOut. Normally, you will want to define the PageSetup first, in order to make sure that the right printer, the right paper size (fx A4), orientation (landscape or portrait), etc. is selected.

Step 1: Define PageSetup.

With Worksheets("MyPage").PageSetup
     .Orientation = xlPortrait
     .Zoom = False
     .FitToPagesWide = 1
     .FitToPagesTall = 1
     .PaperSize = xlPaperA4
End With

It’s important to add the Zoom property in adition to the FitTo... properties, as you can otherwise not be sure that the printing in fact is restricted to 1×1 page.

Step 2: Print the page.

Sheets("MyPage").PrintOut Copies:=1, Collate:=True

And that’s it!

Friday, November 26th, 2010 UserForm