Transposing Collection of Cells from an Open Workbook

Here's the backstory: I've been enjoying VBA's versatility in Excel 2007, but I'm getting stuck trying to reference a range of cells from another workbook. More explicitly, I'd like to create a columnar list starting in cell I12 in ThisWorkbook using the For Each Next process, which will grab the cell values (of varying quantity) from the first row of the sheet (sheet variable is CompShtName) in the OldFile workbook. Here is the excerpt of code I've written:

CompShtName = Range("E12").Value 
For Each Cell In OldFile.Sheets(CompShtName).Range("A1", Range("A1").End(xlRight))  **<---Run-time error 1004**  
    Range("I" & 12 + i).Value = Cell.Value  
    i = i + 1  
Next

I've declared and defined the variables where: OldFile = file address for the old file, CompShtName = name of sheet in OldFile, and i = counter. When I try to run the code, I get run-time error 1004 in the line containing the arrow.

I thought maybe the problem lay in pulling from another book. But if I change the range to something more simple, like Range("A1:A5"), it works. I even tried removing OldFile.Sheets(CompShtName). and letting it run using the active ThisWorkbook, but that works, too. So the problem must be from try to create an unwieldy collection.

UPDATE

I really like @Zemens approach using transposition instead of a loop. I also made sure to properly qualify my range, using the suggestion from @mehow. Unfortunately, I'm still getting a 1004 error. Here is the updated code exceprt:

Dim OldSht As Worksheet
Dim EndOldHeader As Range
Set OldSht = OldFile.Sheets(CompShtName)
Set EndOldHeader = OldSht.Cells(1, OldSht.Cells(1, Columns.Count).End(xlToRight)) Compare.Range("I12").Resize(EndOldHeader.Columns.Count, 1).Value = Application.Transpose(EndOldHeader.Value)

The error occurs in the Set EndOldHeader line.

Solution

The unqualified Range object Range("A1").End(xlRight) is assumed always to refer to the ActiveWorkbook. You need to fully qualify it, per @mehow's comment.

The 1004 error raises because it is not possible to construct a Range object that spans multiple workbooks :)

I find it better when working with large constructs like this to create object variables to represent them. They are easier to read, update, maintain, and debug in the future.

However, you can do this without a loop structure. It should be faster, although might not be noticeably faster.

Dim oldSheet as Worksheet
Dim oldRange as Range
Dim cl as Range
Set oldSheet = OldFile.Sheets(CompShtName)
Set oldRange = oldSheet.Cells(1, oldSheet.Cells(1, Columns.Count).End(xlToRight))

ActiveSheet.Range("I12").Resize(oldRange.Columns.Count,1).Value = Application.Transpose(oldRange.Value)

Or, to do in the loop like:

For each cl in oldRange.Cells
    ActiveSheet.Range("I" & 12 + i).Value = cl.Value  
    i = i + 1  
Next