Copy Range of Cells And Paste Until Last Row

[Unexpc]

Hi guys
i have a page in range A41:H74 and i want copy this page and paste until last row of excel sheet. does any vba code to do this?

 

[Joe4]

So are you wanting to paste to this sheet, all the way down, or to other sheets?

If on other pages, how many sheets are there, and are we going to be copying to ALL sheets?

If down the sheet, what is the first row we will be pasting to? Is it the next one (row 75), or are you skipping any rows in between?

 

[Unexpc]

So are you wanting to paste to this sheet, all the way down, or to other sheets?

If on other pages, how many sheets are there, and are we going to be copying to ALL sheets?

If down the sheet, what is the first row we will be pasting to? Is it the next one (row 75), or are you skipping any rows in between?

copy down this sheet and first cell pasting to row 75 and no skipping any rows
and can i duplicate this sheet with this pages? no problem with this... am i right?

 

[Joe4]

Do you really need to copy down to the bottom of the page?
That is over 30,840 loops!

I have written code that will do that, but it bogging down and timing out, do to the number of times it is running.
If there is some other way to determine exactly how far down it needs to go, that might work better.

Here is the code that I came up with:

Sub MyCopyRange()

    Dim nr As Long

    Application.ScreenUpdating = False

'   Set initial value of next row
    nr = 34
    
'   Copy range
    Do
        Range("A" & nr + 7 & ":H" & nr + 40).Copy Range("A" & nr + 41)
'       Add 34 to next row
        nr = nr + 34
'       Exit if row number exceeds max
        If nr > 1048576 Then Exit Do
    Loop

    Application.ScreenUpdating = True
    
End Sub

Maybe if you let it run long enough, it will finish.

 

[Unexpc]

yeah, YOU FINISH THIS
Thank you

 

[Joe4]

You are welcome.

 

[Unexpc]

one question, when i run macro and doing well copy/paste, the size of worksheet increase to 32MB! and i want duplicate 10 or more this sheet that increase a large amount size for worksheet. this maybe cause damage the file or decrease loading worksheet or anything that's not good?

 

[Joe4]

It shouldn't "damage" it, though it could seriously affect your performance in a negative way (your workbook may become really SLOW!).
Quite frankly, Excel is not the best tool to be storing millions of rows of data. A database is much for something like that (i.e. Microsoft Access, SQL, MySQL, etc).

 

[Unexpc]

Do you really need to copy down to the bottom of the page?
That is over 30,840 loops!

I have written code that will do that, but it bogging down and timing out, do to the number of times it is running.
If there is some other way to determine exactly how far down it needs to go, that might work better.

Here is the code that I came up with:

Sub MyCopyRange()

    Dim nr As Long

    Application.ScreenUpdating = False

'   Set initial value of next row
    nr = 34
  
'   Copy range
    Do
        Range("A" & nr + 7 & ":H" & nr + 40).Copy Range("A" & nr + 41)
'       Add 34 to next row
        nr = nr + 34
'       Exit if row number exceeds max
        If nr > 1048576 Then Exit Do
    Loop

    Application.ScreenUpdating = True
  
End Sub

Maybe if you let it run long enough, it will finish.

do you suggestion nr > 1048576 change number to less number than this ?
and another question, pefomance of system effect to fastest this or excel is not strong to do?

 

[Joe4]

do you suggestion nr > 1048576 change number to less number than this ?

If possible, yes. But it really depends on what the needs are, and you haven't clearly explains them.
Note that it doesn't matter if you did 1,000,000 million records on one page, or 100,000 records on ten pages. That uses the same amount of data.

pefomance of system effect to fastest this or excel is not strong to do?

I am not sure what you are asking here (doesn't really seem to be a well-formed sentence).
Basically, Excel is designed to do calculations. However, using it store millions upon million rows of data will have an adverse affect on performance. That is not really what it was designed to do. It was not really intended to store immense amounts of data (at least not if you want to be able to work with the data).

 

[Unexpc]

another thing, this i ask you, a print page from my main worksheet and i linked main sheet cells to this print sheet. i want when a page fill, next page automatically insert like insert page but automatically, do you have any idea to do this?