How to Print Specific Sheets Using VBA Macro in Excel (4 Ways)

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 use normally 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.

Download Practice Workbook

Download this practice workbook.

4 Ways to Print Specific Sheets Using VBA Macro

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 Sub

We 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.

Read More: How to Print Page Number in Excel (5 Easy Ways)

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 Sub

As 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.

Read More: How to Print Excel Sheet in Full Page (7 Ways)

Similar Readings:

• How to Not Print Empty Cells in Excel (3 Simple Methods)
• Excel VBA: How to Set Print Area Dynamically (7 Ways)
• How to Keep Header in Excel When Printing (3 Ways)
• Print Graph in Excel (5 Ways)
• How to Print Titles in Excel (5 Easy Ways)

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 Sub

4. 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 Sub

This code will print the active sheet that you are working on. Now, from here, you can follow either of the two ways:

Read More: VBA Code for Print Button in Excel (5 Examples)

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 Sub

2. 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 Sub

3. 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 Sub

4. 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 Sub

5. 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 Sub

6. Print All Worksheets

To print all the worksheets of your Excel workbook, use the following code:

Sub print_all_sheets()

Worksheets.PrintOut

End Sub

7. 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 Sub

Using ThisWorkbook:

Sub print_this_workbook()

ThisWorkbook.PrintOut

End Sub

8. 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 Sub

9. Print Active Sheet in Excel

Print the active sheet using the following code:

Sub print_active_sheet()

ActiveSheet.PrintOut

End Sub

10.  Print Selected Sheets

By the following code, you can print the specifically selected worksheets:

Sub print_selected_sheet()

ActiveWindow.SelectedSheets.PrintOut

End Sub

11. Print a Selection from a Sheet

If you want to print a specific selection, use the following code:

Sub print_selection()

Selection.PrintOut

End Sub

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 Sub

13. Print Preview

To print a preview, use the following code:

Sub print_preview()

ActiveSheet.PrintOut preview:=True

End Sub

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 Sub

14.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 Sub

15. 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 Sub

💬 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.

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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• How to Print Selected Area in Excel on One Page (3 Methods)
• Excel VBA: Set Print Area for Multiple Ranges (5 Examples)
• Print Titles in Excel Is Disabled, How to Enable It?
• How to Print Horizontally in Excel (4 Methods)
• How to Print Excel Sheet with Header on Every Page in Excel (3 Methods)