Printing hidden worksheets?

[Guest]

Background: I have a workbook that has about 100 hidden sheets at any one
time, two of which are data sheets named Data1 and Data2 that all other
sheets pull their data from. Basically selection options on the default
visible page make specific sheets visible. Each time a sheet is made visible,
the previous sheet is made invisible.

I want to add a sub routine that will print all the sheets in landscape
format, both visible AND invisible, excluding the two data sheets. I don't
want them to print at all.
I also would prefer the invisible sheets do not become visible again for the
printjob.

Thanks folks - Michelle

 

[Ron de Bruin]

You must unhide them to print
Try this example (untested)

Sub test()
Dim curVis As Long
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Data1" And sh.Name <> "Data2" Then
With sh
curVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = curVis
End With
End If
Next sh
End Sub

 

[Guest]

You could hide all tabs
ActiveWindow.DisplayWorkbookTabs = False
Then by controlbutton control specific sheets for that user thereby all
sheets are unavailable for selection and therefore viewing.

 

[Guest]

Thanks Ron! Your code worked just fine..
Here's what I'm using. Can this be modified to print all selected worksheets
as one job? (I've seen the "copy" method, but it's a little too late for that
with so many sheets..) Thanks again! -Michelle
Private Sub PrintWBCommandButton_Click()
Dim curVis As Long
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Data1" And sh.Name <> "Data2" Then
With sh
.Application.ScreenUpdating = False
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
curVis = .Visible
.Visible = xlSheetVisible
.PrintPreview
.Visible = curVis
.Application.ScreenUpdating = True
End With
End If
Next sh
End Sub

 

[Ron de Bruin]

If you want to print all selected worksheets then use this
(you can't select hidden sheets)

ActiveWindow.SelectedSheets.PrintOut