Bringing data from mulitple worksheets to a master sheet

Each salesman has an enquiry log / hot prospect sheet that they are responsible for.

At present at the end of the month our receptionist has to consolidate this information manually onto a master sheet.

I would like to create an automatic master sheet that auto updates each time it is opened.

It has to read data from about 13 work books each with varting amounts of date. All works books are formatted identically (or they will be)

I would like the date to be sorted by date.

All the files will be stored in the same directory on our server.

I am unable to attach the spread sheet - I keep getting an error (I have tried .xls & .zip)

Basicaly row 1 is a header
2 down will contain consistent data

Coloumns A-Q are filled -

Status, order date, customer, country, area, sales man, machine, quote price, etc.

It is just data - Each spreadsheet will have no forumla just manually typed info.

If you still need an example can you suggest a free file sharing website I can use?

Yeah don't need the header

By consistent - I mean there will be no blank lines. until the end of the data.

someone sent me this code to accomplish a similar goal. you may find it useful:

assume 3 workbooks called bill, mary and arthur. each has a sheet called "1" [saves my typing!] and each has columns called Person Account Date and Value.

In a fourth workbook use >data >import external data > new database query. select Excel files as the data source. Click on the SQL button and type..

Please Login or Register  to view this content.

Hi royUK,
I open your file, but there don't seems to be any place for me to select files.


Hi durandal05,
Where do I find this

Click on the SQL button and type..

I'm facing the same problem as kgkev. Attached is the objective that I want to acheieve. Combine book1 and book2 to from combined book.

Nearly there now I think - But it only gets the data from one workbook.

I have cheated and stored 3 sample enquiry logs in C:\myfolder

I then run the macro from MASTERSHEET on my desktop.


the problem I can see is the folder will contain excel files that do not contain the correct data and should not be carried across - As the file location is decided by our CRM it is not possible to change.

I think rather than only copying one set of data it is over lapping each set rather than starting at the bottom of the data.

Also how do I make this script run as soon as the Spreadsheet is opened.

I cannot specify a directory - Half of the sales team are out of the office and use a remote update facility in our CRM. They take an offline view of all the data and when they have access to the internet they update the database - this stores all documents in the same place.

The script definatly over rights the previous set of data.

ie

It read Sample 1 and inserts the data - Then it reads sample 2 and inserts the date over the top of sample 1. Sample 3 then overright the previous set of data so all I am left with is a copy of Sample 3 but with 2 headers.

I still haven't managed to get this to work.

Also still require some help with this one if possible.

Hi All,

i'm trying to achieve a similar thing as well, but have the same issue with this code not bringing back the data from all the sheets in the specified folder. The files that are not being copied back seem to have more than one worksheet tab in them....could this be casuing the issue??

My problem is not just that it doesn't bring back the data - I also want it to only bring back data from specified workbooks.

I will have non relevant workbooks in the same folder.