Solved: Conditional Print Preview

[sbeatton]

I have posted this on MrExcel.com, but not having much luck.

We are trying to develop further some earlier code as follows. Basically what we want is.

If in Sheets
"Manifest", "Manifest (2)", "Manifest (3)", "2-pg Manifest")if Q5 OR C5 is not blank, select and group the sheets and print preview.




Sub print()
'This is extracted from larger code
For Each worksheet In Sheets(Array("Manifest", "Manifest (2)", "Manifest (3)", "2-pg Manifest"))
If Range("Q5").Value > 0 OR Range("C5").Value > 0 Then
Worksheet.Select
End If
Selection.PrintPreview

End Sub


I may be going about this all the wrong way. Any help much appreciated.
Thanks

[mikerickson]

That code is causing problems for me
The name of the sub, print, gives me an "Identifier required" compile error.
I am surprised that a variable name worksheet didn't fataly compete with the object type Worksheet.
But this worked for me.

Sub Pprint()
Dim worksheet As worksheet
'This is extracted from larger code
For Each worksheet In Sheets(Array("sheet1", "sheet2"))
If worksheet.Range("Q5").Value <> vbNullString Or worksheet.Range("C5").Value <> vbNullString Then
worksheet.PrintPreview
End If
Next worksheet
End Sub

[sbeatton]

Thanks Mike and apologies for the Sub Print(). I just added a quick name to a cut and paste of code without thinking.

That is pretty much it however is it possible to do the check and then group the worksheets in a print preview?
Thanks again,

[Simon Lloyd]

sbeatton, it is courtesy when cross posting to provide a link to the cross post, you already have some 45 posts here (http://www.mrexcel.com/forum/showthread.php?t=318142&page=5)

[sbeatton]

Good point Simon, should have linked to my original post rather than just mention it at the top. Apologies if have wasted anyone's time.

Whilst jwgreen who I was assisting has posted he received a reply on that post, any help regarding the above woul dbe appreciated as it applies to some code of my own I am writing.
Thanks,

[mikerickson]

Dim printSheets As Variant
Dim oneSheetName As Variant, Pointer As Integer

printSheets = Array("Manifest", "Manifest(2)", "Manifest (3)", "2-pg Manifest")

Pointer = LBound(printSheets) - 1
For Each oneSheetName In printSheets
With Sheets(oneSheetName)
If .Range("c5").Value <> vbNullString Or .Range("Q5").Value <> vbNullString Then
Pointer = Pointer + 1
printSheets(Pointer) = oneSheetName
End If
End With
Next oneSheetName

If LBound(printSheets) <= Pointer Then
ReDim Preserve printSheets(LBound(printSheets) To Pointer)
Sheets(printSheets).PrintPreview
Else
Erase printSheets
End If

[sbeatton]

That has well and truly pushed my VBA knowledge out the door. Will now sit down and work out what exactly you've done Mike. I am encountering a problem however when it gets to:



With Sheets(oneSheetName)

I am getting a subscript out of range error.

Any ideas?

[Simon Lloyd]

Perhaps change this:

Dim oneSheetName As Variant
to

Dim oneSheetName As String

[mikerickson]

The control variable of a For Each loop needs to be type Variant.

Since its an "Out of Range error" I'd suspect that one of the sheet names may be misspelt.

[sbeatton]

Spot on Mike. Names were missing a space. Final Code in case anyone interested:


Dim printSheets As Variant
Dim oneSheetName As Variant, Pointer As Integer
printSheets = Array("Manifest", "Manifest (2)", "Manifest (3)", "2-pg Manifest")

Pointer = LBound(printSheets) - 1
For Each oneSheetName In printSheets
With Sheets(oneSheetName)
If .Range("c5").Value <> vbNullString Or .Range("Q5").Value <> vbNullString Then
Pointer = Pointer + 1
printSheets(Pointer) = oneSheetName
End If
End With
Next oneSheetName

If LBound(printSheets) <= Pointer Then
ReDim Preserve printSheets(LBound(printSheets) To Pointer)
Sheets(printSheets).PrintPreview
Else
Erase printSheets
End If

Thanks again for your help.