The problem can be avoided by using the Deactivate Event rather than the
Activate Event.

Let's say that for each worksheet we have a Deactivate macro that just
records the address of the activecell in a public, static, string variable.

That way, any time a worksheet and been activated and then deactivated, any
other sheet will have direct access to the other sheet's most recent active
cell.

Thus you can avoid the "bouncing" behavior in my previous post.
--
Gary''s Student - gsnu2007k


" wrote:

The codes posted are exactly what I asked for. Thanks to both of
you..

I still have a problem in that the code is activated by a
Worksheet_Activate sub. The toggling between worksheets causes the
code to loop endlessly. I'm not sure how to set up a flag that will
only allow a single activation of the subroutine.

..Private Sub Worksheet_Activate()
Dim ActWks As Worksheet
Dim myRow As Long
Application.ScreenUpdating = False
Set ActWks = ActiveSheet
With Worksheets("CaseLoad")
.Select
selectedrow = Selection.Row
End With
updateflag = 1
ActWks.Select
Application.ScreenUpdating = True

Cells(4, 112).Value = Worksheets("caseload").Cells(selectedrow,
1).Value
End Sub

sorry to complicate a simple question.