Copy Paste until blank

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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.

Participate now!

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