I s there a quicker way to copy paste

[Geoff]

Hi
Is there a quicker way to copy paste? In an xla the code below is executed
by a cmdbutton on a form and is used to copy Sheet1 to Sheet2. The copy
paste process takes 12 seconds for 21,000 rows by 10 columns. In its native
form (.xls) the same code and with the same data, the copy / paste takes 1
second. Records can exceed 40,000 rows sometimes and a faster way is
desirable.
Setting Calculation to manual does not appear to reduce the time below 12
seconds in xla form. Is there a better way?

T.I.A.

Geoff

Private Sub cmdExecute_Click()

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

With Sheets(1)

'''get last original column
lastOrigCol = Split(.Range("IV1").End(xlToLeft).Address, "$")(1)

'''copy original data to sheet2
.Range("A:" & lastOrigCol).Copy Destination:=Sheets(2).Range("A1")

''''''other stuff

End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

 

[Tom Ogilvy]

that is just copying 1 cell. I don't see how that could take 12 seconds.

do you have pagebreaks visible? If so, try turning them off.

 

[Geoff]

Hi
With respect, the code copies all rows of columns A to J (last column) in
the data sample I am using. I find there is little point in finding the last
row for this purpose - there is no rduction in time.
There are no page breaks.

Geoff

 

[Tom Ogilvy]

OK - I stand corrected. Misread what you were returning in the split line.

the only other alternative would be to do something akin to a pastespecial
xlvalues

 

[Geoff]

I use this to provide a backup of original data before 'cleaning' sheet1. If
required, Sheet2 provides the means of restoring the original data 'warts and
all' to begin again. Wouldn't I lose this functionality just pasting values?
Is the slowness because when using an xla there are in effect 2 wbooks open
at the same time and as it is the xla calling for the copy paste in another
wbook, the process takes longer. As I said, same code in the original wbook
in xls form takes only 1 second with the same data.

Geoff

 

[NickHK]

Geoff,
If it is only for backup purposes, why not create a copy of the WS, then you
maintain all formatting, formulae etc.
Then delete the WS when ready.

NickHK

 

[Geoff]

NickHK
mmm thought you might have something there however after testing I found the
method increased the time from 12 to 21 seconds.
My question remains, why should an xla cause such a dramatic increase of
time over the same code in an xls form? It is definately volume related.
Wbooks with only a few thousand rows whistle through with no discernable
pause.

Geoff