Print Command - Array of Worksheets

[mathel]

I need some help with printing an array of worksheets in a workbook based on
data in a specific cell.

I have a workbook with 11 worksheets within it. Depending on the
information carried forward to the different worksheets, a formula in cell
G32 will indicate 'Print' or remain blank. What I am trying to accomplish is
a macro that will loop through each of the worksheets and print those that
indicate Print in cell G32.

Following is what I tried to accomplish this:

Sub Print_All_Worksheets_With_Value_In_G32()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible And Sh.Range("G32").Value <> "Print"
Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub

However, when I try and run this macro, it stops running and the following
line is highlighted:

If Sh.Visible = xlSheetVisible And Sh.Range("G32").Value <> "Print"
Then

Any help to correct this problem would be greatly appreciated

 

[JLGWhiz]

Maybe like this:

For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = True And Sh.Range("G32").Value <> "Print"

 

[mathel]

I copied the line below and replaced the line in my current macro,
unfortunately when I run it, the macro still stops running and hightlights
the same line.

I really hope you can help, there are about 6 - 8 possible variations of
worksheets that need to be printed.

Thanks

 

[Orion Cochrane]

Try this (It works for me):
For each ws In Worksheets
ws.Acticate
if ws.Range("G32").Value="Print" then ws.PrintOut
Next

 

[Dave Peterson]

If you want to print the sheets that have Print in G32, then you'd want to use
equal (not <>), right?

Option Explicit
Sub Print_All_Worksheets_With_Value_In_G32()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Long
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible _
And LCase(Sh.Range("G32").Text) = LCase("Print") Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
If N > 0 Then
ActiveWorkbook.Worksheets(Arr).PrintOut preview:=True
End If
End Sub

And my guess is that your formula is returning an error in G32 of one of those
sheets and sh.range("G32").value will cause that error. You can use .text to
avoid that error.

And here's another way to build that array. Notice that it's only redim'ed
twice. Once to make it large enough for all the worksheets and another to
resize to just the count of the worksheets that should be printed:

Option Explicit
Sub Print_All_Worksheets_With_Value_In_G32()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Long

N = 0
ReDim Arr(1 To ActiveWorkbook.Worksheets.Count)
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible _
And LCase(Sh.Range("G32").Text) = LCase("Print") Then
N = N + 1
Arr(N) = Sh.Name
End If
Next
If N > 0 Then
ReDim Preserve Arr(1 To N)
ActiveWorkbook.Worksheets(Arr).PrintOut preview:=True
End If
End Sub

 

[JLGWhiz]

What is the message that you are getting when it highlights the line?