VBA to automatically update and copy data to other sheets

[kary4567]

Hi everyone, I am new here so I hope I don't mess this up. I am looking to do one of two things, preferably transfer data from 1 tab to multiple tabs.
I have a table on a sheet titled all data-enter information here. There are headings starting at B3 and going to N3. It starts at B3 so that I can have an index link on top and column A may eventually have numbers.

A B C D E F G H I J K L M N
Blank Name School KTF/ESP Home Address City State Zip Cell Phone Home Phone Home Email Work Email DOB Sick Bank Y/N

I would like to copy the data to individual tabs based on column C (school), and have the data update when changes are made to the sheet all data-enter information here. I already have the tabs set up, but could remove them if that is the easier route to take. The tabs are in order (KHS, JWB, MCM, Chambers, Crosby, Edson, GW, JFK, Myer)

I have tried multiple sites, youtube videos etc and all the codes are way too complicated and give me errors when I change where it should be changed. I KNOW part of it is that I want the data to start in column B, row 4 (headings in B3) but as I said I need to have the link to return to index.

If it is easier I could paste the data to the individual tabs and would just need a VBA to copy and update each tab to the master tab (all data-enter information here)

I know providing the file might be helpful but there is a ton of personal information here.

Thank you in advance for ANY help I can get.

[Mumps1]

Have a look at the attached file. You will see that I have added a column (column o) that contains a drop down list with one word: "Done". Enter all the data in any particular row in columns B to N and when you are finished entering the data in that row, just select "Done" in column O. That row will be automatically copied to the appropriate sheet.

[xladept]

Hi Kary,

This seems to work:

Please Login or Register  to view this content.

Guessing that JFK is Kennedy:

Please Login or Register  to view this content.

But Still there is no Kingston Catholic sheet

[kary4567]

Mumps1-

This is perfect, I can look and see how you did it but would you mind explaining it so I can use it again?

Thank you so much!!!!!!!!!!!!!!!!!!!!!

[kary4567]

xladept
This is also perfect! Yes Kennedy is JFK, my fault! Also not sure how to handle the Kingston Catholic one yet, it's an odd ball

I truly appreciate the answers and the help from both of you!!

[Mumps1]

You are very welcome. The macro is a WorkSheet_Change event and this type of macro is placed in the worksheet code module not a regular module. You can see the macro by right clicking on the tab name and then clicking 'View Code'. These event macro are triggered automatically, in this case by any change in the sheet in column O. When you select "Done" in column O, that is a change in the cell and so the macro is triggered to copy the range from columns A to P in that row to column B in the sheet corresponding to the name in column C of the "All data-enter information here" sheet. I hope that makes sense.

[xladept]

You're welcome and thanks for the rep!

P.S.

Give the Kingston Catholic its own sheet

BTW - Unlike Mumps, requiring an entry in O column, my routine will trigger on any entry where there is a value in Column C.

[kary4567]

xladept,

Obviously I sent a test file, so I was trying to paste your codes into my original file and they aren't working like they do in your file. My issue is that I would love to use the one you sent back to me but I did a LOT of work in my original file with all the data. I can copy and paste the data but at this point I did drop downs on the 2nd tab to change schools/employees/union reps etc and would hate to have to duplicate all of that work. Am I doing something wrong when pasting your codes or does it have to do with the tabs I have? I have an index tab, a school information tab (with drop downs), the all data tab, all the schools and two additional end tabs that hold all the information for the drop downs.
BTW I did create a tab for Kingston Catholic and changed the tab to JFK. (I see the code included both of those so THANK YOU again for that)
My plan is to use both of your options and present two different files. At this point there are a ton of lines that still have to be added, as a school district there are at least 1500 employees...

After this I should be done lol

[xladept]

Hi Kary,

Thanks for the rep!

As long as this code:

Please Login or Register  to view this content.

is in the "all data" module and column 3 has the sheet names then it should work?

Can you post a facsimile of what doesn't work?

[kary4567]

Set wd = Sheets("All data-enter information here")

[xladept]

The sheet name must be exact; for the sample that name was "All data-enter information here".

If the name is just "All data" (and the case matters) Then just delete the rest

[kary4567]

That was totally it. I don't think there is an icon for a duh moment, but that's what that was. I literally looked at it forever and didnt even notice. Again thank you so much!!!

[kary4567]

Can I mention again that this is sooooo fabulous!!! I can't thank you both enough!!!

[kary4567]

If I want to do the same as above but with different data should I start a new thread? I thought I could change the code to do this but my understanding isnt as great as I want it to be!

[xladept]

You've not just changed the data but also the configuration i.e. Columns and Rows. f.r.

This now matches the sheet name field:

Please Login or Register  to view this content.

[kary4567]

Thank you, I didnt even think about that but yes there is less data I cant give reputation points but I truly appreciate all of your help!!

[xladept]

You're welcome and thanks for the rep!

(You did give me rep)