Adding contents of multiple files and putting them into one single file

Originally Posted by EsKay!

I have 46 files with identical column headings and rows and I have to summarise the contents of each file into one file. Is there a way to merge these files automatically in one file? What I want is the data from the same cell addresses in each file to be added and put into the same cell addresses of a single new file. I have tried linking the files, but the number of rows and columns run into hundreds.

Can this be done? I will appreciate any help.

Hi,
Here is a similar thread:
http://www.excelforum.com/showthread.php?t=589590

Originally Posted by EsKay!

I will appreciate if you could answer the following questions:

1. What does "InvoiceTest" in Workbooks("InvoiceTest") stand for? Is it the file name?

2. What should I type in = Range ("M3") if the range in each of the worksheets is A1 to HC1150?

Please place this code in a workbook "InvoiceTest"(for this example)that is not in the folder to extract the info,
This example searches a folder called C:\Invoices\ you will have to change it for the folder you require.
Check to make sure the sheet names are correct as well...

Please Login or Register  to view this content.

Thanks. I have made the changes and tried to run the Macro, but it does not return anything in the file InvoiceTest.xls. I do not get any error messages either. So I presume the Macro does run but there is something else that I am doing wrong. The InvoiceTest file is saved in G: Drive and the data files are in H: drive.

This is what I have done:

Please Login or Register  to view this content.

Range("A1:HC1150").Copy

Do you actually have data in Column A?

In plain English....

What this code does is,
Opens a workbook in the folder, copies A1:HC1150
Then it goes to Workbooks("InvoiceTest").Sheets("Sheet1"), finds the last entry in column A moves down 1 row and pastes what has been copied, it then closes the workbook, and opens the next workbook,

if there is no data in column A, you will always be pasting to the same spot, is this the case?

Thanks. Column A has "Heading" and the "Details" (Revenue, COS, etc.). The actual numbers against the details, which are to be added start from Column C.

ColumnA ColumnC ColumnD


Revenue $$$ $$$
COS $$$ $$$


and so on


Also Between, A1 and HC1150, there are many blank cells intermittantly.

Originally Posted by EsKay!

Thanks. Column A has "Heading" and the "Details" (Revenue, COS, etc.). The actual numbers against the details, which are to be added start from Column C.

ColumnA ColumnB ColumnC


Revenue $$$ $$$
COS $$$ $$$


and so on

Hi EsKay,

So do you want to copy c1:HC1150 then? and paste to column C?

Hi,
I have never used consolidate before, and have only tested it out on a single workbook, hopefully somebody else knows the answer better about it or even if it is possible,
Check out help with excel consolidate, to get to it go to ,data,consolidate, lots of info on it as well on the web

Originally Posted by EsKay!

Thanks for your help so far. I am wondering what does the Macro that you have suggested do? It seems to run but nothing happens.

See post #7 for that answer,
Did you check out consolidate?