Run-time Error 1004 - Range 'Visible' of object '_Worksheet' failed

[s0up2up]

Hey team,

Hopefully this is an easy one to solve, but sitting here scratching my head on this one. When I run the below code, I get the following error and can't seem to figure out why...

Error
Run-time error '1004':

Method 'Visible' of object '_Worksheet' failed

Background
Macro that I am currently running loops through a set of .xlsx files in a folder and manipulates them one by one, assigning the current workbook as wbOpened and in this section of code loops through the workbooks tab, and hides all tabs that aren't named 'Subscribers' or 'Subscriber Check'.

<code>

For Each wshSheet In wbOpened.Worksheets        Select Case wshSheet.Name
            'Hide sheets unless name is either 'Subscribers' or 'Subscriber Check'
            Case "Subscribers"
                wshSheet.Visible = xlSheetVisible           'ensure sheet is visible
                Set wshSubscribersSheet = wshSheet          'populate wshSubscribersSheet object
            Case "Subscriber Check"
                wshSheet.Visible = xlSheetVisible           'ensure sheet is visible
                Set wshSubscriberCheck = wshSheet           'populate wshSubscriberCheck object
            Case Else
                wshSheet.Visible = xlSheetHidden            'hide sheet
        End Select
Next wshSheet
</code>

Now the line I am getting an error on is the line below Case Else 'wshSheet.visible = xlSheetHidden' and can't seem to understand why as I swear this code worked a moment ago.

Any help would definitely be appreciated!!

Kind regards, s0up2up

 

[louisH]

Hi.

It's probably because you are trying to hide the last visible sheet in the workbook. I tried it and it seems like the workbook needs at least 1 sheet visible.
You might try Something else.

First solution crossing my mind, doing :

Worksheets("Subscribers").Visible = xlSheetVisible
Worksheets("Subscriber Check").Visible = xlSheetVisible

Before your For Each loop.

 

[Fluff]

Check the names of the 'Subscribers' & 'Subscriber Check' sheets, especially look for any leading/trailing spaces.