VBA code to match and mark data from multiple sheets into a master sheet.

Hi everyone,

I'm totally new working with Excel and so my question might have an easy fix solution for you Excel masters out there

I have this company Personnel file (Master sheet) and multiple training attendance sheets. What I would like to do is to compare data from multiple attendance sheets to data in a Master sheet using VBA.

Here an example of my Master sheet named "Master":

Please Login or Register  to view this content.

Here an example of my first Training attendance sheets named "Sep24-2015@0900-1000":

Please Login or Register  to view this content.

Here an example of my second Training attendance sheets named "Sep24-2015@1030-1130":

Please Login or Register  to view this content.

I would like the VBA code to:
1. Loop trough the first Training Attendance sheet
2. Check if the "Signature" column (D) is marked
3. Look at the ID number in Column B and find the matching ID number in the Master sheet.
4. Then go to the correct column (which has the same name as the Attendance sheet) and mark the cell in the correct column.
5. Go to the next Training attendance sheet and repeat steps 1-5.

So in the end my Master sheet will look something like this:

Please Login or Register  to view this content.

I've been struggling with this problem for two weeks now and have no idea how to write the proper VBA code instead of a long senseless VBA code full of error.

Many thanks in advance.
Mike

You can do it using Formulas aswell..

Put this in E2 and drag down and across

=IFERROR(VLOOKUP($B2,INDIRECT("'"&E$1&"'!$B:$D"),3,0),"")

Then I use Conditional Formatting to format zeros with white font..

VF Formula - =E2=0 (Format with White Font)

See the files attached (With and without VBA).

Also here is the VBA

Please Login or Register  to view this content.

Make sure the Headings are exactly matching to the sheet name..

No problem..

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