Synchronized scrolling in excel 2010 across multiple sheets

by Lj .   Last Updated September 13, 2017 14:01 PM

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.

Answers 2

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
May 07, 2015 12:42 PM

There is an add on called KuTools, which allows you to do this. Just go into the new Enterprise tab and click on worksheets / synchronise worksheets.

There are loads of other really useful functions too. You can install it for free as a trial and then pay for it if it is of any use to you. I have bought it and - for me anyway - it was worth every penny.

September 13, 2017 13:18 PM

Related Questions

Listbox, mouse wheel scroll issue

Updated October 22, 2015 08:00 AM

Formula issue Microsoft Excel

Updated July 02, 2015 16:00 PM

If Difference Higher or Lower than Display

Updated August 03, 2015 15:00 PM