Avatar of andrefm
andrefmFlag for Australia asked on

Synchronize excel worksheets (cell navigation + scrollbars)

Hi,
I have one excel file with 12 worksheets. All the worksheets are very large with the same scruture.
What I need:
 - As soon as I change the view (change position of scrollbar) and/or selected any cell in any of the worksheets, all other worksheets should synchronize to same view and cell selection.

Is that possible?
Microsoft Excel

Avatar of undefined
Last Comment
andrefm

8/22/2022 - Mon
SiddharthRout

Cell selection, yes

I am not sure about the 'View' though....

Sid
ASKER CERTIFIED SOLUTION
SiddharthRout

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
andrefm

Nice to see VERY fast answers, but in mean time I found the solution. The ones informed were not working properly.

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

Sub SynchSheets()
'   Duplicates the active sheet's active cell upperleft cell
'   Across all worksheets
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Dim UserSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Integer
    Dim UserSel As String
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False

'   Remember the current sheet
    Set UserSheet = ActiveSheet
    
'   Store info from the active sheet
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
'   Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht
    
'   Restore the original position
    UserSheet.Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Open in new window

ASKER
andrefm

My solution is tested and working properly. Anyone can try.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
andrefm

This for some could be better as it changes only when changing the sheet and not always when clicking at one cell.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    
    If TypeName(Sh) <> "Worksheet" Then Exit Sub
    Dim NewWorkSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Integer
    Dim UserSel As String
    
        
    Application.EnableEvents = False
    Application.ScreenUpdating = False

'   Remember the new sheet
    Set NewWorkSheet = ActiveSheet

'   Activate the previous Worksheet
    Sh.Activate
    
'   Store info from the active sheet
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
'   Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht
    
'   Restore the original position
    NewWorkSheet.Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Open in new window