Multiple file merge output incorrect.

[adamcfishman]

I posted this earlier "I am working on a project that will require me to extract data from over 300 workbooks, looking for a simplified solution for extracted the data and compiling onto one new file. All workbooks are located at C:\Documents and Settings\af91468\Desktop\Inspections and I need to extract data from cells B1 through H27 (large range due to merging) I actually need the data from these cells specifically though a range will also work (B1:B6 AND B22:B27) Also some of this data will be the result of a formula. I am attaching a sample of the workbook that I need to extract. Any help would be awesome" and i had thought i found the resolution but the output is not how i want it with the code below and help to change code to reflect the attachmentSample Export.xlsSample Import.xls

Please Login or Register  to view this content.

[arlu1201]

Firstly, please do not create duplicate threads. If you do not receive a reply, you can bump your thread.

Coming to your question, do you want all the data to be stored one below the other in column B of the summary file? Do you want the file name to also show?

[adamcfishman]

Appoligies, thought I had figured it out but was unsucessful. No need for file name. What i would like to see is the results by row. Like this:
Name Number Region Divison Area Start Date PL TE FC IM Comp Overall
Sample 1 1 1 1 1 1 1 1 1 1
sample 2 2 2 2 2 2 2 2 2 2 2

[arlu1201]

You want the data to be consolidated starting in column A or column B of your master file?

[adamcfishman]

You want the data to be consolidated starting in column A or column B of your master file?

Looking to start in Column A

[AB33]

I have tested this code on PC and works. The reason why you are having an issue is may be due to merge cells. You need to unmerge them.

Please Login or Register  to view this content.

[adamcfishman]

Works perfect... Another question for you is with this same code can i add additional cells that are not in a range such as b1:b27 and f43 and l46 etc... Also is there a code to unmerge the cells in all of the files?

Thank you again, you have been a lifesaver...

[AB33]

Yes, you can, need to adjust, this line.

Please Login or Register  to view this content.

There are many ways of doing it. You can loop through certain columns(Although it may slow down the speed)
If you have a fixed range to copy, you can use for example Union

Please Login or Register  to view this content.

It all depends on the format of your data.
To unmerge cells

Please Login or Register  to view this content.

[adamcfishman]

Where would i add the code for unmergeing?

[AB33]

Try to change the line

Please Login or Register  to view this content.

[adamcfishman]

I modified this:

Please Login or Register  to view this content.

It does not seem to be unmerging files as results for only b1:b6 are displayed and all results are in column A
File 1
b1
b2
b3
etc.
file 2
b1
b2
b3

should be

File 1 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
File 2 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
File 3 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
etc through file 426

[AB33]

adam,
Please use code tags with your code.
If there is nothing to unmerge, this will not affect running the code.
The reason for including this line is in case you have merged cells, the code may give you an error, so the code unmerges the cells before it is looping through the range, but if there are not merge cells, the code works as intended.

As to this line

Please Login or Register  to view this content.

Ohm!!
I gave you just an example. It will not work until you get the lower and upper boundary right as the code, which was written by Ron Deburin, uses an array to copy back the range.
I suggest you to use similar code, which does not have array. A code which gives you flexibility and you can also easily amend it yourself.