Run-time error '1004': Select method of Worksheet class failed. Help :)

[Mr Retirement]

I'm trying to loop through my sheets and when I get to the 8th sheet, it has this error:

"Run-time error '1004': Select method of Worksheet class failed."


Anyone see my mistake? It works great through the first 7 sheets, not sure why it wont continue.


My Code is:

Sub RefreshAllWorksheets()
Dim Count, i As Long
 i = 1
 Count = Sheets.Count
    Do While i < Count
     Sheets(i).Select
     
     Sheets(i).Unprotect
     
     Call HypMenuVRefresh
    
     Sheets(i).Cells(1, 12).Select
        ActiveCell.Interior.Color = RGB(255, 255, 255)
     
     Sheets(i).Protect
     
     i = i + 1
    Loop
End Sub

Thanks!

 

[Mr Retirement]

I think it was caused because I had a Sheet hidden. Any tips to edit my code that it will skip hidden sheets and continue?

 

[John_w]

    If Sheets(i).Visible = xlSheetVisible Then
        
    End If

 

[Mr Retirement]

My code still wouldn't go past the hidden tab. Am I applying your thought correctly?

Sub RefreshAllWorksheets()
Dim Count, i As Long
 i = 1

 Count = Sheets.Count
    Do While i < Count

    If Sheets(i).Visible = xlSheetVisible Then

     Sheets(i).Select
     
     Sheets(i).Unprotect
     
     Call HypMenuVRefresh
    
     Sheets(i).Cells(1, 12).Select
        ActiveCell.Interior.Color = RGB(255, 255, 255)
     
     Sheets(i).Protect
     
     i = i + 1

     End If

    Loop

End Sub

 

[John_w]

Not quite. The i = i + 1 should go outside the If block because you always want to increment the loop index.

And shouldn't the loop be Do While i <= Count? Otherwise it doesn't loop to the last sheet. Or just use a For Next loop instead.

 

[Mr Retirement]

Yes! Thank you for the <= tip. I changed that as well!