Need A Fast Script To Copy 80000 Cells But My Current Script Is Too Slow

Loops are for noobs just a joke, but with some truth to it.
loops are a nice way to learn VBA and can be usefull in many cases but not for copying large ranges. there are far better options for that.


there is no need to copy cell by cell. if you want to copy and paste 1 area whether it is 8 cells or 8 miljon cell you can do that in one codeline without looping..
so yes vba can handle large datasets when they are handled in the right way..

I think this is all you need for the copy.

Please Login or Register  to view this content.

to explain..
.Usedrange is the VBA version of CTRL+A or select all this is usefull when the exact data size is not known but all data is in one range and no other data
But there are many other ways to control the size of the copy range. depending on your needs..
sheet1. is the codename of the sheet you called with sheets("sheet1") advantage is that when you change the sheetname on the tab from sheet1 to "rawdata" the codename is still sheet1 and code will run without change when using your version you also need to change the name in the code. That is why I prefer the codename as it is more failsafe

__
also for your testdata set you can use a different code that is not that slow as your loop..

Please Login or Register  to view this content.

see attachment, generating the testdata is still a few seconds but copying is a splitsecond

Ok, so usedrange is not suitable for you, no problem..
But your decription is a bit confusing at times as you seem to mixup columns and rows. So I will translate your needs as I understand them
(for correct reference the columns have a letter and the rows have a number. So in standard excel reference terms your range is 21 columns wide and 800 rows long..)

So based on the new description I understand that

- the source range is always on sheet1 but the copy area changes depending on amount of available date, the copy rangealways starts in column C and then you need the last 800 rows of the available data.
- the paste or destitnation range is always on sheet2 and in the fixed postion starting in cell C21, so for every copy / paste the old data in sheet two is overwritting / replaced by the new data
- the ranges is 21 columns (C to X) wide and 800 rows long it has no gaps.
because there are no gaps in the range, no looping is needed for row to row transfer you can copy 800 rows in 1 go..

This new macro will find the last filled row of sheet1, column C (lastRow variable)
then it calculate the start row of based of that value (startRow = lastRow - 800)
the range-object, helps me to point to topleft cell and bottom right cell to get the total range to copy, all cells between C&startrow and X&lastrows are automaticly included in the copy range. (total of 800 rows with 21 columns each = 16800 cells)
All those cells will be copied to sheet2 with in topleft cell C21

Please Login or Register  to view this content.

let me know if this is closer or hopefully exactly what you need..

now this should work as a basic setup