How to Print Specific Sheets Using VBA Macro in Excel: 4 Methods

Method 1 – Print Specific Sheets by Sheet Number Using VBA Macro

Print multiple sheets using the sheet number, but this method will print specific sheets that are contiguous. We will use the basic PrintOut syntax.

Open your Visual Basic Editor. After that, insert a module, and 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 individually. 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. It will ask you to save the print output file:

It will show the following dialog box:

After completing all of these, you will get your print preview.

Method 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. This process is pretty hectic. You have to remember the sheet names.

Method 3 – Print Specific Sheets Using Array

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

Method 4 – Print Specific Sheets by a Button in Excel

Steps

• Go to the Developer If you don’t have it, enable the Developer tab in the ribbon.
• From the Controls group, click on Insert.

• From the ActiveX Controls, click Button

• Place the button on your worksheet.

• To change the name of the button, right-click on the button. Click Properties.

• From the Properties dialog box, change the caption of the button. It will change the name of the button.

• The print button is ready.

• Double click on the button while Design Mode is on. You will see the following VBA code.

• 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:

Other Useful Macros to Print Sheets in Excel

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.

Method 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

Method 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

Method 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

Method 4 – Macro to Print both Hidden and Visible Sheets

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

Method 5 – Print Multiple Excel Worksheets with Macro

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

Method 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

Method 7 – Print Entire Workbook

To print the whole workbook, follow any 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

Method 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

Method 9 – Print Active Sheet in Excel

Print the active sheet using the following code:

Sub print_active_sheet()

ActiveSheet.PrintOut

End Sub

Method 10 – Print Selected Sheets

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

Sub print_selected_sheet()

ActiveWindow.SelectedSheets.PrintOut

End Sub

Method 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

Method 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

Method 13 – Print Preview

To print a preview, use the following code:

Sub print_preview()

ActiveSheet.PrintOut preview:=True

End Sub

Method 14 – Print Specific Sheets by Taking User Input

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

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

Download Practice Workbook

Download this practice workbook.

Related Articles

• Excel VBA: How to Set Print Area Dynamically
• Excel VBA: Set Print Area for Multiple Ranges