Excel VBA to Display Print Preview for Multiple Sheets

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. Sometimes, we need to print the worksheets. Here, we can choose all the sheets or just the desired ones for printing. However, Print Preview plays a significant role in rechecking the sheets before we print them. In that case, we can see if everything is in place or not. In this article, we’ll show you the 5 practical examples of Excel VBA to Display Print Preview for Multiple Sheets.


Download Practice Workbook

Download the following workbook to practice by yourself.


5 Practical Examples of Excel VBA to Display Print Preview for Multiple Sheets

We can print and display the print preview through some methods. But, applying the VBA code to perform this operation is the easiest one. And it’s without any hassle too. To illustrate, we’ll use a sample workbook as an example. For instance, the following workbook contains 5 worksheets. In Sheet1 we have Dataset 1, in Sheet2 we have Dataset 2. And like this, we have Sheet3, Sheet4, and Sheet5. This article will show 5 practical examples to display the print preview for multiple sheets using excel VBA.


1. See Print Preview of Selected Worksheets with Excel VBA

In our first example, we’ll apply the VBA code to display the print preview of our select worksheets. Therefore, follow the steps below to carry out the operation.

STEPS:

  • First, go to the Developer tab.
  • Then, select Visual Basic.

See Print Preview of Selected Worksheets with Excel VBA

  • As a result, the VBA window will pop out.
  • Now, click Module from the Insert drop-down.

See Print Preview of Selected Worksheets with Excel VBA

  • Next, the Module window will emerge.
  • Afterward, copy the following code and paste it into the box.
Sub SelectedSheets_PrintPreview()
ActiveWindow.SelectedSheets.PrintPreview
End Sub

See Print Preview of Selected Worksheets with Excel VBA

  • Subsequently, save the code and minimize the VBA window.
  • Again, go to the Excel workbook window.
  • Select the desired sheets by clicking them while pressing the Ctrl key.
  • See the below figure where we select Sheet1, Sheet2, and Sheet5.

See Print Preview of Selected Worksheets with Excel VBA

  • Consequently, go to the VBA tab.
  • After that, press the F5 key to run the code.
  • Thus, it’ll return the Print Preview window.
  • You’ll see the Sheet1 at first.

See Print Preview of Selected Worksheets with Excel VBA

  • Click Next Page.
  • You’ll get to see Sheet2.

See Print Preview of Selected Worksheets with Excel VBA

  • Similarly, press Next Page to see Sheet5.
  • In this way, you can select your desired sheets for which you want to see the print preview.

See Print Preview of Selected Worksheets with Excel VBA


2. Specify Desired Multiple Sheets in VBA Code for Displaying Print Preview

We can also specify the sheet names in our VBA code. And the code will return the print preview for those specified sheets. In this example, we’ll mention the sheets Sheet2 and Sheet3. So, learn the following steps for displaying Print Previews of multiple sheets.

STEPS:

  • Firstly, go to Developer Visual Basic.
  • Then insert a new Module.
  • Copy the below code and paste it there.
Sub PrintPreview_SpecifiedSheets()
Worksheets(Array("Sheet2", "Sheet3")).PrintPreview
End Sub

  • Save the file.
  • After that, run the code by pressing the F5 key.
  • Accordingly, you’ll get the print preview.

  • To see Sheet3, click Next Page.
  • Lastly, it’ll return the Sheet3.


3. Use Excel VBA to Get Print Preview of All Sheets

Moreover, you can get the print preview for all the sheets in a workbook at once. You don’t have to select or mention the sheet names as we did in our above examples. Hence, follow the process to carry out the operation.

STEPS:

  • Get a new Module window by repeating the steps in example 1.
  • Copy the code and paste it into the window.
Sub AllSheets_PrintPreview()
Worksheets.PrintPreview
End Sub

Use Excel VBA to Get Print Preview of All Sheets

  • Now, press the F5 key to run the code.
  • Thus, it’ll return the Print Preview for all the sheets.

Use Excel VBA to Get Print Preview of All Sheets

  • Accordingly, click Next Page to see the other sheets.
  • At last, you’ll see the Sheet5.
  • This method reduces the workload of selecting or mentioning the sheet names.

Use Excel VBA to Get Print Preview of All Sheets


4. Display Print Preview of Multiple Hidden Sheets Using Excel VBA

However, the VBA code can get tricky if we want to display the print preview for the hidden sheets. Therefore, learn the following process to perform the task.

STEPS:

  • First of all, we’ll show you how to hide the sheets.
  • Select the desired sheets.
  • Then, right-click the mouse.
  • The Context Menu will pop out.
  • Choose Hide.

Display Print Preview of Multiple Hidden Sheets Using Excel VBA

  • Thus, the sheets will get hidden.
  • You won’t see the sheets in the sheet tab.
  • Look at the below picture.

Display Print Preview of Multiple Hidden Sheets Using Excel VBA

  • Now, go to the VBA window following the example 1 steps.
  • Next, insert a Module window.
  • Afterward, paste the code into the box.
Sub PrintPreviewHiddenSheets()
    Dim Vis As Long
    Dim st As Worksheet
    For Each st In ActiveWorkbook.Worksheets
        With st
            Vis = .Visible
            If Vis >= 0 Then
                .Visible = xlSheetVisible
                .PrintPreview
                .Visible = Vis
            End If
        End With
    Next st
End Sub

Display Print Preview of Multiple Hidden Sheets Using Excel VBA

  • Save the file.
  • To run the code, press the F5 key.
  • Hence, you’ll see the print preview for Sheet3.

Display Print Preview of Multiple Hidden Sheets Using Excel VBA

  • Subsequently, press Close Print Preview.
  • Immediately, you’ll see the Sheet4 which was hidden too.
  • Following this process, you can display the hidden sheets.

Display Print Preview of Multiple Hidden Sheets Using Excel VBA


5. Apply VBA to Display Print Preview for Visible and Hidden Worksheets

Again, we can get the print preview window for both the hidden and the visible sheets with Excel VBA. In this example, Sheet3 and Sheet4 are hidden. So, go through the steps to make some changes in the code and get the required output.

STEPS:

  • Firstly, go to Developer Visual Basic.
  • Then, click Insert Module.
  • In the Module box, paste the following code.
Sub PrintPreviewHiddenAndVisibleSheets()
    Dim Vis As Long
    Dim st As Worksheet
    For Each st In ActiveWorkbook.Worksheets
        With st
            Vis = .Visible
            .Visible = xlSheetVisible
            .PrintPreview
            .Visible = Vis
        End With
    Next st
End Sub

  • After that, press the F5 key to run the code.
  • As a result, it’ll return the print preview for Sheet1.

  • Press Close Print Preview.
  • Similarly, you’ll get the other sheets.
  • The below figure shows the hidden sheet.
  • So, this code helps us to display both the hidden and the visible sheets.


Conclusion

Henceforth, you will be able to use Excel VBA to Display Print Preview for Multiple Sheets following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo