Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2012
    Posts
    5

    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
    Last edited by mg1313; Dec 5th, 2012 at 04:28 PM.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

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

    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?

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2012
    Posts
    5

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

    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.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

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

    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?

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2012
    Posts
    5

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

    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

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width