Vba printing a worksheet in vba in Excel

Printing a document is a routine task performed by almost everyone, who works with MS Excel.

The printing of documents has the same set of procedures irrespective of the type of application used, either Word, PowerPoint or Excel.

However, since Excel documents are tabular, there rises a need to accommodate the whole data(columns) in a single page within the print range.

This is where the Excel document printing differs from the rest.

With large number of rows, it can be printed in multiple pages, whereas, the columns cannot be split in most cases.

So, This chapter aims to provide some details on various printing methods followed in Excel.

The VBA has various methods and properties associated with printing worksheets.

These are as explained below.

PrintOut method

This method is used to instruct the application to print the worksheet.

The various settings set through the code are taken into account.

Syntax

PrintOut(�From�,�To�,�Copies�,�Preview�,�ActivePrinter�,�PrintToFile�,�Collate�,�PrToFileName�,�IgnorePrintAreas�)

From

The starting page number to begin the printing. If this argument is omitted, printing starts at the beginning.

A number that indicates the last page to be print. If not specified, the printing ends with the last page.

Copies

An Integer that represents the number of copies to be printed.

Default value is 1.

Preview

It takes binary values TRUE or FALSE.

A TRUE invokes the print preview dialog box before printing.

A FALSE simply prints without showing the preview dialog.

ActivePrinter

This parameter specifies or sets the name of the active printer to print with.

PrintToFile

This parameter takes a binary value TRUE or FALSE.

A TRUE is given to enable Print to File.

PrToFileName

When PrintToFile is set to TRUE, this argument specifies the name of the file to print to.

Collate

This Property takes a binary value.

A True to collate multiple copies and False for otherwise.

IgnorePrintAreas

A TRUE will ignore the print area selected and prints the entire object.

The following example demonstrates the print feature.

  1. Sub printsheet()
  2. Dim prtsht As Worksheet
  3. Set prtsht = Sheets("Q97")
  4. prtsht.PrintOut From:=97, To:=98, Copies:=1, Preview:=True
  5. End Sub

The above code is used to print this sheet.

The screenshot of the editor is as shown below

excel vba printing a worksheet in vba

 

You can find similar Excel Questions and Answer hereunder

1) How to concatenate strings in vba in Excel

2) Tables in Excel VBA. How you can create table in VBA and work with them with macros

3) Excel 2010 vba replacement of application filesearch in Excel

4) How to do webscrapping in VBA. Here some basics

5) How can I set non-contiguous print area using VBA?

6) How can I hide a specific Shape in my WorkSheet?

7) How can worksheet functions be accessed in VBA?

8) How can I get users to select a folder to save the output of my macro?

9) How can I filter and copy only filtered data using VBA?

10) How to create Pivot table in excel VBA

 

Here the previous and next chapter