Solved: Printing Specific Sheets

[ajrob]

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

[CCkfm2000]

see kb entry

http://vbaexpress.com/kb/getarticle.php?kb_id=129

submitted by mvdas

[ajrob]

I went through this code ... it's not exactly what I was looking for though. I don't want a user to be able to select the worksheets. Instead, I want to define them, and have the user print by clicking on a button that calls the macro. The code I have does work, but again, I was hoping to consolidate the .PrintOut into one line, so that I preserve the page numbering. Any other ideas?

[Ken Puls]

Hi there,

I'm not sure if I totally follow either, but you mean like this? (untested)

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
ReDim Preserve arr(1 To (N + 2))
arr(N + 1) = "Notes"
arr(N + 2) = "Main Form"
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

[ajrob]

That's it! Thank you,

Adam

[Ken Puls]

You're welcome. :)