Copy and paste cells dynamically to a range in a series of named Worksheets.

All, need your help, as searched high and low, and although I found a few similar posts, I simply can't figure this out!!!

So here goes.........

I have a Summary worksheet which details a list of people in column C.

In my sample worksheet there are two other worksheets with names that are exactly the same as cells e2 and i2 of the summary.

An equation outputs information to columns H and L on the summary sheet for each person.

I wish to copy these outputs to the relevant sheets, so that all the people (Columns H or I) participating in the event (denoted by a tick in either columns E or I) will be listed alphabetically in the sheet of that name, beginning in c6.

I would really like this to be dynamic, and controlled by the ticks in columns E and / or I, which are controlled by a worksheet before double click event.

I hope this make sense, and attach a demo file which shows the desired output.

Thanks for Looking,

Skyping

Just a word, if you do order things alphabetically, then (1) Derek goes before Chris, because of the (. Do you really want it alphabetically or by the name of the player?

BTW alphabetically would be simpler in the end, when you start having doubles for example. So for now I'll do it alphabetically...

Sorry for the delay.

Please Login or Register  to view this content.

It seems to work. Test.xlsm

Keep in mind that event macros can eventually lead to your computer running slower. Macros working automatically might seem cool, but manually is more appropiate in many situations. Just because it can be done doesn't mean its always the best approach. That said, in this case, with such small tasks, it might not make you too slow.

Its no problem. As long as your names follow some sort of pattern, we can try to find a way to loop your code through all the sheets. How will they be? Event3, Event4, Event5, and so on?

Will the name of the sheets be the same of the name of the events listed? Will they be different but similar?

If this is not true, maybe we can loop through sheets using the sheets order instead of the sheets names, but to do this we need the order of the events in sheet "Summary" (how they are ordered in columns, like in your example its first event1 then event2) to match the order of the corresponding sheet in the workbook (in your example, first the event1 sheet, then the event2 sheet). We need some sort of pattern for the loop to follow, if it is not sheets name maybe we can do it with sheets order.

I'll be waiting for your reply.

I have here two sets of codes that can work.

This first one requires for the name of the events listed on row 2 of the summary sheet to match those of the event's corresponding sheet. By match I mean they have to be exactly the same.

Please Login or Register  to view this content.

This other code, instead, requires that the order of the events on the Summary sheet matches the order in which the events are listed on the Events sheet. Again, by match I mean they have to be exactly the same. Also, the event's corresponding sheet name will have to be the same as the event name listed on the Events sheet.

Please Login or Register  to view this content.

....which I can just about follow!!!

To help you on that, I have added some comments to the code, that indicate what it's doing on that part. If you need anything else explained, don't hesitate to ask. I think that the comments should help a bit anyway, but I might have forgotten to explain something. You choose which code to use keeping in mind the requirements I explained they have.

I hope that helps.

BTW if the comments seem too much you can delete them. They won't slow down execution tho, when running those lines are simply skipped. Commenting is a great help if for example in the future you start having trouble in some part, and instead of looking at your code and trying to figure out what it was supposed to do, you can read your comments and know. What I mean is that you might start having problems a long time after writing a code, and that's when the comments will be handy, since most likely you'll have forgotten about that piece of code. That's why I always comment my codes. They aren't as long as the ones I wrote up there, but they still point out at what's going on.