Thread: Scroll to next sheet?

I suggest you keep columns A and IV clear of data

code in the Thisworkbook object

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = Columns.Count Then
        ' move to next sheet
        If Sh.Index + 1 <= Worksheets.Count Then
            Application.Goto Worksheets(Sh.Index + 1).Cells(Target.Row, 2)
        Else
            Application.Goto Worksheets(1).Cells(Target.Row, 2)
        End If
    ElseIf Target.Column = 1 Then
        ' move to previous sheet
        If Sh.Index = 1 Then
            Application.Goto Worksheets(Worksheets.Count).Cells(Target.Row, Columns.Count - 1)
        Else
            Application.Goto Worksheets(Sh.Index - 1).Cells(Target.Row, Columns.Count - 1)
        End If
    End If
    
End Sub

1) On sheet1, don't use column IV, just put a header at the top that says "Next Page" or something like that.
2) On sheet2, don't use column A, just put a header at the top that says "Previous Page" or something like that.
3) Right-click on sheet1 tab and select VIEW CODE
4) Paste in the worksheet event code into the module that appears:

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = Columns.Count Then
        Sheets("Sheet2").Activate
        Sheets("Sheet2").Range("B1").Select
    End If
End Sub

5) Right-click on sheet2 tab and select VIEW CODE
6) Paste in the worksheet event code into the module that appears:

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        Sheets("Sheet1").Activate
        Sheets("Sheet1").Cells(1, Columns.Count - 1).Select
    End If
End Sub

7) Close the VBEditor
8) Save your sheet

Now try scrolling off the right side of Sheet1 and it will switch you to column B of Sheet2. Try scrolling off the left of Sheet2 and it will switch you to the end of Sheet1....so you can just scroll back and forth.

Andy, that is awesome!!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)