Excel VBA to Display Print Preview for Multiple Sheets

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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 of displaying 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 to the Print Preview window.
  • You’ll see 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 the desired sheets for which you want to see the print preview.

See Print Preview of Selected Worksheets with Excel VBA

Read More: Excel VBA: Print Preview for Selected Range


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 Sheet5.
  • This method reduces the workload of selecting or mentioning the sheet names.

Use Excel VBA to Get Print Preview of All Sheets

Read More: How to Print Specific Sheets Using VBA Macro in Excel


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


Download Practice Workbook

Download the following workbook to practice by yourself.


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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo