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

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

Mike,

Give us a bit more detail. In each "copy" workbook, is it only 1 column that you're copying? And then when you're pasting it into the "paste" workbook, are you putting it into the next available row or something like that?

Hi, thanks for your reply.

Here's a little bit of a clearer picture. I'm copying a section with 4 rows in it, and two sections with 1 row each, the rows of data being about 150 columns long. I am pasting into the next available row. The way the copy workbook works, it's got a dropdown, and the data change with the dropdown, so I have to copy and paste the data for every possible value of the dropdown. In my current paste workbook, I end up with about 70 thousand rows and the program will likely have to copy from multiple workbooks, so I could easily be copying and pasting some 200 thousand rows of data. It currently takes about 10 min to run.

Ok, that helps.

One thing I'm still not completely clear on...the drop down in the copy workbook. How many values are in the list? What do you mean by "the data change with the dropdown?"

Can you zip and attach to make it easier to see what you're doing?

In the file I'm currently working on there are 12 possible values for the dropdown, but I can envision as many as 30 or so. What I mean is that the data are based on formulas that are dynamic with the dropdown, i.e. the data is based on a sum if array function, where the if is based on the value in the dropdown. It sums from a larger set of data, but unfortunately those data are not well organized and the sum if function is sufficiently complicated that I don't see a point in trying to figure it out. The workbook I'm copying was not created by me, if that helps you understand what's going on. Additionally, there are actually 2 dropdowns that between them have 12-30 possible combinations of values, I just said that it was one for simplicity's sake.

Does this make it clearer?

I wish I could send an example, but unfortunately the workbooks I'm working on have a ton of proprietary data and I don't have time to put together a scrubbed example right now.

Thanks again,
Mike

Still trying to understand exactly. Can you mock up something small and not sensitive? with the "before and after" look?