Select method of sheets class failed when trying to hide sheets

Question

I am struggling with hiding sheets.

I used two sets of code:

 Sub hidesheets()
 Sheets("Materials - Specifications").Visible = False
 Sheets("Fibre drop release sheet").Visible = False
 End Sub

And next

Sub RAtoPDF()
Call hidesheets
Dim sh As Worksheet, sh2 As Worksheet 
Dim ArraySheets() As String
Dim custom_name As String
Dim x As Variant
        
For Each sh In ActiveWorkbook.Worksheets
    If sh.Tab.ColorIndex = 33 Then
        'Sheets("Materials - Specifications").Visible = False
        ReDim Preserve ArraySheets(x)
        ArraySheets(x) = sh.Name
        x = x + 1
    End If
        
Next sh

Sheets(ArraySheets).Select

custom_name = "RA_" & ThisWorkbook.Name & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                ThisWorkbook.Path & "\" & custom_name, _
                                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, OpenAfterPublish:=True
                                
Sheets("Frontsheet").Select
End Sub

I get:

Select method of sheets class failed

with debugger pointing to:

Sheets(ArraySheets).Select

I tried this line without an external macro, but the error is the same.

The XlVeryhidden option also is not working.

When you cycle through the sheets it will include hidden sheets, but you can't select hidden sheets. Check your ArraySheets elements when the debugger halts on that line. View > Locals Window — jamheadart
– jamheadart, Commented Jun 19, 2020 at 15:17
I would like to deselect the sheets which I don't need to. I used the hidden option, because I though that the code will omit them. — Geographos
– Geographos, Commented Jun 19, 2020 at 15:22
You should just create a list of sheets you want to exclude and check against that when adding to your ArraySheets — jamheadart
– jamheadart, Commented Jun 19, 2020 at 15:23
What's the tab color for the two hidden sheets? You don't really say why your're hiding them or whether those are the sheets you want to exclude... Might help to be a bit more explicit in your question. — Tim Williams
– Tim Williams, Commented Jun 19, 2020 at 15:40

Mo Khalefa · Accepted Answer · 2020-06-19 16:10:37Z

2

Select method of sheets class failed means that you select a hidden sheet for printing

If sh.Tab.ColorIndex = 33 And sh.Visible = True Then

answered Jun 19, 2020 at 16:10

Mo Khalefa

6361 gold badge6 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geographos Over a year ago

Yes! Perfect! This is exactly what I was looking for. Thank you so much.

Collectives™ on Stack Overflow

Select method of sheets class failed when trying to hide sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related