Transfer rows of data from multiple workbooks to master workbook based on value in row

Hello all,

I'm a bit of a self taught excel user, so my advanced knowledge is a bit limited. I have created timesheets for employees that work in our shop. Our company manufactures products for different industries, such as mining, wind power generation, general industrial applications, and so forth. I modified some time sheet templates I found for excel to accommodate our company's actions. Each employee has their own workbook, in which the months are separated into different worksheets. Each sheet is divided further into weeks and in each weekly section the areas of information are divided as follows:

A / B / C / D / E / F / G / H / I / J / K / L / M
Work Sector / Workshop or Fieldservice / Scope of Work / Job # / Reg or OT / Mon / Tue / Wed / Thu / Fri / Sat / Sun / Total

There are 7 workbooks (one for each employee), each with 12 sheets (one for each month). I want to create a master sheet that will pull information from everyone's timesheet if they worked on a particular job. In other words, I would like to type a job number into a cell, then have excel look through everyone's timesheets and pull over only the rows of information that contain that job number.

I have been searching and could not find any advice on this subject. Please let me know if this is possible. Thank you in advance for any help.

Originally Posted by mikeTRON

Also dont bother typing in some explanation of your dataset, always post a sample workbook.

As for your particular issues, it seems you want to pull all the data in, append it into ONE data source then have some sort of reporting tab that references the data source.
Is this correct?

Are all of the files exactly the same, are all of the tabs exactly the same?

Thank you for the reply Mike,

My apologies, I have attached an example.

As you can see, I would like to pull the information from rows only if the row contains a particular job # that can be input to a cell in a master file. So we could have 5 different employees working on the same job at different dates and putting different amount of hours into the job. Each employee's spreadsheet is exactly the same format with the tables in exactly the same format. So once again, I would like to have excel look through each employee's sheet and each tab (save the last tab since it is only a yearly overview for each employee), and pull over the rows of information that contain a specific job number that you can input into the master sheet. All the rows should populate in the master sheet so that total hours for the job and further division of those hours can be done from the master file. Let me know if this answers your questions. Thank you again sir.


Joe Smith.xlsx

Still looking for advise on this subject. I have very limited knowledge of VBA, any suggestions would help immensely. Thanks again.

If you look at each month, there are different sections for each week. In each weekly section, they fill out a row of information that pertains to a job that they are working on. So for example, look at March week 10. In my master spreadsheet I would type in the job # L40009 and it would pull over the rows of information in Joe's spreadsheet that contain that job number. I would like it to pull over all the columns between A and M for each row containing this job #. So in March week 10 alone, the master sheet would pull over rows 47, 48, 49, 52, and 53. But in the grand scheme of things, the program would look through every week of every month in everyone's timesheet and pull over the rows of information. Let me know if this makes sense or if I confused things further.

i Already Posted The Sample Previously. Its Called JohnSmith

Sorry, JoeSmith.xlsx

The data I want pulled I clarified another earlier post. Consider the master workbook a blank workbook. I want to enter a job number in cell A1 in the master workbook, for this example we'll use L40009 again. Once I enter that job number in cell A1, I would like excel to search for that job # in each employee's workbook for each month. So, let's say that the first employee that excel will look through is Joe Smith. Excel should start in the January tab and start looking through the job# column (column D). Once it finds L40009 (in this example the first instance would be D79 in the January tab), I want it to copy all the information in A79 to M79 from the January tab in Joe's worksheet. It will then list this information in the master workbook, honestly anywhere, let's say it lists it in cells A3 to M3 in the master workbook. So once again, excel finds job number L40009 in Joe Smith's workbook in January D79, and pulls the information from A79-M79, and copies it to the master sheet in A3-M3. After this it will continue to search through Joe's workbook, the next instance will be in January D80. So it will pull information from A80-M80, and copy it into the master sheet right below the first set of information pulled, so it will copy information from A80-M80, and put it in the master book in A4-M4. This program should continue search through all the months in Joe's sheet, and then move onto the next employee and do the same. After it looks through all the employees' workbooks, the program is done. Let me know if this is clearer or if more information is necessary