summarizing data from multiple workbooks into one workbook

A friend used Excel to generate invoices for wine sales. He now has over 1000 invoices, each is a separate workbook. See attached 1001_IssuesForm.xls and 1002_IssuesForm.xls.

He needs to summarize all these workbooks into one, similar to the salestotal-mod1.xls workbook with each "issue" being a column in the salestotal workbook.

With only a handful, this could be done manually. With over 1000, its murder.

Anyway to simplify/automate this process?

Have tried copying/pasting, but can't figure out how to increase issues file number from 1001 to 1002 to 1003...so on, so that the references will work.

Thanks.

Hi

Had trouble with your salestotal workbook and couldn't download it.

Can you please reattach.

rylo

Rylo:
Thanks. Here is a second copy.
Bruce

Hi

Put the code below into a general module in the salestotal workbook, and save it in the same directory as the invoice spreadsheets.

Please Login or Register  to view this content.

Some notes:
1) You will need to have the invoice numbers in row 1 of salestotal. You currently have 1001, 1002...1007, so extend that across the page.
2) You mention that you have over 1000 invoices. However depending on your version of Excel, you may only have 256 columns. How do you want to handle the overflow.

HTH

rylo

Rylo....thanks. We'll give this a try tonight.
My friend is using 2007...I'm in 2003 Excel.

I think he can split his long list of workbooks by month, and get the count permonth below 256 if necessary.

My other thought was to re-orient the data in the sales summary sheet, switching rows/columns. That way there would be room for, as I recall, about 35000 lines...and we would only use about 20 columns.

Lets see if we can make your code work first....my skills are rusty...then we'll worry about the volume of data to be handled.

Thanks again.
Bruce

rylo:
Thanks... the code works great. Took my rusty brain some time to figure out how/where to put it...but it works fine.

So now I'll ask you for a revision. We like to transpose rows/columns so the report looks more like the attached rough version.

So, instead of stacking the data from each invoice into columns, we want to spread it accross a row..... allowing us to have the full 65000 rows available.

I was trying to rewrite your code..but I'm getting lost. Appears we'd have to change all your outcol to outrows and change the OutSH.Rows to OutSh.Col.

If possible, it will help us deal with the mass of pages he has.

Thanks again.
Bruce Sutherland/Sacramento, California

Bruce

Try this

Please Login or Register  to view this content.

rylo

Rylo..
Thanks again. will give it a try tonight.
Bruce

Rylo....got curious, and tried it now.
Works perfectly.

Now...just hoping his multiple spreadsheets are as perfectly consistent as he says they are....this should work fine.

You've pushed my curiosity....so I'm now trying to figure out what you have done differently to accomplish this magic. Most of the instruction changes make sense...but the OutSH.Range ("A:A"), 0) has me mystified. I'll figure it out eventually.

Thanks again.
Bruce

Bruce

You have only taken part of the command. The whole command is

Please Login or Register  to view this content.

If you look at the MATCH function in raw excel, it is in the form
match(lookup_value, lookup_array, lookup_type).

In the code:
lookup_value: val(left(filess,4)). This takes the 4 leftmost characters from the file name (ie the number component) and converts it from a character string to a number.
lookup_range: outsh.range("A:A") is column A on outsh. This will have all the numbers down the column.
lookup_type: 0 is an exact match.

rylo