Rifat Hassan
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.
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.
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
⧭ 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:
- Excel Button to Print Specific Sheets (With Easy Steps)
- How to Print Horizontally in Excel (4 Methods)
- Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)
- Print Titles in Excel Is Disabled, How to Enable It?
- How to Print Excel Sheet in A4 Size (4 Ways)
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.
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.
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.
