loop/repeat copy/paste Macro

[vizbasic]

In Excel, I have a created a cell that randomly generates values. Using a Macro, I would like to copy the randomly generated Cell and paste special the value in another cell. Then I want the macro to repeat this process X times, but paste the randomly generated value in a different cell. For example, if I am randomly generating the values in Cell A1, I want the macro to randomly generate Cell A1, copy the value, and paste special in Cell B1. Then I want the macro to randomly generate a value in Cell A1 again, copy the value, and paste special the value in Cell B2, etc. etc.

I created a basic Macro, but it does not work correctly. The VBA code is as follows:

Please Login or Register  to view this content.

-----------------------------

The above code essentially does what I want to do, but it pastes each iteration over the same cell. I realize that the issue is with the Range("L22").Select portion of the code. However, I do not how to resolve this issue. If anyone could help I would greatly appreciate it. Thanks

Josh

[shg]

Welcome to the forum.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with Code Tags.

[shg]

Josh,

Thanks for the code tags.

I don't see how your code follows your description of what you're trying to do. Can you back up a little and explain your objective?

[vizbasic]

Here is what I am trying to do:

In Cell L16 of my Excel file, I have calculation that generates random values (i.e. there is calculatoin in that cell that is tied to the =rand() function). I want to run a macro that copies Cell L16 and pastes it into Cell L22. Then I want to repeat this procedure, except instead of pasting into Cell L22, I want to paste the copied value into the cell directly below Cell L22 (i.e. Cell L23). Furthermore, I want the Macro to repeat this procedures 1000 times; each time pasting the copied cell below the previously pasted cell. For example, when I run the Macro, I want it to copy Cell L16, Paste in Cell L22, copy Cell L16, Paste in Cell L23, Copy Cell L16, Paste in Cell L24 etc. etc. all the way until it reaches Cell L1022.

You can essentially disregard the code that I posted in my original post, since it is incorrect. I was just posting it for illustrative purposes. That code, however, is obviously incorrect since it simply copies Cell L16 and pastes it into Cell L22 ten times. Want I want to create is described in the paragraph above. I hope that clarifies things a little better.

Josh

[shg]

Maybe like this:

Please Login or Register  to view this content.