Strange, Irregular Merge of Cells
 

I have inherited an Excel file that currently has like 50 sheets, each one
lists the files contained in a box referenced by the sheet title. Each file
name is on a different row (all in column 1) on the sheet.

I need to move the data to another file that lists all the files (contents)
in one cell, separated by "soft" returns (alt-enter). Some of these boxes
hold 2 files, some 100.

I've tried a couple macros I found discussed, but none of them seem to be
able to merge the row contents (the "file names") so I can cut-and-paste to
my final location.

Can anyone help?


For clarity:
Excel File #1
Sheet #1 (named "Box 1")
Cell (R2, C1) contents: Company #1
Cell (R3, C1) contents: Company #2
Cell (R4, C1) contents: Company #3
Sheet #2 (named "Box 2")
Cell (R1, C1) contents: Company #4
Cell (R2, C1) contents: Company #5

NEEDS TO BE:
Excel File #2
Sheet #1
Cell (R1, C1) contents: Company #1(soft return)Company #2(soft
return)Company #3
Cell (R1, C1) contents: Company #4(soft return)Company #5

--
dhunton