VBA - Debugging Print Error b.c of Hidden/Very Hidden Sheets

[vbanoob1234]

Hello everyone,

This is my code. This code will grab all the files from a source folder, open each workbook, print all worksheets, and then close it. repeat.

However, I am getting an error saying "Run Time Error 1004. Method "PrintOut" of object_Worksheet. failed.

I think it is because there are very hidden and hidden sheets on the workbook.
Any help is appreciated. Thanks

Dim sFil As String, sPath As String


Set wb = ActiveWorkbook
Set ws = ActiveSheet


'change path to match yours


sPath = "C:\Desktop" 'location of files
sFil = Dir(sPath & "*.xlsx") 'change or add formats


Application.DisplayAlerts = False
Do Until sFil = ""
Workbooks.Open sPath & sFil

Set wb = ActiveWorkbook

'ws.PrintOut

For Each ws In wb.Worksheets
If Worksheet.Visible Then ws.PrintOut
Next
wb.Close


sFil = Dir()
Loop
Application.DisplayAlerts = True


End Sub

Thank you

 

[StephenCrump]

Presumably you've defined Worksheet, if you're not getting an error with Worksheet.Visible?

But it looks like you want:

For Each ws In wb.Worksheets
    If [COLOR=#ff0000][B]ws[/B][/COLOR].Visible Then ws.PrintOut
Next

 

[mikerickson]

The .Visible property of a worksheet is NOT a boolean value.
Change the code to

For Each ws In wb.Worksheets
    If ws.Visible = xlSheetVisible Then ws.PrintOut
Next

If you run this test code on a worksheet with two worksheets, I think you might be surprised by the third message box

Sub test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ws.Visible = xlSheetVisible
    If ws.Visible Then
        MsgBox "xlSheetVisible, .Visible=True"
    Else
        MsgBox "xlSheetVisible, .Visible=False"
    End If
    
    ws.Visible = xlSheetHidden
    If ws.Visible Then
        MsgBox "xlSheetHidden, .Visible=True"
    Else
        MsgBox "xlSheetHidden, .Visible=False"
    End If
    
    ws.Visible = xlSheetVeryHidden
    If ws.Visible Then
        MsgBox "xlSheetVeryHidden, .Visible=True"
    Else
        MsgBox "xlSheetVeryHidden, .Visible=False"
    End If
End Sub

 

[StephenCrump]

I think you might be surprised by the third message box

Oops, thanks Mike. Not surprised, just ...

 

[vbanoob1234]

Hi mikerickson

Thank you for your code. It worked perfectly. Much appreciated.

However, I am not understanding your sub test() code,
Could you elaborate? It just deletes one of my sheet when I run it.

Thanks

 

[mikerickson]

If you look at the third IF, you'll see that the

If ws.Visible Then goes to the True branch when ws is VeryHidden. Its a demonstration of the inaccuracy of testing ws.Visible as a Boolean and the need to test ws.Visible against xlSheetVisiblibility constants.