moving to next worksheet

[Kent McPherson]

I have a workbook with several worksheets. I hide some worksheets for
various reasons. I have navigation buttons on each worksheet that are
support to go to the previous or the next worksheet. The macro code I'm
using is:

Prev: ActiveSheet.Previous.Next

Next: ActiveSheet.Next.Select

If the next sheet has been hidden, I get a run-time error '1004'. How
can I move to the next visible worksheet ?

 

[hideki]

Hi, this is the way I always did int Excel 2000. I know its not a goo
code at all. I'm a beginner myself. I appreciate any kind of comments.

Sub GoToPrevioustSheet()

Dim idx As Integer

idx = ActiveSheet.Index
If idx = 1 Then
MsgBox "This is the first sheet"
Exit Sub
ElseIf Sheets(idx - 1).Visible = False Then
Do Until Sheets(idx - 1).Visible = True
idx = idx - 1
Loop
Sheets(idx - 1).Select
Else
Sheets(idx - 1).Select
End If

End Sub

Sub GoToNextSheet()

Dim idx As Integer

idx = ActiveSheet.Index
If idx = Sheets.Count Then
MsgBox "This is the last sheet"
Exit Sub
ElseIf Sheets(idx + 1).Visible = False Then
Do Until Sheets(idx + 1).Visible = True
idx = idx + 1
Loop
Sheets(idx + 1).Select
Else
Sheets(idx + 1).Select
End If

End Su

 

[Tom Ogilvy]

Sub AA()

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

 

[Kent McPherson]

Sub AA()

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

Thanks! Works perfectly.