ActiveSheet.Next with hidden sheets
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 |
ActiveSheet.Next with hidden sheets
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) "KurviousKoala" wrote in message ... 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 |
ActiveSheet.Next with hidden sheets
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) "KurviousKoala" wrote in message ... Fab!!! thanks a million :-) "Bob Phillips" wrote: 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) "KurviousKoala" wrote in message ... 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 |
ActiveSheet.Next with hidden sheets
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. -- Regards, Tom Ogilvy "KurviousKoala" wrote in message ... Fab!!! thanks a million :-) "Bob Phillips" wrote: 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) "KurviousKoala" wrote in message ... 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 |
All times are GMT +1. The time now is 08:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com