simultaneous scrolling among certain sheets

[esbencito]

Hi all,

Is there a VBA code to have Excel scroll simultaneously among different sheets (not all sheets, only specific ones)? I would need something like the below but without having ALL sheets move their position...

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

 

[tonyyy]

You might try...

Sub SynchSomeSheets()
' 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
    [COLOR=#ff0000]If sht.Name = "Sheet2" Or sht.Name = "Sheet4" Then 'change to match your sheet names[/COLOR]
        sht.Activate
        Range(sAddr).Select
        ActiveWindow.ScrollRow = lTopRow
        ActiveWindow.ScrollColumn = lLeftCol
    End If
Next sht

shUser.Activate
Application.ScreenUpdating = True
End Sub

 

[esbencito]

Thanks!! But it doesn't seem to work as wished...

It seems to scroll a few rows and columns in the other sheets, but only if I run the Macro every time before I scroll (can I have it run permanently in the background?), and it doesn't move to the exact same position as in the active sheet!

Sub SynchSomeSheets()
' 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.Name = "TTL APAC" Or sht.Name = "Japan" Or sht.Name = "Korea" Or sht.Name = "HK Hub" Or sht.Name = "China" Or sht.Name = "HMT" Or sht.Name = "SEA" Or sht.Name = "ANZ" Then
        sht.Activate
        Range(sAddr).Select
        ActiveWindow.ScrollRow = lTopRow
        ActiveWindow.ScrollColumn = lLeftCol
    End If
Next sht


shUser.Activate
Application.ScreenUpdating = True
End Sub

 

[esbencito]

Does anyone have a solution for this? Is this even feasible?