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.
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.
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.
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 sht.Activate Range(sAddr).Select ActiveWindow.ScrollRow = lTopRow ActiveWindow.ScrollColumn = lLeftCol End If Next sht shUser.Activate Application.ScreenUpdating = True End Sub
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.