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 ?

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



--
Regards,
Tom Ogilvy

"Kent McPherson" wrote in message
...
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 ?

Tom Ogilvy wrote:
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.

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

--
hidek
-----------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890
View this thread: http://www.excelforum.com/showthread.php?threadid=39335