reduce processing time for Excel Copy and Paste across Workbooks with Transpose?

[mg1313]

Hi all,

I'm working on a program that automatically propagates data from multiple workbooks into a single workbook, but because the data has to be transposed, I have had to use copy and PasteSpecial. The processing time is huge, especially since I'm going across workbooks. I'm wondering if there's a more efficient way to do this.

My thought is that I can use a For Loop and set the cell value from one sheet to the other like in the code below, then use pastespecial for pasting values into my own sheet.

For i = 1 to NumberOfRowsToCopy
pasteWorkbook.Sheets("Sheet1").Range("A" & i).Value = copyWorkbook.Sheets("Sheet1").Cells(1,i).Value
Next i

Will this help my processing time? Is there something else that would work better?

Thanks,
Mike