synchronize multiple worksheets
 

I have 7 worksheets in an Excel workbook.
all the worksheets have a lastname, firstname, employee ID in the A,B,C
columns of every worksheet.
I need to synchronize the worksheets so that when I click on an employee
name in any worksheet and select a different worksheet I see the same row as
the original employee name.
I realize this will be a VBA and I have no experience so i hope someone can
explain this in explicit terms.
thank you
don

Don,

Copy the code below, press Alt-F11 to open the VB Editor, press Ctrl-R to
open the project explorer, double click on your workbook to open it, then
double-click on the ThisWorkbook object, then in the window to the right
(usually), click the upper left dropdown, and select "Workbook" Paste the
code into that window, and it should behave as you want.

As written, this will work on names in column A only (Last names). If you
want, it could work on column C instead (employee ID).

If you have trouble getting this to work, post back and I will send you a
working file (leave your email with lots of extra spaces to foil the address
harvesters...)

HTH,
Bernie
MS Excel MVP

Dim myVal As Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Sh.Range("A:A").Find(myVal).Select
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Column = 1 Then myVal = Target(1).Value
End Sub


"don" wrote in message
...
I have 7 worksheets in an Excel workbook.
all the worksheets have a lastname, firstname, employee ID in the A,B,C
columns of every worksheet.
I need to synchronize the worksheets so that when I click on an employee
name in any worksheet and select a different worksheet I see the same row
as
the original employee name.
I realize this will be a VBA and I have no experience so i hope someone
can
explain this in explicit terms.
thank you
don

Bernie, code worked, but i need it to work on the row selection rather than
the name because names may be listed several times in Col A of the
worksheets. And also it ran the first time only, is there a way to turn it on
and leave it on while i'm working on it
"Bernie Deitrick" wrote:

Don,

Copy the code below, press Alt-F11 to open the VB Editor, press Ctrl-R to
open the project explorer, double click on your workbook to open it, then
double-click on the ThisWorkbook object, then in the window to the right
(usually), click the upper left dropdown, and select "Workbook" Paste the
code into that window, and it should behave as you want.

As written, this will work on names in column A only (Last names). If you
want, it could work on column C instead (employee ID).

If you have trouble getting this to work, post back and I will send you a
working file (leave your email with lots of extra spaces to foil the address
harvesters...)

HTH,
Bernie
MS Excel MVP

Dim myVal As Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Sh.Range("A:A").Find(myVal).Select
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Column = 1 Then myVal = Target(1).Value
End Sub


"don" wrote in message
...
I have 7 worksheets in an Excel workbook.
all the worksheets have a lastname, firstname, employee ID in the A,B,C
columns of every worksheet.
I need to synchronize the worksheets so that when I click on an employee
name in any worksheet and select a different worksheet I see the same row
as
the original employee name.
I realize this will be a VBA and I have no experience so i hope someone
can
explain this in explicit terms.
thank you
don