Transfer Data from one workbook to worksheets in a closed workbook by criteria

Hello ExcelForum,

This is a cross-post from here: http://chandoo.org/forums/topic/exce...s=1#post-57527

Apparently, I still haven't received any response since yesterday so I am trying my luck on this forum instead.
May I please ask for your assistance on how I could achieve this report process:
1. I have a workbook (WB1)with one sheet with various data (Staff Name, Project Worked, Date, Hours Spent)
2. I need to transfer some data from WB1 to a closed workbook (WB2) from my desktop into different worksheets.
3. Criteria to transfer WB1 to different sheets into WB2 is by Staff Name.
4. WB1 has a column with Staff Names.
5. WB2 has multiple worksheets labeled with the same Staff Names as WB1.
6. Each sheet of WB2 has 2 tables inside:
6A. Table 1 data will come from WB1 table
6B. Table 2 data is like a calendar, if WB1 table marks a date as holiday, it will mark the same date as HOLIDAY in WB2 Table 2.

I have explained this much to my capability and hoping for a favorable response to get me started. Thank you for any advise you can give.

Sample Files:
WB1: https://www.dropbox.com/s/44w7ur9wqaf0kfk/WB1.xlsx
WB2: https://www.dropbox.com/s/dcn1yetdzyk4tql/WB2.xlsx
or
WB1.xlsx
WB2.xlsx

Kind regards and thank you for your help.
Joyce

Hello Arlette,
I'm so thrilled somebody noticed me

1.) How would you want the holiday to be marked in WB2? I would have the holidays marked as "HOL" but would also like to have it editable on my end because it won't just be holidays that I will track here, I will also have to track the different kind of filed leaves. But one sample (which is the holiday) is enough, I think I may be able to replicate the code to another type, thanks!

1.1) Is the 2nd table columns F to AK? Just from my sample - yes, but in the real tracker that I would use where I would somehow update the code given to me, the calendar will be placed from H21 to AM33.

2.) Once the update to WB2 is done and you have fresh data in WB1, do you want the existing data in WB2 to be cleared and fresh data to be input starting from row 2 of WB1? WB1 will be updated on a weekly basis, which should be reflected to WB2 every week as well, so the new data for the week should be appended to the last entry of WB2 always. Both workbooks will be emptied at the start of a new month, so I will just save as the file and manually delete the entries, so far that's what I have imagined. But if you can suggest a better way, I'd be more than happy to accept!

Thank you so much.

Kind regards,
Joyce

Hello Arlette,
Well WB1 will depend on the dates if there's a new entry.
But I don't want to make my life miserable if I have to update the codes someday, nor would I want to make it difficult for anyone helping me build up this file as well.
So the easiest and most doable that I could think of is since this is an extracted file from a system we use for attendance, maybe it will be easier for us to generate a new WB1 file every week, in which we should exclude the dates already available from the previous week's WB1 and WB2 files.
Kind regards,
Joyce