Can you make all sheets scroll when scrolling one sheet

[Guest]

Is it possible to have worksheets scroll at the same time? For example when
you scroll down to line 100 on sheet 1 and then you go to sheet 2 or 3 or 4
and that sheet will be on line 100 also?

 

[Bernie Deitrick]

Billy,

Sure, it's possible - easy, even. Copy the first sub below (an Excel event), and paste it into the
codemodule of the Thisworkbook object. Then copy the second sub (blantantly copied from John
Walkenbach's excellent website: http://j-walk.com/ss/excel/tips/tip75.htm) into a regular
codemodule.

For extra instructions and help working with event code, see
http://www.mvps.org/dmcritchie/excel/event.htm

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
SynchSheets
Application.EnableEvents = True
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.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
End Sub