Set Print Area & Print Macro Command
This article provides code for vba print macro Excel 2010 or versions less than that and also manual method.
Just like printing a PDF, Word document the Excel reports, dashboards; charts are also sent to printer to have a hard copy. People generate dashboards, pivot tables/charts, repots in Excel sheets for their business and personal uses and few times print them too. So, let’s learn what the available methods to print an Excel are and also how to automate a print process.
In many shops, the invoices, purchase bill, inventory details, balance sheet are all maintained in Excel. So, it becomes important for them to print the final version for future hard copy records.
Manual: Excel Set Print Area & Page Layout
In Excel before printing a worksheet, the following basic steps have to be taken care.
- Set Print area – Choose which cells have to appear in the paper after a print. This option will be available in Menu ->Page Layout -> Print Area. Select the cells that you want to print and then choose this option.
- Portrait or Landscape – This is also present in Menu ->Page Layout -> Orientation.
- Misc Options: There are also options available to insert a header/footer, background image, Margin, Page or Line break etc.
- In case if there is a need for more controls, click on Print Titles button available in Page Layout Menu.
Once these settings are completed, press Ctrl + F2 to view Print Preview. If the preview looks good, press Ctrl + P to send the printer to default Printer.
Macro: Excel VBA Print Command
In Excel macro we have options to print the content through Workbook, Window, Sheet, Range, Chart classes using the below function.
Function PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName])
But, before issuing this vba print command, the basic steps – set print area, page orientation etc has to be taken care as well. Otherwise we might end up in printing unwanted data, wasting paper.
Assume the records in sheet1 in range A1:K20 has to be printed in Landscape format along with a background image.
Visual Basic12345678910111213141516171819202122Option Explicit'Visit http://officetricks.com for more Excel Tips & TricksSub Excel_VBA_Print()'Set Print AreaThisWorkbook.Sheets("Sheet1").PageSetup.PrintArea = "A1:K20"'Page Layout'ThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlPortraitThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlLandscape'Set Background Image or Watermark ImageThisWorkbook.Sheets("Sheet1").SetBackgroundPicture Filename:="D:\PrintBackgroundImage.JPG"'Enable a Label object to appear in print - Printing Label in ExcelLabel1.SelectWith Selection.PrintObject = FalseEnd With'Print 2 Copies of the pageThisWorkbook.Sheets("Sheet1").PrintOut Copies:=2, From:=1, To:=1End Sub
Ensure there is some data in excel before executing the code(F5 – to run the macro). Once it is done, Excel will send the selected Print area to the default printer.
Excel VBA Print Labels
This above code also explains how to print labels from excel. First you have to make the vba code to select the label object and modify its “Printobject” property. By using the above code, printing a excel sheet can be assigned to a command button or any scheduled printing can also be done.
Also Read: Print Yearly Calendar for Quick Reference
But, it is advised that you verify code logic twice before placing this print login inside a loop. If the loop goes endless, then there are chances that your printer will end up in too many pages, wasting lot of paper.
Set Excel VBA Print Area – Give Printout from Macro