macro for multiple spreadsheets into one

**adlamixion** · 06-26-2008, 05:44 PM

I have hundreds/thousands of spreadsheets. For each sheet I have to take 6 boxes, each covering 2 horizontal cells each (two cells merged) that cover positions C12-N12. These 6 boxes have to be placed horizontally in one large "summary spreadsheet". The boxes from the first page need to be placed in the spaces AA25-AL25 in the summary sheet, the second in AA26-AL26, ect.

There is a condition for the content in each of the boxes once placed in the summary spread sheet: For each of the individual sheets the information in each box corresponds to information in boxes below them ranging from C26-N26. So C12-D12 box corresponds to the box C26-D26. If the lower boxes contain information of any kind (not blank), the upper boxes should simply post their information in the summary sheet. If the lower boxes contain no information (blank) the upper boxes should contain no information in the summary sheet.

I hope I explained this well enough, I understand it's quite a complicated job.

I'll appreciate any idea (preferably code) to do this- however I do not know visual basic!

Thanks a ton!!

Adam

**davesexcel** · 06-26-2008, 07:35 PM

Hi Adam,
It is a simple process to loop through the worksheets...
If you could attach a sample workbook with just a few worksheets, and include a summary worksheet with the desired results, that would be a great help

Click on the paper clip icon to attach a file, if the sample workbook is larger that 100kb,you will have to zip the workbook first. The simplest way to do that is to got to "my computer" find your workbook, right click on it, and select send to, compressed folder, you will then have a compressed copy of the workbook.

**adlamixion** · 06-27-2008, 10:31 AM

I've attached 3 sample sheets and a summary sheet; colour has been added to make viewing easier.

Ideally I'd like all the sheets to work with a single macro, just going down each file in a folder- or something similar to that- if that's at all possible.

Cheers :-),

Adam

**davesexcel** · 06-27-2008, 08:04 PM

Originally Posted by adlamixion

I've attached 3 sample sheets and a summary sheet; colour has been added to make viewing easier.

Ideally I'd like all the sheets to work with a single macro, just going down each file in a folder- or something similar to that- if that's at all possible.

Cheers :-),

Adam

Hi Adam,
Based on the samples you have attached, here is a working example of retrieving data from workbooks in a folder.
You will notice the "summary sheet" is in a sub-folder so the code will not go in it to retrieve data.
Check the code and make sure the folder address matches the forlder you need, run the code in the "summary sheet" workbook... Good Luck

AdamsFolder.zip

**adlamixion** · 06-30-2008, 09:48 AM

Forgive my ignorance, but I can't seem to be able to run the program, and when I look in the VBA editor I can't find any code- How am I supposed to do either?

Thanks so far!

**davesexcel** · 07-01-2008, 09:02 AM

Originally Posted by adlamixion

Forgive my ignorance, but I can't seem to be able to run the program, and when I look in the VBA editor I can't find any code- How am I supposed to do either?

Thanks so far!

I will have to do it again.

**davesexcel** · 07-03-2008, 07:30 AM

Hi Adam,
Based on the samples you have attached, here is a working example of retrieving data from workbooks in a folder.
You will notice the "summary sheet" is in a sub-folder so the code will not go in it to retrieve data.
Check the code and make sure the folder address matches the forlder you need, run the code in the "summary sheet" workbook... Good Luck

Attachment now has the code in it
http://www.excelforum.com/attachment...1&d=1215084362