Select sheets from an array for printing

[Guest]

I need Help!!
I have a workbook with hidden sheets, I want to search the visible sheets
for those with value greater than 0 in C2 then print them.
Can someone show me where I am going wrong with this code?
Tahnks

Sub PrintSelectedSheets()
'will print sheet with a value >0 in C2
For Each Sheet In ThisArray.Sheets(Array("Sheet1", "Sheet3", "Sheet5",
"Sheet7", "Sheet9"))
Sheet.Activate
If Range("C2").Value > 0 Then
ActiveSheet.PrintPreview
End If
Next
End Sub

 

[Bob Phillips]

Dim Sheet As Worksheet
Dim shStyle As Long
'will print sheet with a value >0 in C2
For Each Sheet In Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7",
"Sheet9"))
If Sheet.Range("C2").Value > 0 Then
shStyle = Sheet.Visible
Sheet.Visible = xlSheetVisible
Sheet.PrintPreview
Sheet.Visible = shStyle
End If
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob!
Works a treat

Dim Sheet As Worksheet
Dim shStyle As Long
'will print sheet with a value >0 in C2
For Each Sheet In Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7",
"Sheet9"))
If Sheet.Range("C2").Value > 0 Then
shStyle = Sheet.Visible
Sheet.Visible = xlSheetVisible
Sheet.PrintPreview
Sheet.Visible = shStyle
End If
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)