ajrob
09-06-2006, 09:08 PM
Here's a peculiar one ...
I've got an application in which I'd like to create a "Print" button that prints specific worksheets, but there are some complications...
The first is that I have for tabs which initiate in the hidden state. User input dictates which of the four should become visible. Let's call these Site PFD 1, Site PFD 2, Site PFD 3, and Site PFD 4.
Second, there will be multiple tabs visible after the appropriate Site PFD tab comes into view - but I only want to print a subset of those visible.
I've got some code here that does work, but ideally - I'd like to send one print command. This code goes out and finds the visible Site PFD tab, then separate prints the other subset. Can you help me consolidate to one print command?
Thanks
Sub prt_Form()
' xlSheetVisible = -1
' Visible PFD
Dim sh As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Sheets(Array("Site PFD - High", "Site PFD - Med LD", "Site PFD - Med", "Site PFD - Low"))
If sh.Visible = -1 Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = sh.Name
End If
Next
With ThisWorkbook
.Worksheets(arr).PrintOut , , 1, True, , True
' Subset of Standard Sheets
.Sheets(Array("Notes", "Main Form")).PrintOut , , 1, True, , True
.Worksheets(1).Select
End With
End Sub
I've got an application in which I'd like to create a "Print" button that prints specific worksheets, but there are some complications...
The first is that I have for tabs which initiate in the hidden state. User input dictates which of the four should become visible. Let's call these Site PFD 1, Site PFD 2, Site PFD 3, and Site PFD 4.
Second, there will be multiple tabs visible after the appropriate Site PFD tab comes into view - but I only want to print a subset of those visible.
I've got some code here that does work, but ideally - I'd like to send one print command. This code goes out and finds the visible Site PFD tab, then separate prints the other subset. Can you help me consolidate to one print command?
Thanks
Sub prt_Form()
' xlSheetVisible = -1
' Visible PFD
Dim sh As Worksheet
Dim arr() As String
Dim N As Integer
N = 0
For Each sh In ThisWorkbook.Sheets(Array("Site PFD - High", "Site PFD - Med LD", "Site PFD - Med", "Site PFD - Low"))
If sh.Visible = -1 Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = sh.Name
End If
Next
With ThisWorkbook
.Worksheets(arr).PrintOut , , 1, True, , True
' Subset of Standard Sheets
.Sheets(Array("Notes", "Main Form")).PrintOut , , 1, True, , True
.Worksheets(1).Select
End With
End Sub