How to automatically populate worksheets from criteria in Master List?

[dm6]

Hi all,

What I'm trying to do is populate different group worksheets based on the master list. The worksheets are subgroups of the main list. An employee can be a member of more than 1 group. Basically if the employee has a percentage to a Group they need to have an entry in the associated worksheet. I've tried a bunch of filtering options, but the best would be to automatically populate the group worksheet based on having a percentage in the master list.

Obviously my real payroll list is much more extensive than this, but this is a simpler example that addresses my problem. I've been working on this and searching for quite awhile, found some similar problems but nothing that I can make work.

Thanks for anything you can provide, whether it is a different direction or some VBA code.


dm6

[JBeaucaire]

Let's use VBA to update each GROUP sheet each time you look at it.

On the attached workbook right-click the GROUP A sheet tab and select VIEW CODE, you'll find this event code:

Please Login or Register  to view this content.

Now compare that to the event code in the other two sheet modules, you'll see the parts highlighted in red are the only difference so it copies the correct columns over.

Also, on the Master Sheet, I removed the color banding you had manually applied and replaced with a conditional formatting that colors the EVEN rows blue. This way when it copies random rows to the other sheets the color banding works on the other sheets, too.

[dm6]

Great, that works great. One last question, let's say that Group A changes periodically. Is there an easy way to specify the range D:I without going into the code everytime? I'll be dealing with around 15 groups and the funds change every few months on the master list.

-dm6

[JBeaucaire]

Hm, ok, another change is needed first. On the Master List, all the cells that make up the columns for a Group need to be merged on row 4. So put Group A in D4, highlight D4:I4 and click on the Merge Selection to make it one big cell. This will act as an anchor for each group.

Now, any sheets/groups you ADD, make sure they are named exactly the same thing, Group A, for instance.

Now delete all the macros in the Sheet modules.

Now put this ONE macro into the ThisWorkbook module, it will work automatically from there on any sheet called Group ??.

Please Login or Register  to view this content.

[JBeaucaire]

Thanks for the feedback. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.