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


mathel wrote:

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
--
Linda

--

Dave Peterson