Automatic Data Transfer Between Worksheets

Is the data in each workbook in exactly the same location?

What is the filename of your summary workbook?

What is the full path where the "single worksheet Excel files with data from a hundred people" exist or are stored?

Is the data in each workbook in the same range? Or, what is the maximum range that the data can be in?

Is the data in each workbook in the same sheetname?

Have a great day,
Stan

Thank you for the responses, I knew I must have missed some key points! I'll try and address the questions as they came in:

(1) The data in each worksheet is in the exact same locations every time, with a cell range of A2:O64, though some of the data is text strings, and some is numeric.

(2) Summary workbook filename: "2006 Summarized Results.xls"

(3) The filepath where all the individual workbooks are stored is: "D:\0m1739\Miscellaneous\2006 Entries"

(4) The data in each individual workbook is in the same cell range, specifically A2:O64 (small range, I know). Some is text string, some is numeric, but the numbers don't go below zero or above the hundreds place.

(5) The individual workbooks I would receive would not have the same name as any other individual workbook, but some workbooks may have multiple worksheets, which obviously would not have the same name either.

As an example of what I'm looking for, if I receive 25 worksheets in 20 workbooks and they all have a piece of data in cell B4, I would want to put all 25 individual B4 values across a single row in the summary worksheet and be able to do that for a bunch of other pieces of information in other cells in the individual worksheets and/or workbooks.

Thanks again guys! If this works, you'll save me immesaurable time. I apologize for the slow response. I sent it right before I left work and didn't get a chance to take a look at the board again until this morning when I came in.

Can anyone help me here? I'm desperate and would rather not spend the weekend manually typing in all of the results from the individual worksheets into the summary sheet. Thanks.

Initially you said you received "single worksheet Excel files", your later post mentions "I receive 25 worksheets in 20 workbooks". That indicates that some files will have multiple worksheets.
I have code that will cycle through all files in a specified folder, but it will only work with a single worksheet.
Can you elaborate on exactly what the files will contain? Worksheet names, etc.

If need be, I can create a separate file for those employees who have multiple sheets in their submitted file because they would not be connected in any way. That would be no problem to do compared to the work saved by your code.

Each file I receive now has one or more tabs and each tab is labeled with the employee's last name and then a number, i.e. Smith 1, Smith 2 (if more than one tab), etc. There are text string and numeric data in each employee's file and they are always in the same cells in each employee's worksheets (company standard file). The data is in a relatively small cell range (A2:O64), but is scattered in that range.

Ideally, the summary sheet would include one column per employee worksheet, so if Smith has two worksheets (which I can split into two files), then he would have two columns in the summary sheet, Smith 1 and Smith 2. If I have 24 employees, I would have 24 employee names in C1:Z1 (A & B reserved for descriptors) and would also like to be able to pull the employee name from the file name and put it in C1:Z1, if possible. After that, all employees have the same type of text string (thought not identical) in cell B4 in their individual files and I would like to take that informatoin and display it in cells C2:Z2, corresponding to the proper name in the row above. The next text string in the employee files is in B8 and I would want to display them in C3:Z3 and so on.

Before, I was doing a lot of cutting and pasting and it would take a long time. If your code can help accomplish even a portion of what I'm looking for, it would be an immense help. Thank you!

P.S. - Sorry for the long post.

Here is some code I put together that should help you. This will copy the hardcoded range of the "active" sheet in each file in your directory, (folder) to the end of existing data in the "Summary" sheet.

It would help if the data you wanted transfered to the Summary sheet was in the same named Worksheet in each file. Copying from the "active" sheet like I did in my code can be dangerous. It will copy from what ever sheet was active the last time the file was saved. If there is only a single sheet, no problem. If the active sheet is something else, whatever is in the hardcoded range will be copied.
If you could arrange to have all data sheets named the same, you could change the code to copy just from that worksheet.

John,

Thank you very much for the code post. Unfortunately, I don't have all the spreadsheets at home with me this weekend, so I'll have to take a look at applying it on Monday. Thanks again!!!