Move to next or previous unhidden sheet

[troy.gagnon]

I have buttons on my sheets that let me move from sheet to sheet using ActiveSheet.Next.Select and ActiveSheet.Previous.Select. However, if there's a hidden sheet between any two sheets, it won't move to that sheet - for obvious reasons - and nothing happens.

So, is there a way to move to the next UNHIDDEN sheet? Or will I have to test to see if the next sheet is hidden and skip over it somehow? If the latter, what might the code look like?

Thanks.

 

[Michael M]

Try this from a Bob Phillips post

Dim nSkip As Long
On Error Resume Next
Do
Err.Clear
nSkip = nSkip + 1
Worksheets(ActiveSheet.Index + nSkip).Select
Loop Until Err.Number = 0

 

[sharkiness]

Try this from a Bob Phillips post

Dim nSkip As Long
On Error Resume Next
Do
Err.Clear
nSkip = nSkip + 1
Worksheets(ActiveSheet.Index + nSkip).Select
Loop Until Err.Number = 0

Hi,

This code doesnt work for me but i simply use a

ActiveSheet.Next.Visible = True
ActiveSheet.Visible = False for next hidden or


ActiveSheet.Previous.Visible = True
ActiveSheet.Visible = False for previous hidden.

I apply this to each button on a click procedure through to all of them.

Would you know how to stop an error when I get to the end or beginning when there are no more hidden sheets

If you could tell me of a call procedure to use that would be great as when i tried this to call a module on click it thriough up an error.

Cheers

 

[klarowe]

I use these 2 codes for my next / previous sheets and it works perfectly on only going to the next visible sheet. Then if I get to the end of the workbook it just doesn't do anything (no errors come up).

Sub NextSheet()
    Set Sh = ActiveSheet
    On Error Resume Next
    Do While Sh.Next.Visible <> xlSheetVisible
        If Err <> 0 Then Exit Do
        Set Sh = Sh.Next
    Loop
        Sh.Next.Activate
    On Error GoTo 0
End Sub
Sub PrevSheet()
    Set Sh = ActiveSheet
    On Error Resume Next
    Do While Sh.Previous.Visible <> xlSheetVisible
        If Err <> 0 Then Exit Do
        Set Sh = Sh.Previous
    Loop
        Sh.Previous.Activate
    On Error GoTo 0
End Sub

Please note that these were not created by me and I take no credit for them. They were made for me by one of the great guys on here and I'm just forwarding it on.

 

[tlowry]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> 'Here’s what I came up with:

‘-----------------------------------------------
‘-----------------------------------------------
‘-----------------------------------------------
Sub nexter()
For i = ActiveSheet.Index + 1 To Sheets.Count
Select Case Sheets(i).Visible
Case True
Sheets(i).Select
Exit Sub
Case False
End Select
Next i
End Sub
‘-----------------------------------------------
‘-----------------------------------------------
‘-----------------------------------------------


‘-----------------------------------------------
‘-----------------------------------------------
‘-----------------------------------------------
Sub Prever()
For i = ActiveSheet.Index - 1 To 1 Step -1
Select Case Sheets(i).Visible
Case True
Sheets(i).Select
Exit Sub
Case False
End Select
Next i
End Sub
‘-----------------------------------------------
‘-----------------------------------------------
‘-----------------------------------------------