Help Fixing Error on Print Macro

**groundin** · 03-08-2013, 04:56 PM

I found this on Ron DeBruins site. Works great, until I tried to modify it...
Basically I want it to print any hidden sheets that have a value in cell A600. I changed one line in the code and now get the error in the attached file on the line indicated in the code. Also added the original code so you can see the change I made

Sub Print_All_Worksheets_With_Value_In_A600()
    Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = False And Sh.Range("A600").Value <> "" Then  ' Original Code...If Sh.Visible = xlSheetVisible And Sh.Range("A1").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    With ActiveWorkbook
        .Worksheets(Arr).PrintOut  'This is the line hilighted as the error
    End With
End Sub

error.PNG

**Leith Ross** · 03-08-2013, 05:16 PM

Hello groundin,

You need to change the With..End With portion of your code. You can use an Array with the Sheets object to select sheets in workbook but not the Worksheets object. here is the corrected code.

Sub Print_All_Worksheets_With_Value_In_A600()
    Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = False And Sh.Range("A600").Value <> "" Then  ' Original Code...If Sh.Visible = xlSheetVisible And Sh.Range("A1").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    With ActiveWorkbook
        .Sheets(Arr).PrintOut  'This is the line hilighted as the error
    End With
End Sub

**groundin** · 03-08-2013, 07:55 PM

Thanks, Changed it, but still getting same error...I was looking at the code, should i change

For Each Sh In ActiveWorkbook.Worksheets

worksheets here to sheets as well?

**groundin** · 03-11-2013, 02:03 PM

Hi Again,
I tried changing the .Worksheets to .Sheets, but still got the same error. Here is the existing code.

Sub Print_All_Worksheets_With_Value_In_A600()
    Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    For Each Sh In ActiveWorkbook.Sheets
        If Sh.Visible = False And Sh.Range("A600").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    With ActiveWorkbook
        .Sheets(Arr).PrintOut 'This is where I get the debug error
    End With
End Sub

Any help would be appreciated.
Thanks

**groundin** · 03-15-2013, 05:26 PM

Hi All,
Here is the code that ended up doing what I needed it to do. Thanks for your time.

Sub Print_All_Worksheets_With_Value_In_A600()
    Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    Call PrintSlideSheet
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Range("A600").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    With ActiveWorkbook
        For N = 1 To UBound(Arr)
If ActiveWorkbook.Worksheets(Arr(N)).Visible = xlSheetHidden Then
ActiveWorkbook.Worksheets(Arr(N)).Visible = xlSheetVisible
ActiveWorkbook.Worksheets(Arr(N)).PrintOut
ActiveWorkbook.Worksheets(Arr(N)).Visible = xlSheetHidden
Else
ActiveWorkbook.Worksheets(Arr(N)).PrintOut
End If
Next N

    End With
End Sub