Hide / Unhide Workshets on Print and Open

[Guest]

I have two worksheets in a file that I'd like to unhide when the file is
opened and I'd like to hide them prior to printing since they should not be
printed when "print entire workbook" is chosen. They are named VAV Data and
FPB Data. They may or may not be hidden when the file is closed.

Thanks

 

[Guest]

Try this on the Thisworkbook code module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True

'hide the sheets
Sheets("VAV Data").Visible = xlSheetVisible
Sheets("FPB Data").Visible = xlSheetVisible

With Application
.EnableEvents = False
.Dialogs(xlDialogPrint).Show
.EnableEvents = True
End With

'show the sheets
Sheets("VAV Data").Visible = xlSheetVisible
Sheets("FPB Data").Visible = xlSheetVisible
End Sub

Private Sub Workbook_Open()
'show the sheets
Sheets("VAV Data").Visible = xlSheetVisible
Sheets("FPB Data").Visible = xlSheetVisible
End Sub

 

[Guest]

Correction.

This part of the code:

'hide the sheets
Sheets("VAV Data").Visible = xlSheetVisible
Sheets("FPB Data").Visible = xlSheetVisible

should be like this:

'hide the sheets
Sheets("VAV Data").Visible = xlSheetHidden
Sheets("FPB Data").Visible = xlSheetHidden

 

[Guest]

Excellent. Thanks a lot.

Is there any way to print those sheets that I hide if I want to review the
data on them? I can disallow the macro when I open the file, but is there a
way to do it from within Excel?

Thanks

 

[Guest]

You can use a macro like the one below. It turns off events then shows the
print dialog without hiding any of your data sheets..

Sub SpecialPrint()
With Application
.EnableEvents = False
.Dialogs(xlDialogPrint).Show
.EnableEvents = True
End With
End Sub