Printing hidden sheets with vba.

[gtslabs]

I have a sheet with a command button linked to a macro that will print
out other sheets in the workbook. Id like to have the printable sheets
hidden from view. I thought it was printing fine but today I got the
following error.

"Run-Time Error 1004"
"Printout method of worksheet failed"

My code is the following:
Worksheets("Standard").PrintOut

Can I print hidden sheets? or do I need to unhide, print, then hide
again?

Thanks
Steve

 

[Ron de Bruin]

Hi gtslabs

See
http://www.rondebruin.nl/print.htm#visible

 

[Don Guillett]

use a macro to

Sub printsheet()
Application.ScreenUpdating = False
With Sheet1
..Visible = True
..PrintPreview
..Visible = False
End With
Application.ScreenUpdating = True
End Sub

 

[gtslabs]

Thanks
I have this print statment in a Select Case Structure with 36 cases and
36 different sheet names.

How can I pass my sheet name to that code making that code a
subroutine?

 

[Don Guillett]

post your code

 

[gtslabs]

Don here is a part of it. I left out all the other cases to keep the
post smaller.
Also I have this as a command button code but I would love to have it
as a module and have the requesting sheet be the active sheet.
Thanks
Steve


Private Sub CommandButton2_Click()

Dim R, C As Integer

Dim client_contact, client, project, project_number, boring, depth,
sample, lab_Id, test, date_rec As String

client = Range("C4")
project = Range("c5")
project_number = Range("p11")
date_rec = Range("ak12")

Worksheets("input").Range("b3..b6").Value = "" 'clear range
Worksheets("input").Range("b10..k13").Value = "" 'clear range

Worksheets("input").Range("b3").Value = client
Worksheets("input").Range("b4").Value = project
Worksheets("input").Range("b5").Value = project_number
Worksheets("input").Range("b6").Value = date_rec

For C = 6 To 38 ' Scroll thru all the tests
If Cells(33, C).Value > 0 Then ' Check to see if any tests for this
project
z = 2
For R = 19 To 28
If Cells(R, C).Value <> "" Then
Worksheets("input").Cells(10, z).Value = Cells(R, 2).Value '
insert boring
Worksheets("input").Cells(11, z).Value = Cells(R, 3).Value '
insert depth
Worksheets("input").Cells(12, z).Value = Cells(R, 4).Value '
insert sample
Worksheets("input").Cells(13, z).Value = project_number &
Cells(R, 1).Value ' insert lab id number
z = z + 1
End If
Next R


Select Case C
Case 6

Sheets("Water Content").Visible = True ' Note I just started to go thru
and add all these statements.
Worksheets("Water Content").PrintOut
Sheets("Water Content").Visible = False ' Note I just started to go
thru and add all these statements.



Case 7
Sheets("Limit").Visible = True ' Note I just started to go thru and add
all these statements.

Worksheets("Limit").PrintOut
For s = 3 To Cells(33, C).Value + 1
Worksheets("input").Cells(10, 2).Value = Worksheets("input").Cells(10,
s).Value ' insert boring
Worksheets("input").Cells(11, 2).Value = Worksheets("input").Cells(11,
s).Value ' insert Depth
Worksheets("input").Cells(12, 2).Value = Worksheets("input").Cells(12,
s).Value ' insert Sample
Worksheets("input").Cells(13, 2).Value = Worksheets("input").Cells(13,
s).Value ' insert Lab Sample No.
Worksheets("Limit").PrintOut
Next s
Sheets("Limit").Visible = False ' Note I just started to go thru and
add all these statements.


Case 8
Sheets("Shrinkage").Visible = True ' Note I just started to go thru and
add all these statements.

Worksheets("shrinkage").PrintOut
Sheets("Shrinkage").Visible = False ' Note I just started to go thru
and add all these statements.


End Select

End If
Next C
End Sub