Need help with defining VBA Macro for copypaste from sheet1 to sheet2 and back & looping

Sorry Just bumping this since I edited my post a little

Thanks for the reply AB33! Your code is working But it is not peforming as expected. Below are the problems -

1.For Step 3, to answer your question, it is just those 7 rows - C22:C27. Also, For step 3, I would like it to be looped 700 times - as many times as steps 1 and 2. So in the first iteration it should take values from C22:C27 and put them back into sheet 1 AB1:AG1, on the next iteration it should take values from C22:27 and put them back to AB2:AG2. On the third iteration it should take values from C22:27 and put them back to AB3:AG3. On the fourth iteration it should take values from C22:27 and put them back to AB4:AG4. You get the drift...

Basically, sheet 1 is my inputsheet from where I grab each row of values and paste it on sheet2 on the SAME set of cells each time. And then I pull/copy my "output" values from cell C22:C27 EACH time and put it back to sheet 1 in the specified rows.
so each input row from sheet 1 has a corresponding output row on sheet 1 (pasted in AB:AG) incrementally.

2. For STEP 2, I would like to copy values in cells B1,D1,F1,H1,J1,L1 and N1 from sheet 1 and paste them into C12:C18(transposed) on sheet 2. - Fo every single iteration I would like the values to be pasted on the SAME cells (C12:C18(transposed) on sheet 2) NOT IN DIFFERENT cells like you have done in the code.

3. I would like to add a few milliseconds of delay before step 3 is executed for each iteration.

Please advise with appropriate code. Thanks again!!

"For STEP 2, I would like to copy values in cells B1,D1,F1,H1,J1,L1 and N1 from sheet 1 and paste them into C12:C18(transposed) on sheet 2. - Fo every single iteration I would like the values to be pasted on the SAME cells (C12:C18(transposed) on sheet 2) NOT IN DIFFERENT cells like you have done in the code."


Sorry! I am not getting it. When you. So say loop through, you are looping from row 1 to row 24 ,one at a time, so how can copy 24 times in to single range of C12-C18? Or are you just copying a single row which is row 1. When you transpose a range you swap horizontal to vertical and vice versa. In your case, the data in source are in horizontal and will change in to vertical.

No Problem. Let me explain. Yes I would like to loop through rows 1 through 24 (or 700) one at a time and this is how it should work -
First, Think of sheet1 as a data feeder sheet. Think of Sheet 2 as the magical engine/application with a set of input(B12:B18 and C12-C18) and output fields (C22:27) so you pass your data from sheet 1 to sheet 2, you grab the outputs (C22:27) and put it back to sheet 1(AB:AG)

NOW -
1.You copy row 1 data first(from sheet 1 range A1:N1) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB1:AG1
2.Then, You copy row 2 data(from sheet 1 range A2:N2) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB2:AG2
3.Then, You copy row 3 data(from sheet 1 range A3:N3) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB3:AG3
4.Then, You copy row 3 data(from sheet 1 range A4:N4) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB4:AG4
5.Then, You copy row 3 data(from sheet 1 range A5:N5) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB5:AG5
So on and so forth until 700 rows are completed....


Question - what is NR = 12 in your code ???

I would like to add a few milliseconds of delay before step 3 is executed for each iteration.

Question - what is NR = 12 in your code ???

I would like to add a few milliseconds of delay before step 3 is executed for each iteration.

N is there the first row in sheet 2.
I would like to add a few milliseconds of delay before step 3 is executed for each iteration.
Sorry! I do not know. You could post it as a new thread.


1.You copy row 1 data first(from sheet 1 range A1:N1) into range of B12:B18 and C12-C18 respectively. NOW AFTER COPYING these, You go grab the values from C22:27 on sheet 2 and paste them back to sheet1 in rows AB1:AG1
Okay! You want to copy C22:27 24 times too. Whether I post it separately, or incorporated with the code, it will not make any difference, you want to match the number of times you wish to copy that range is the same as the other loop.
Now, How can you copy all 24 rows in to the same range B12:B18 and C12-C18 ?

