Print All, Except

[mharper90]

I want set a macro to print all tabs of a workbook, except for the first 3. The first 3 never change, so I know their names, but they're length does, so it's difficult to use code that starts printing on a particular page, in case one of the first 3 takes up a different number of pages than it does right now.

Also I want to show the print preview screen, instead of sending them straight to the printer. This will allow the user to adjust the printer duplex and binding options prior to printing.

Currently I've tried the code below, but I don't like that it sends each tab as it's own print job/opens a print window for each tab. I have over 60 tabs to print, so I want to avoid clicking print 60 times. Thanks!

For Each ws In ThisWorkbook.Worksheets[INDENT]If ws.Name <> "Main Data" And ws.Name <> "Figure 2-2" And ws.Name <> "Master Blank for ERC" Then[/INDENT]
[INDENT=2]If ws.Visible = xlSheetVisible Then

Application.Dialogs(xlDialogPrint).Show

End If[/INDENT]
[INDENT]End If[/INDENT]
Next ws

 

[Fluff]

How about

Sheets(4).Select
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Main Data" And ws.Name <> "Figure 2-2" And ws.Name <> "Master Blank for ERC" Then
      If ws.Visible = xlSheetVisible Then
         ws.Select False
      End If
   End If
Next ws
Application.Dialogs(xlDialogPrint).Show

 

[mharper90]

I tried this today at work and I think it's exactly what I need, except that for some reason "Master Blank for ERC" was the ONLY sheet that didn't show up in the print preview. The other two ("Main Data" and "Figure 2-2") still tried to print. Any idea why?

Also, I just double checked your code with mine, and I somehow forgot to insert "Sheets(4).Select". I'm home now and can't try it again until work tomorrow, but curious if you could explain what this line is doing? Is that the key to why only 1 of the <> sheets held back from printing? Thanks!

 

[Fluff]

If it's always the first 3 sheets that you want to ignore, try

Dim i As Long
Sheets(4).Select
For i = 5 To Worksheets.Count
   If Sheets(i).Visible = xlSheetVisible Then
      Sheets(i).Select False
   End If
Next i
Application.Dialogs(xlDialogPrint).Show