Excel – Synchronized scrolling in excel 2010 across multiple sheets


I'm trying to figure out, how to scroll across multiple worksheets of one work book in a split horizontal view, without row number being off, when switching between the sheets.

Excel synchronized scroll works good when you have 2 sheets. I have sheet1 always at the top, and sheets 1 to 5 on the bottom. When I switch between the sheets on the bottom, row numbers are out of sync. Also the longer I was trying to figure out the solution,the more out of syns numbers were, not even reset window position helped.

  1. Problem:Let say I'm at row 100 sheet1, row 100 sheet2, then I switch to sheet 3, which will start at number 1, but sheet1 is at number 100. And I get more messed up the more I switch.

  2. Problem: Why does scrolling starts to lag after a while? For example: Sheet1 is at number 1, Sheet2 is at number 1. When I start to scroll at sheet1, then it reaches number 60, and only then sheet2 starts to scroll from number 1, but then you have 60 number difference?

Any help is appreciated.

Best Answer

First, Excel is working properly. You are expecting it to do something it cannot do.

Synchronous Scrolling is designed to work with only two sheets at a time. That is the limit of the program. Each time you switch sheets, it starts where that sheet was when it will not active. Therefore, the more you switch the more "out of sync" the sheets get.

The only work around I found is a VBA macro you can try if you like. Synchronous Scrolling with More than Two Windows uses the following VBA to scroll more than two sheets at a time.

Note: Use at your own risk. Backup your work first.

Sub SynchSheets()
    ' Duplicates the active sheet's cell position in each sheet

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    Dim shUser As Worksheet
    Dim sht As Worksheet
    Dim lTopRow As Long
    Dim lLeftCol As Long
    Dim sAddr As String

    Application.ScreenUpdating = False

    ' Note the current sheet
    Set shUser = ActiveSheet

    ' take information from current sheet
    With ActiveWindow
        lTopRow = .ScrollRow
        lLeftCol = .ScrollColumn
        sAddr = .RangeSelection.Address
    End With

    ' loop through worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            ActiveWindow.ScrollRow = lTopRow
            ActiveWindow.ScrollColumn = lLeftCol
        End If
    Next sht

    Application.ScreenUpdating = True
End Sub