In Microsoft Excel, you will have to print sheets for various purposes. It is one of the familiar things while working with Excel. Now, we macro to print specific sheets in Excel using our built-in print button or the print option of Excel to print sheets. However, you can also use VBA macro to print sheets in Excel. In this tutorial, you will learn to print specific sheets using VBA macro in Excel.
This tutorial will be on point with other resources and proper illustrations. So, read the whole article.
How to Print Specific Sheets Using VBA Macro: 4 Ways
In the following sections, we are going to provide you with two simple but effective VBA macros that you can imply in your worksheets. We recommend you learn and apply all these methods to your workbook. It will surely develop your Excel knowledge.
1. Print Specific Sheets by Sheet Number Using VBA Macro
Now, you can print multiple sheets using the sheet number. But this method will print specific sheets that are contiguous. We will use the basic PrintOut syntax.
First, open your Visual Basic Editor. After that, insert a module. Then type the following code:
Sub print_sheets_by_number()
For i = 3 To 6
Worksheets(i).PrintOut
Next i
End SubWe are using a For-Next loop here. This code will print the sheets from 3 to 6. But, remember, it will print them one by one. After running the macro, it will ask you to save your file.
Now, after running the code, Excel will save these specific sheets as pdf. And, it will ask you to save the print output file:
Also, it will show the following dialog box:
After completing all of these, you will get your print preview.
2. Macro to Print Specific Sheets by Sheet Name
Another way to print specific sheets using a macro is to print them by the sheet name. This method is pretty obvious but effective. If you have limited sheets in your Excel workbook, you can use the following code:
Sub print_sheets_by_name()
Worksheets("Dataset").PrintOut
Worksheets("AutoSum").PrintOut
Worksheets("MIN").PrintOut
Worksheets("SMALL").PrintOut
End SubAs you can see, we have used the sheet name to print those multiple and specific sheets from the Excel workbook. But, this process is pretty hectic. You have to remember the sheet names.
3. Print Specific Sheets Using Array
Now, you can use an array to print specific sheets. You have to enter the sheet names in the array like the following:
Sub print_multiple_sheets()
Worksheets(Array("Dataset", "AutoSum", "MIN", "SMALL")).PrintOut
End Sub4. Print Specific Sheets by a Button in Excel
Now, another way to print specific sheets is to create a print button in Excel. First, we will create a button. After that, we will insert a code for that button. If you click that button, it will print specific sheets. Why is this helpful? We will use this because it is hazard-free. Just one click will do all of these.
📌 Steps
- First, go to the Developer If you don’t have it, enable the Developer tab in the ribbon.
- Now, from the Controls group, click on Insert.
- After that, from the ActiveX Controls, click on the Button
- Now, place the button on your worksheet.
- To change the name of the button, right-click on the button. After that, click on Properties.
- From the Properties dialog box, change the caption of the button. It will change the name of the button.
- As you can see, our print button is ready.
- Now, double click on the button while Design Mode is on. You will see the following VBA code.
- Now, type the following code:
Private Sub CommandButton1_Click()
ActiveSheet.PrintOut
End SubThis code will print the active sheet that you are working on. Now, from here, you can follow either of the two ways:
- Copy the same button and paste it into all the worksheets. In this way, you will be able to print any sheet you want.
- Or you can use the macro of the previous two methods and use them in this button. In this way, you will print specific sheets with one button.
Read More: VBA Code for Print Button in Excel
Other Useful Macros to Print Sheets in Excel
In the following sections, we are going to provide you with some very essential VBA macros that you can use in a lot of scenarios. You can use all of these codes in your workbook. It will work nicely. Let’s get into it.
1. Print Specific Sheets into Single Page
The following code will print the sheet “Dataset” exactly one page wide and tall.
Sub print_single_page()
With Worksheets("Dataset").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
End Sub2. Print Sheets with Comments
The following code will print sheets with comments:
Sub print_sheets_with_comments()
Application.DisplayCommentIndicator = xlCommentAndIndicator
With ActiveSheet
.PageSetup.PrintComments = xlPrintInPlace
.PrintOut
End With
End Sub3. Macro to Print Hidden Sheets in Excel
If you have some specific hidden sheets in your Excel workbook, use the following code to print them:
Sub print_hidden_sheet()
Dim current_visible As Long
Dim working_sheet As Worksheet
For Each working_sheet In ActiveWorkbook.Worksheets
With working_sheet
current_visible = .Visible
If current_visible >= 0 Then
.Visible = xlSheetVisible
.PrintOut
.Visible = current_visible
End If
End With
Next working_sheet
End Sub4. Macro to Print both Hidden and Visible Sheets
By the following code, you can print all the hidden and visible sheets:
Sub print_hidden_visible_sheet()
Dim current_visible As Long
Dim working_sheet As Worksheet
For Each working_sheet In ActiveWorkbook.Worksheets
With working_sheet
current_visible = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = current_visible
End With
Next working_sheet
End Sub5. Print Multiple Excel Worksheets with Macro
We have already shown the code above. Use the sheet names in the array to print multiple sheets in Excel:
Sub print_multiple_sheets()
Worksheets(Array("Dataset", "AutoSum", "MIN", "SMALL")).PrintOut
End Sub6. Print All Worksheets
To print all the worksheets of your Excel workbook, use the following code:
Sub print_all_sheets()
Worksheets.PrintOut
End Sub7. Print Entire Workbook
To print the whole workbook, follow any the of the following VBA codes:
Using ActiveWorkbook:
Sub print_active_workbook()
ActiveWorkbook.PrintOut
End SubUsing ThisWorkbook:
Sub print_this_workbook()
ThisWorkbook.PrintOut
End Sub8. VBA Code to Print a Specific Sheet
Mention the sheet name to print a specific sheet from your workbook.
Sub print_specific_sheet()
Sheets("Dataset").PrintOut
End Sub9. Print Active Sheet in Excel
Print the active sheet using the following code:
Sub print_active_sheet()
ActiveSheet.PrintOut
End Sub10. Print Selected Sheets
By the following code, you can print the specifically selected worksheets:
Sub print_selected_sheet()
ActiveWindow.SelectedSheets.PrintOut
End Sub11. Print a Selection from a Sheet
If you want to print a specific selection, use the following code:
Sub print_selection()
Selection.PrintOut
End SubRead More: How to Set Print Area to Selection Using VBA in Excel
12. Excel VBA to Print a Range
The following code will help you to print a specific range from a worksheet.
Sub print_range()
Range("B4:C11").PrintOut
End SubRead More: Excel VBA: Print Range of Cells
13. Print Preview
To print a preview, use the following code:
Sub print_preview()
ActiveSheet.PrintOut preview:=True
End SubRead More: How to Display Print Preview with Excel VBA
14. Print Specific Sheets by Taking User Input
Now, you can take the sheet number or sheet name as input from the user. The following code will do that with ease.
14.1 Take Sheet Number as User Input
The following code will take the sheet number as input from the user:
Sub print_user_input_number()
Dim sheet_number As Integer
sheet_number = Application.InputBox("Enter Sheet Number to Print:")
Worksheets(sheet_number).PrintOut
End Sub14.2 Take Sheet Name as User Input
You can print a specific sheet by taking the sheet name as user input:
Sub print_user_input_name()
Dim sheet_name As String
sheet_name = Application.InputBox("Enter Sheet Name to Print:")
Worksheets(sheet_name).PrintOut
End Sub15. Excel VBA to Print Preview a Selected Range in Excel
The following code will give you the preview, along with the print option:
Sub print_preview_selected_range()
Sheets("Dataset").Range("B4:C11").PrintPreview
End SubRead More: Excel VBA: Print Preview for Selected Range
💬 Things to Remember
✎ Here, the macros are based on our practice workbook. If you are using a different workbook, change the sheet name, number, and range.
Download Practice Workbook
Download this practice workbook.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the Date in VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
Related Articles
- Excel VBA Debug Print: How to Do It?
- Excel VBA: How to Set Print Area Dynamically
- Excel VBA: Set Print Area for Multiple Ranges
- Excel VBA to Display Print Preview for Multiple Sheets
