Multiple Worksheet Scrolling

[jwleonard]

This is the first time I have ever posted on a forum so please forgiv
me if I do something wrong.

I am creating a workbook with multiple spreadsheets, the first shee
simply totals cells in the other sheets. All sheets are very simila
and have information all in the same layout. I would like to get al
of the sheets to scroll together at the same time so if a user switche
worksheets then they will be in the same location as on the previou
worksheet. This would greatly enhance the usability of this workbook.
I don't know how to explain this any better right now, if I was unclea
on something just reply and let me know, then I will try to clarify!
will check back often and respond quickly. Also, I greatly appreciat
any help, this is for work and it seems I have bitten off more than
can chew this time.

I am using Office 2003 and would rate myself as an intermediate exce
user (Don't get visual basic though!) so just point me in the righ
direction please!

Thanks
Jef

 

[Dave Peterson]

First, this would drive me batty if I were referring to other cells on the
different worksheets and I didn't want the sheets "sync'ed".

But this seemed to work ok for me (and it's VBA):

Rightclick on the Excel Icon to the left of the File dropdown on the worksheet
menubar.

Select view code and paste this in the code window:

Option Explicit
Dim PrevSelection As Range
Private Sub Workbook_Open()
Set PrevSelection = Selection
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim myScrollRow As Long
Dim myScrollColumn As Long

On Error GoTo errHandler:

If PrevSelection Is Nothing Then
Set PrevSelection = Selection
Else
'go back to previous sheet and grab info

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If PrevSelection Is Nothing Then
'do nothing
Else
PrevSelection.Parent.Activate
With ActiveWindow
myScrollRow = .ScrollRow
myScrollColumn = .ScrollColumn
Set PrevSelection = .Selection
End With

'come back and match the previous stuff
Sh.Activate
Sh.Range(PrevSelection.Address).Select
Set PrevSelection = Selection
With ActiveWindow
.ScrollRow = myScrollRow
.ScrollColumn = myScrollColumn
End With
End If
With Application
.ScreenUpdating = True
End With
End If

errHandler:
Application.EnableEvents = True

End Sub

It tries to go back to the sheet that you just left--grab the info from there
and use it in the new worksheet's window.

 

[jwleonard]

Dave Peterson,

Thanks, that worked perfectly!!! I was a little worried at first wit
the code and all; but that couldn't have been easier. Now, if I onl
understood how it worked that would be even better! I guess I ha
better learn VBA, it seems there are no limits to what can be done!
Anyway, thanks again!

Jef