How to Print Data with Excel VBA (A Detailed Guideline)

One of the most important and widely used tasks that we come across while working with VBA in Excel is to print the necessary data. Today in this article, I’ll show you how you can print data in Excel VBA with proper examples and illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Steps to Print Data with Excel VBA

Here I’ve got a data set with the Names, Types, and Prices of some books of a bookshop called Martin Bookstore.

Data Set to Print Data in Excel VBA

Today we’ll learn how we can print this data set with VBA.


Step 1: Opening the VBA Editor to Print in Excel

Press ALT+F11 on your keyboard. It’ll open the Visual Basic Editor.

Opening VBA Editor to Print Data with VBA in Excel

Read More: How to Set Print Area in Excel (5 Methods)


Step 2: Inserting a New Module to Print in Excel

Go to the Insert option in the VBA toolbar. Click Insert > Module to open a new module.

Related Content: How to Print Gridlines in Excel (2 Ways)


Step 3: Entering the VBA Code to Print in Excel

A new module named Module1 will open. Enter the following VBA code there.

⧭ VBA Code:

Sub VBA_Print()

ActiveWorkbook.ActiveSheet.PrintOut copies:=1

End Sub

VBA Code to Print Data with VBA in Excel

⧭ Notes:

  • Here, I want to print the active worksheet of my workbook. To print any other worksheet, write the name of the worksheet directly in the code.

For example, to print out the worksheet called Sheet1, use:

ActiveWorkbook.Worksheets(“Sheet1”).PrintOut copies:=1

  • You can also print from a workbook that’s not active. For example, to print out Sheet1 from a workbook called Workbook1, use:

Workbook(“Workbook1”).Worksheets(“Sheet1”).PrintOut copies:=1

  • Here we’re printing only one copy of the worksheet. If you want to print more than one copy, change the copies property accordingly.
  • If you want to print multiple worksheets and collate them while printing, there is also an option for you. The PrintOut function of VBA has a property called Collate. Set it to be True.

ActiveWorkbook.ActiveSheet.PrintOut copies:=10, Collate:=True

Related Content: Excel VBA: How to Set Print Area Dynamically (7 Ways)


Similar Readings:


Step 4: Running the VBA Code to Print in Excel

After entering the VBA code properly, run the Macro by clicking the Run option in the VBA toolbar.

Running Code to Print Data with VBA in Excel

Related Content: Excel VBA: Set Print Area for Multiple Ranges (5 Examples)


Step 5: The Final Output: Print with VBA

If you can successfully write down the code and run it, you’ll find the worksheet printed on your printer, and a small window appeared like this.

Related Content: Excel VBA: Print Range of Cells (5 Easy Methods)


Things to Remember

Here we’ve used the PrintOut function of VBA. There’s another function in VBA called PrintPreview, that shows the preview of the data before printing.

The syntax of the PrintPreview function is the same as the PrintOut function, just use PrintPrview in place of PrintOut.

ActiveWorkbook.ActiveSheet.PrintPreview

It’ll show the preview of your worksheet before printing.

Preview of Print through Excel VBA


Conclusion

So, this is the method with which you can print any data from an Excel worksheet with VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo