Distribute Data from One Main Worksheet to Several Other Worksheets

Greetings Forum;

I have searched for this but did not find anything here that seemed to help – I hope I searched logically.

I am in need of an Excel Macro to help me create an address list by market. For simplicity sake I’ll set up a mini-example: I have an Excel document with a MASTER tab that lists the Market (Column A), a person’s name (Column B) and their Phone number (Column C). What I need to macro to do is read column A and make a new tab for each market in that column, then populate the name and phone fields accordingly.

A found one macro that was close, but it does not automatically make a new tab (I have to do that manually) and it does not refresh the sheet each time I open it, so when I run the macro again I get duplicate entries. I’m not an Excel expert and will never be able to repay and help I get in kind, so I hope one of you experts takes pity on me and can offer some help.

Thanks in advance,

So will you always have the same number of sheets and you'll just be adding entries to them, or will you be needing to create new sheets (markets)?

Thanks for the fast reply!

Occasionally I will need to add a new market(s). But if I have to do that part by hand I can live with that -- I never see myself needing to track more than 50 markets.

Do you think this is do-able?

Originally Posted by StephenR

So will you always have the same number of sheets and you'll just be adding entries to them, or will you be needing to create new sheets (markets)?

See if this starts you off in the right direction. It will check if a sheet exists and if not create it.

Please Login or Register  to view this content.

This worked, but when I run the macro again it still duplicates the data in the older tables. See the attached:

Originally Posted by StephenR

See if this starts you off in the right direction. It will check if a sheet exists and if not create it.

Please Login or Register  to view this content.

OK, so tell me how to tell whether it is a duplicate. Is it the same name or same phone number or both? An alternative would be to delete everything each time. If you've got hundreds of records that might be inefficient.

Stephen;

Again I really appreciate you helping me out. If you open up the second attachment you will see I added Terre Haute as a market. When I ran the macro to make the Terre Haute Tab it re-ran the tabs for Chicago, Detroit, Davenport and Green Bay - thus doubling the same entries. You can see them for your self. In fact each time the macro is re-run, it keeps duplicating entries. We're using this a a "phone book" (if you will) so we only need one unique entry per market.

Boy, this is tough to explain... I hope this makes sense.

Regards,

Originally Posted by StephenR

OK, so tell me how to tell whether it is a duplicate. Is it the same name or same phone number or both? An alternative would be to delete everything each time. If you've got hundreds of records that might be inefficient.

Hi, Try this:-
The code will insert any sheet that does not exist in sheet "Master" Column "A".
The code will then delete values in Existing sheets columns "A to C" then re-insert all values from Sheet "Master" in related sheets, columns "A to C".

Please Login or Register  to view this content.

Regards Mick

Yes exactly!

Originally Posted by StephenR

Yes, I understand the issue. What I was asking was how to tell Excel that it already has an entry. I think from your last post you're saying we need to compare the name and the phone number and if they're both the same then don't add the row. Does that sound right?

OK, I have edited the code in 6.05 post which simply deletes any existing entries - see how that goes.

This is nearly perfect!

How do I get the column titles "Market", "Name" and "Phone to populate on the created tabs?

Can I assume that is I add columns like fax, address, etc, that the marco won't need to be tweaked?

Originally Posted by StephenR

OK, I have edited the code in 6.05 post which simply deletes any existing entries - see how that goes.

Try this. You can see the headings line so add entries and extend range as necessary.

Please Login or Register  to view this content.

This is great. Any way I can get the macro to run itself with each data change or on each open/close of the document?