Summary sheet (Vlookup)

Hello,

I have five club register workbooks populated by staff and we now need to keep a "summary" workbook with all the information collated together.
Each of the five can be a variety of row lengths as they are updated daily.
How can i have one summary workbook pulling through the information from all.

Example club workbook.
Club house 1:
Student ID, Student Name, Activity, Date.

It is a literally "copy and paste" of all five workbook sheets but on an updated/daily basis.

Thank you.

Have you tried Pivot Table?

Can you upload a sample file here?

Are the 5 sheets kept in the same folder somewhere?
Are other workbooks kept in that folder - if so, is there a special file name format that will enable code to recognize the 5 sheets?

In the summary workbook, what is the name of the tab where the summary should appear? Also, where (what address) on that sheet should the summary start?

In the 5 sheets - what tab name acts as the source for the summary (where are we pulling from)?
In the 5 sheets - where is the data located and is the layout identical between them?

Are you talking about 5 workbooks, each with one sheet; or one workbook (file) with 5 sheets in the one file?

Is the layout IDENTICAL in all 5 sheets/files?

I see that the OP has edited her first post to answer the question...

Damn!! I have a solution that will combine multiple sheets into one sheet and another solution that combines multiple files into multiple sheets in one file. So, now I need to get a combination of the two. I'm not sure if my VBA is up to that!! We'll see...

OK. Back again. Put the three files (Tom, **** and Harry) in a folder called Raw on your desktop. In each file there is a Sheet 1, from which the code will import the data. The presence of other sheets in each of these workbooks will not interfere with the merge process. There is an extra sheet of junk in one of the files.

The last sheet (Merge.xlsm) can be put anywhere. Open and enable macros. The code (in module 1 - ALT F11) is as follows:

Please Login or Register  to view this content.

For the purposes of this demo, change the path to mirror EXACTLY the path to "Raw" on your PC. On mine it's C:\Users\Windows 8\Desktop\Raw On yours it will probably be different. Currently it is set to import data from Sheet 1 A2 of each file in raw out to column Z. Should you need more columns (and in this case, you don't...), adjust the end column appropriately. I have put the common column headers on the top of the consolidated sheet. Then, hit the "Import Data" button. Job done.

Ha ha ha. In English, there's a term for "the common man". It's (reproduced here without any spaces between the three names) TomDickandHarry. I used these three names as my example files. It seems that I have offended the Excel Forum censor. The universally accepted four-letter contraction of the name Richard (D-i-c-k) is clearly unacceptable and it has been replaced by ****!!!

May the Good Lord preserve us from such silly political correctness.