move between worksheets

[Dan]

Does anyone know if you can move from one worksheet to
another in a workbook.

The CTRL-PAGE UP/DOWN feature does this, but I want to do
it with a twist.

I want a shortcut that will go to the same cell in the
next sheet. This is for auditing large workbooks.

 

[Frank Kabel]

Hi
one way: assign the following two macros two some shortcuts:

Sub move_it_next()
Dim index_count
Dim cur_address
index_count = ActiveSheet.index
If index_count < Sheets.Count Then
index_count = index_count + 1
End If
cur_address = ActiveCell.Address
Sheets(index_count).Activate
Range(cur_address).Select
End Sub
'--------------------

Sub move_it_prev()
Dim index_count
Dim cur_address
index_count = ActiveSheet.index
If index_count >1 Then
index_count = index_count -1
End If
cur_address = ActiveCell.Address
Sheets(index_count).Activate
Range(cur_address).Select
End Sub

 

[JE McGimpsey]

One way:

This code, placed in the ThisWorkbook code module (right-click on the
workbook title bar and choose View Code), will do that automatically:

Dim sActiveAddress As String

Private Sub Workbook_Open()
sActiveAddress = Selection.Address(False, False)
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Range(sActiveAddress).Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
sActiveAddress = Selection.Address(False, False)
End Sub