Copy & Paste: Convert a Loop to an Array

  • Hi All,

    I have a sheet that will be 500+ lines when finished. Because of this, the loop I currently have running takes a very long time to complete.

    What I need to do is convert the code below into an array, but I have very basic knowledge when it comes to arrays and don't fully understand how to write them at the moment.

    I have searched for a skeleton to use but cant find something I understand how to adapt to my needs. Any help would be greatly appreciated.

    Current Functionality

    The functionality of the sheet is to be a cost sheet which then exports a client version. It works by the user "selecting" each row to be included in the client version by putting a 1 in column A (ColCheck).

    If the row is selected to be included in the client version, the sheet will copy the value of certain columns and paste them into the client version. The loop will do this for each row that it selected and then exit the sub. As you can imagine, using a loop to run through this takes quite a while, hence the need for an array.

    You'll notice that there are no Column References in the code. these have been replaced by variables because columns kept being added and I only wanted to change it in one place! These are included. I have annotated the code to make it clear also. Unfortunately I don't have a demo document to put on the forum at the moment. I can upload one if required, but it will take me a while create a blank document.

    Hopefully I have described this well enough, but please let me know if I missed something.

    Thanks in advance,

    Joe :)

  • A better way is to use filtering and copy matching cells.

    Without a sample sheet, attached is an example of one possible way this could work.

    Copy & Paste Different Columns.xlsm

    Wow, thanks gijsmo. It is much more efficient to do it this way than the current solution. I'll just import this into my main sheet and probably have to tweak a few bits to get them working together. I'll let you know how I get on.

    Thanks again!

  • I've put the code into the main sheet and it's all working perfectly.

    Last thing: Is it possible to merge columns B to D (SOWSection to SOWDesc2) on each row after the data has been copied?

    Before, this was done during the loop.

  • Hi gijsmo,

    Sorry, for the delay, I was just trying a few things to see if I could make this work any differently at my end before asking more for more of your time.

    I seem to have sent a different version of the code, which is why the merge cells line isn't in there, but I have worked out that it would be better to do it a different way, if possible.

    Would it be possible to enter 3 cell values into one cell? So, for each row, the values of column B, C & D on the "Cost Sheet" sheet would be entered into column B on the "SOW" sheet. Each value would append to the next.

    Does that make sense?

  • OK, as with many things in Excel, there are a number of ways to do this.

    In the attached example, I have chosen to create the merged data in a helper column to the right of the existing data (this will be temporarily created by the macro in the source sheet).

    The only small complication with this is that the data in the helper column needs to be pasted first into the SOW sheet and the assumption is the remainder of the columns can be copied together after this merged data.

    Please note, there are a number of small changes and corrections to the code in addition to accommodating this merged data so need to copy this carefully to your project.

    Copy & Paste Different Columns v2.xlsm

  • Hi gijsmo,

    This is great. The only trouble is that the merged cell really needs to be pasted into column B. Sorry to be awkward. Is there any way around this? Perhaps pasting the rest of the columns and then inserting the 1st column before those?

  • Thanks for the quick response.

    SOWRef (Column A) should contain values from ColRef

    SOWSection (Column B) should contain the values of ColSection, ColSubNote and ColDescription

    The rest of the columns can be sequential after that.

  • Hi gijsmo,

    Again, sorry for the delay. I've just been testing it all out.

    Everything works brilliantly. Thank you very much for all your help with this, it's much appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!