ActiveSheet.Next with hidden sheets

[Guest]

Hello,

I currently have a spreadsheet that has a 'veryhidden' sheet.

I have written a macro to navigate foward and backwards throughout the
spreadsheet, however, when it gets to the hidden sheets location, the macro
will not skip over it.

Macro:
ActiveSheet.Next.Select
Even when I use the macro recorder, the code is as per above?!?!

I have found that the error is:
1004, 'Select method of worksheet class failed'.

I have managed to use the errorhandler to move past this by calling the next
sheet by name, but fear this sheetname may get changed in the future.... is
there anyway of retrieving the sheet index number?

Thanks,
KK

 

[Bob Phillips]

Try this

On Error Resume Next
ActiveSheet.Next.Select
Do While Err.Number <> 0
Err.Clear
Worksheets(ActiveSheet.Index + 2).Select
Loop
On Error Goto 0

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

Actually, there is a problem if there is more than one very hidden sheets
next to each other.

This overcomes that

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


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Tom Ogilvy]

Interesting problem:

Sub Nextsheet()
Dim sh As Object
If ActiveSheet.Index = Sheets.Count Then
MsgBox "At the last sheet"
Exit Sub
End If
Set sh = ActiveSheet.Next
Do While sh.Visible <> xlSheetVisible
If sh.Index <> Sheets.Count Then
Set sh = sh.Next
Else
MsgBox "Last visible sheet is selected"
Exit Sub
End If
Loop
sh.Select
End Sub


is a slightly different approach.