Select sheets from an array for printing

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

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)


"bennyob" <[email protected]> wrote in message
news:[email protected]...
> 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

Thanks Bob!
Works a treat

"Bob Phillips" wrote:

> 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)
>
>
> "bennyob" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>