Help me please!!! Excel 2003

[Dan]

I have a situation where I am trying to make a contacts list for different
people in different counties in the surrounding area of my work that are
directly involved with my job. I have my worksheet setup so each county has a
different tab and then there is one master list. I have it setup so when you
change info in one of the county tabs it updates the master list but that is
as far as I can go. I need to be able to link the masterlist with the county
tabs so that when the masterlist is updated it updates the the county tabs
and when the county tabs are updated the masterlist is updated. Also I need
to the list in a specific order so when I use the sort function it messes up
my data. Can anyone help me?

 

[Joel]

You need a macro that can be mannually run, not automatically. If you had an
automatic macro it would run forever because when the masterworksheet got
change it would change the county tabs and then the country tab would change
the master tab.

The orhter problem is when you have more than one column in a tab what would
be the trigger to update the other sheets. Writing data in column A or
column B.

 

[Don Guillett]

Put this in the ThisWorkbook module. Be advised that , as written, you MUST
have the sheet name in the cell to the left of the activecell on the MASTER
sheet. There has to be some way to determine where to go.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
mc = ActiveCell.Address
If ActiveSheet.Name = "Master" Then
ms = ActiveCell.Offset(, -1)
Sheets(ms).Range(mc) = Target
Else
Application.EnableEvents = False
Range(mc).Copy Sheets("master").Range(mc)
Aplication.EnableEvents = True
End If
End Sub

 

[Dave Peterson]

I wouldn't approach it this way.

I'd keep all my data in one worksheet and apply data|Filter|autofilter to show
(or hide) the data that I wanted to see (or not see).

But if I had to split the data into separate worksheets, I'd still update a
single sheet and use a macro to copy the data to different worksheets.

You may want to look at how Ron de Bruin and Debra Dalgleish approached this
kind of thing:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb