Repost - Is there a quicker way to copy paste

[Geoff]

Hi
A brief summary of where I am with this. Yesterday I posted the question
when using code from an xla does it take 12 seconds to copy some 20,000 rows
by 10 columns when the same code from an xls wbook, same data, takes only 1
second.
Tom Ogilvy, thanks, suggested copying values only but I need an exact copy
of sheet1 data to use as a backup.
NickHk, thanks, suggested copying the wsheet instead of the data.
Unfortunately this had the effect of increasing the time from 12 to 21
seconds.
From testing it does not appear that turning calculation to manual has any
discernable effect.
To save folk having to find the code in the original post here it is again.
Appreciate any thoughts

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

 

[NickHK]

Geoff,
I can't see any any noticable difference running code (copy/paste) on 20000
x 12 cells from an .xls or xla, both about 1 sec.
Not sure if it is your method of getting the required range, but does
something like this help:
Sheets(1).Range("IV1").CurrentRegion..Copy
Destination:=Sheets(2).Range("A1")

NickHK

 

[Ardus Petus]

Sheets(1).UsedRange.copy Sheets(2).range("A1")

HTH

 

[Geoff]

NickHK
No i am happy with the range finding statement BUT...
You are right about the speed - this is curious......
Your test result caused me to take a copy of this wsheet, allegedly 21,053
rows by 10 columns and create a new wbook. I ran the code from the xla and,
like you, found it took only 1 second.... mmmmm.
I have used the original wbook because to date it represents the largest
wbook. My code copies the entire column (as I found very little advantage to
be gained by limiting the copy by rows) and I now begin to wonder if
somewhere in the rest of the original rows there is some hidden formatting
for instance which might be affecting the situation.
Even so, if copy pasting from the xla using a new work book containing the
21,053 rows takes 1 second why does copying only 3 times times the amount
take 12 seconds from the original wbook?

Geoff

 

[NickHK]

Geoff,
If you copy/paste a lot of formats/names etc that you do not really need, it
will not make the process any faster. How much slower will depend on ...many
things.

NickHK

 

[Geoff]

Hi Ardus
No that still took 12 seconds.
I tested further after NickHk's reply and specifically set the range from A1
to last cell, which as you suggest should be equivalent to UsedRange in this
case but found no change.
I conclude there must be some kind of formatting which is being copied.
But what I have now is:
If I manually create a new wbook and copy only the visible data, 21,053 x
10, to it and run the xla the copy paste takes only 1 second.
The same applies if I disable the xla and copy ALL of sheet1 into the
original xls code wbook and run the code from there.
The exception is when I programatically create a new wbook using:
Sheets(1).UsedRange.Copy Destination:=procFile.Worksheets(1).Range("A1").
When I run the xla here, I get 12 seconds. In my opinion then, the above
statement is somehow affecting the procedure. But how??

Geoff

 

[NickHK]

Geoff,
Any benefit if you toggle
Application.Calculation
Application.EnableEvents

NickHK

 

[Geoff]

Hi NickHK
Tried Calculation to manual with no benefit, to my surprise. Re
EnableEvents, I use DoEvents to increment a progress bar so presume by making
EnableEvents = False it would prevent that function.
I am going to focus on that part of my reply to AP - what is happening when
I programatically create a new wbook using UsedRange.Copy. As I said if i
manually copy ALL Sheet1 to the original xls of the xla I get 1 second. If I
manually copy the specific data range to a new wbook and run the xla I get 1
second. Only when I programatically create a new wbook using UsedRange.Copy
do I get the long delays.

Maybe I should experiment a bit more with PasteSpecial when creating a new
wbook. But as I recall when I was doing the code for that, I got 'Serious
Errors' and only with the suggestion from AP to use UsedRange.Copy did the
proc work.

But I am convinced there is some kind of formatting issue here because I
have tried other largish wbooks and found the same. But importantly, not on
every one - so it looks as though someone is treating there data differently.

Geoff

 

[Geoff]

Hi NickHk
If you are still following the thread
I have established an improved methodology. The process was with a non 3
wsheet wbook:
1. create a new std wbook and copy paste the original wsheet to it
2. save the new wbook BUT leave it open for other processing
3. close the original wbook

I have found if I close the new wbook and then re-open it immediately for
other processing which amongst other things creates a backup on Sheet2 then
this copy paste takes 1 second as expected.

The only thing I have to puzzle out now is why does it takes so long to copy
data from the original to the new wbook.

Geoff