Can I Offset of Excel Tabs similar to ActiveCell.Offset

[senators40]

I have a number of tabs that in an excel file with many different names. Can I create a macro that goes to the left or right of the active tab similar to the ActiveCell.Offset feature in an excel workbook without entering in the tab or sheet name.

Thanks in advance

Jeff

 

[SkipVought]

Hi,

Sub TabLeft()
    If ActiveSheet.Index = 1 Then
        idx = Worksheets.Count
    Else
        idx = ActiveSheet.Index - 1
    End If
    Sheets(idx).Select
End Sub
Sub TabRight()
    If ActiveSheet.Index = Worksheets.Count Then
        idx = 1
    Else
        idx = ActiveSheet.Index + 1
    End If
    Sheets(idx).Select
End Sub

You could assign TabRight to shift+ctrl+R etc

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

A little less code than Skip's and it does exactly what you want.

To go one sheet forward use:

Sub SheetOffset_Next()
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Select
On Error GoTo 0
End Sub

To go one sheet back use:

Sub SheetOffset_Previous()
On Error Resume Next
Sheets(ActiveSheet.Index - 1).Select
On Error GoTo 0
End Sub

Due to the errror trapping, no page index checking is needed.

Sorry Skip!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

What is there to be sorry about?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

Sorry for butting in.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

senators40,

To make this even easier, you can use the SendKeys function. Besides, my previous code, doesn't take into account that some sheets might be hidden. You should use the following code.

The keyboard shortcut to goto the next sheet is Ctrl+PgUp and Ctrl+PgDn to goto the previous sheet.

Sub NextSheet()
' goes to the next visible worksheet
Application.SendKeys ("^{PgUp}")
End Sub

Sub PreviousSheet()
' goes to the previous visible worksheet
Application.SendKeys ("^{PgDn}")
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!