Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing hidden worksheets?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing hidden worksheets?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "MichelleDuquette" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing hidden worksheets?
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. -- Jim at Eagle "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "MichelleDuquette" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing hidden worksheets?
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "MichelleDuquette" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing hidden worksheets?
If you want to print all selected worksheets then use this
(you can't select hidden sheets) ActiveWindow.SelectedSheets.PrintOut -- Regards Ron de Bruin http://www.rondebruin.nl "MIchelleDuquette" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "MichelleDuquette" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing a hidden image | Excel Discussion (Misc queries) | |||
Printing hidden rows | Excel Discussion (Misc queries) | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
Printing Hidden Rows | Excel Discussion (Misc queries) | |||
Printing hidden worksheets | Excel Discussion (Misc queries) |