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

Maybe like this:

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


"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

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


"JLGWhiz" wrote:

Maybe like this:

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


"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

Try this (It works for me):
For each ws In Worksheets
ws.Acticate
if ws.Range("G32").Value="Print" then ws.PrintOut
Next
--
I am running on Office 2003, unless otherwise stated.


"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

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

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

"mathel" wrote:

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


"JLGWhiz" wrote:

Maybe like this:

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


"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