sheet 1 range A1:N1 will be copied in to B12:B18 and C12-C18 and then
range A2:N2 will be copied in to next empty cells, unless you want to cut the already copied range.
I am missing something. Where are all 24 rows will be copied in to sheet2?

Please use code tags with your code. It is forum's rule to use tags with your code. Look at my code, they all wrapped in code tags. Could you please edit the above code while I look at the thread? Click edit, highlight the entire code, then click this sign # from the quick reply.

Ok. Done. Thanks. Since I am a newbie I was not aware or read the guidelines.

Now back to start!
You do not need NR as my original code assumed that you want to copy 24 rows next to each other. Since you are not, no need to use next row.
As I copy and cut range C22-C28, sheet1 AB will be populated by the new range in C22-28. That is A1-N1 will be copied in C22-28 and cut it and copy back to sheet1 in the next Range AB.
Where I am lost is what is the relevance of copying B-12-B18 and C12-C18, if we are simply cut them and will not be copied in to any where? What is the point of copying and cutting them? My understanding from the attached is you have data in sheet 2 range C22-28 and these are copied from the range above C12-C18.
I think if you are trying to link them, perhaps as you said, you need a code with timer delay.
Sorry! I could not helpful as I am probably not getting your request.

Okay. I fixed that too now. Try code below it works. BUT I see that both your sheet 1 and sheet 2 code loops are independent. I want steps 1, 2 and 3 to occur one after the other in that sequential order.
how do I include the "with ms..." function and make it a part of the earlier step 1 and 2 loop ? Can you modify the code below and paste it back after making the changes ?

Please Login or Register  to view this content.

No, there is something missing from the code. As you get rid off the next NR=NR+1, the code stuck at 12 and did not go to C22, hence nothing is copied in to AB sheet1 as C22-28 range is blank.
You either change the N=12 to N=22, or out back the next row loop.
Here is all lines of code in one

Please Login or Register  to view this content.

Well thanks again but this code you pasted seems to hang up after iteration 3 and does not go any further. On debugging it seems like it gets stuck here:

Please Login or Register  to view this content.

did you try running it at your end ? I am attaching a sample spreadsheet you can use to run it on.

attached book4.xls

Yes, you are right!
N is fixed at 12 and not increasing with the loop, so despite the code loops through 300 times, NR remains static 12, so the code will end up copying the last batch as all previous batches have been overwritten.
It is the same issue with the attached

the fact that previous batches get overwritten is NOT a problem for me. What is the problem is the output(C22:C27) does not get copied and pasted to sheet 1 (AB:AG) 300 times. Why does it stop after row 3. How do you fix it ? ideally AB300:AG300 should be populated with output1, output2.... Also if you look at B12:B18 and C12:C18 on sheet2, they should reflect the valus in row 300 of sheet 1 (A300:N300) but they dont. How do you fix that ?

No, N has nothing to do with the bit (C22:C27) of the code

Please Login or Register  to view this content.

These lines do not have N. What these lines do is to copy range c22-27 300 times in the next empty row. First batch will go in AB2-AH2, then the next copy will go in to AB3-AH3 and so on. Why do not you put some figures in the range c22-27 and run the code?

Originally Posted by AB33

No, N has nothing to do with the bit (C22:C27) of the code

Please Login or Register  to view this content.

These lines do not have N. What these lines do is to copy range c22-27 300 times in the next empty row. First batch will go in AB2-AH2, then the next copy will go in to AB3-AH3 and so on. Why do not you put some figures in the range c22-27 and run the code?

Yes, That is what I want. BUT It does not do that. See the book4.xls I attached.I've put some figures in the range c22-27 and ran the code but on sheet1 it only increments till AB3-AH3 and not after that. That is the whole problem. You can take the code and run it agaianst book4.xls I attached and see how many times it increments.

Please do not reply with quote. Quotes have no purpose but only to clutter the space and make me to vomit.

I have tried this code by inserting dummy data in range c22-c27 of sheet 2 and copies 300 times

Please Login or Register  to view this content.