Copy Paste until blank

  • Hi, this is a request for a new sub routine. I've attached a template for the steps and dummy data that I would like to simulate.


    I have multiple columns of random data (3 rows each) from columns N to column T or at least until there is an empty column range in sheet1. I want to copy and paste the first column data ( 3 rows) in column N and paste it to say sheet 1, cell C5 then my model runs until a condition is met and paste results in the next sheet2. Once that condition is met, then I want to get next column data (3 rows) from column N and paste over the data in cell C5 and the model runs until same condition is met again etc. This goes on until a blank column is met in sheet1 then stops.


    Thanks in advance.

  • This will copy each set of numbers to a sheet, adding the sheet if needed...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Thanks for the feedback. I tried to manipulate your code to generate the range of numbers in sheet 2 only as a sequence as how they were displayed in sheet 1, once the condition is met but, I failed.


    What I want to achieve is to copy the range of numbers in each column starting from sheet 1, column N5:N7 then paste it into column C, C5:C7. Once the condition is TRUE looking at sheet 1, A1 then this range of numbers are value pasted into sheet 2, A1:A3. The iteration starts over again and moves to the next column in sheet 1, that is O5:O7 and does the same thing by pasting in C5:C7 this range of numbers and once the condition is met then it value paste that range into sheet 2, B1:B3 etc. This continues for each column of data until a blank column is met in sheet 1. The code ends then.


    I failed to modify your code. I want to achieve pasting each separate column range of numbers in sheet 2 only as a sequence to each other and and also to ensure the condition set is a reflection of each range of numbers pasted into sheet 1, C11. Hope this is clearer or at least let me know. Thanks in advance. My failed attempt at code below :-(

  • If I've been helpful, let me know. If I haven't, let me know that too.

  • Solved. I took your code and re-modified it to value paste the series of data into sheet 2, as a sequence once the condition is met. This gives me the desired outcome. Thanks again for your guidance.


  • HI RR,


    This code works but starts on sheet 2 at B1, you can change to start pasting in A1 with an if function if necessary. To test it I changed your if function in A1 to read =if(C11 > C10, TRUE, "") then put a number in C10 that would cause some false results.