Copy and Paste Macro Ad infinitum

[Istrane]

Hey guys I have a question. Here is the rundown of what I am trying to do.


1. I want to copy a specific cell from Sheet1 let's say A1

2. Then I want to paste the 'value' not formula of that cell on Sheet2 starting at A1.

3. Next I want to refresh the data values on Sheet1 (it does calculations with rand)

4. After the refresh I want to again copy the 'value' from A1 on Sheet1 and paste it in A2 on Sheet2.

5. Repeat refreshing Sheet1 copying and pasting into A3, A4, A5 ... of Sheet2

I want to repeat this as many times as possible.


From browsing I have figured out I can use this to search a column for the next available spot.


Dim LastRow As Integer

LastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row (Thank you Luke M)


I don't know if it is possible to fill up Column A and move over to B or not, but I am fine with 65k values each time I run the macro. I could do the transfers manually.


My problem is I don't know how to use the copy or cells command correctly.

This was my attempt

Worksheets("Sheet1").Cells("1","A").Select

Selection.Copy Worksheets("Sheet2").Cells(LastRow + 1, "A")


Even if that somehow worked, I am not sure if the data on Sheet1 needs some type of command to refresh or not.


Any Help would be greatly appreciated, thanks.

 

[SirJB7]

Hi, Istrane!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look at this file:

https://dl.dropbox.com/u/60558749/Copy%20and%20Paste%20Macro%20Ad%20infinitum%20%28for%20Istrane%20at%20chandoo.org%29.xlsm


And sorry to say this, but your upwards code isn't fully bullet proof, check this link:

http://chandoo.org/forums/topic/vba-colour-to-rows#post-35531


You should be aware that with the default parameters (rows 1048576 and columns 16384) you'll be trying to fulfill a whole worksheet, that is 17 billion (thousands of millions) cells. That's a huge volume and it will last very long, which I don't actually know if any machine can handle. So be my guinea pig.


Workbook is saved after each column filled.


Just advise if any issue.


Regards!


PS: Depending on your random function in cell A1 of 1st sheet, maybe it'd be more efficient to copy 10, 1000 or the whole column, and then copy those blocks to 2nd sheet.

 

[Hui]

Istrane


Firstly, Welcome to the Chandoo.org forums.


Can I ask what your going to do with all this Random Data ?

As there may be better ways of doing what your thinking


Have you read: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

 

[Hui]

Istrane


If your not hung-up about going down a column and are happy to go across row after row

[pre]

Sub FillSheetUp()

For i = 1 To 20000 'Remember each row has 16384 columns
Worksheets("Sheet2").Cells(i) = [a1]
Next i

End Sub

[/pre]

is as simple as it gets

 

[Istrane]

Thanks for the warm welcome guys!


SirJB7 your posted spreadsheet seems to go above and beyond what I was able to comprehend. I am doing this mostly as an intellectual exercise and to help a friend, so I am going to spend a while going over your code to figure out what each line does.


I am not currently at home and thus can't test it fully on my spreadsheet. I do have a decent PC though so I am tempted to see if it can handle a full spreadsheet. I may start with just 10-30 Columns and if all goes well, I will try the whole spreadsheet. I will let you know the outcome tonight hopefully.


-

Istrane

 

[SirJB7]

Hi, Istrane!


I made a couple of slight modifications to the code and it passed from 5' to 10K cells (2K/min) to less than 3' for 1M cells (300K/min), a 15000% of improvement.

The code was originally recalculating 1st sheet, but that'd ve been only necessary if you use a UDF (user define function) for A1 cell. So I only recalculate A1 cell and I assign directly the value instead of selecting, copying, and pasting only values: much faster.


I added too a step count (constant klDebug set to 1E5) to display number of cells created and the actual time. Check the Immediate window from the VBA editor to see the progress.


So, please download again the file from same previous link.


Regards!

 

[Istrane]

SirJB7,


Sorry work has had me occupied lately and I haven't had time to reply. I appreciate you taking steps to improve the macro, unfortunately I need it to recalculate the entire sheet like it did originally. My main sheet does other calculations of which the end result is what I need copied. Somehow, I lost the original file and I am not skilled enough to recreate it. Is there anyway you could recreate a file similar to the one you posted originally. Perhaps drop them both in a zip file so I could compare them later and perhaps learn the workings of both. That would be greatly appreciated.


Sorry to be such a pain.


EDIT: SirJB7, never mind I found a old copy of the macro!

 

[SirJB7]

Hi, Istrane!


Better you found it, otherwise I should have re-des-built the last one as I kept only the optimized version.


Despite of this, I recommend you to keep the latest version with this tweaks:


a) comment the lines:

.Calculation = xlCalculationManual

.Calculation = xlCalculationAutomatic

this will keep your workbook calculation as normal, increasing processing time but hopefully much less than original one


b) hope you don't have change event triggered code, if yes, I don't remember first version, so if you upload it I'll confirm you


Regards!