Hello,
I found this page http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm and this code
but how I do such a copy and paste to another workbook?
Thanx!
/Obelix
Hello,
I found this page http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm and this code
but how I do such a copy and paste to another workbook?
Thanx!
/Obelix
Re: Copy and paste without clipboard to another book
What about something like this - untested though
Sub Copy()
Dim RngCopy As Range
Dim RngPaste As Range
Set RngCopy = Workbooks("Your book.xls").Sheets("Sheet1").Range("A1:A200")
Set RngPaste = Workbooks("Book to paste.xls").Sheets("Sheet1").Range("A1:A200")
RngCopy.Copy RngPaste
End Sub
Robert
Re: Copy and paste without clipboard to another book
A word of caution, both workbooks need to be open for this to work!
:roll:
Robert
Re: Copy and paste without clipboard to another book
Ya know, that is a curious example...
I'm not sure why Dave suggests to avoid copy/paste whenever possible as a means to speed up code. Maybe he can shed some light?
I've actually come across more examples to the contrary; where assigning range values to be equal is much slower than a copy/pastevalue.
Sub test()
'slower
Sheet2.Range("A:E").Value = Sheet1.Range("A:E").Value
'faster
Sheet1.Range("A:E").Copy
Sheet2.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub
Display More
If anything, I'd say assigning values is a convenient code shortcut, but it certainly won't speed up a macro.
Re: Copy and paste without clipboard to another book
Quote from Aaron BloodYa know, that is a curious example...
I'm not sure why Dave suggests to avoid copy/paste whenever possible as a means to speed up code. Maybe he can shed some light?
I've actually come across more examples to the contrary; where assigning range values to be equal is much slower than a copy/pastevalue.
Code Display MoreSub test() 'slower Sheet2.Range("A:E").Value = Sheet1.Range("A:E").Value 'faster Sheet1.Range("A:E").Copy Sheet2.Range("A1").PasteSpecial (xlPasteValues) Application.CutCopyMode = False End Sub
If anything, I'd say assigning values is a convenient code shortcut, but it certainly won't speed up a macro.
Yeah, noticed that too, ran a timer function on your example the first code (the slow one) ran in 0,5 seconds while the copy paste method ran in !! 0,0001 seconds??
Check it out:
Sub test()
Dim Starttime As Date
Starttime = timer
'slower
Sheet2.Range("A:E").Value = Sheet1.Range("A:E").Value
MsgBox Format(timer - Starttime, "00.0000") & "Seconds"
Starttime = timer
'faster
Sheet1.Range("A:E").Copy
Sheet2.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
MsgBox Format(timer - Starttime, "00.0000") & "Seconds"
End Sub
Display More
??
Re: Copy and paste without clipboard to another book
I've been meaning to build a tool to quickly compare the processing times of two macros.
I posted something to the Cool forum. I just wrapped the timer in a userform along with some tricks I stole from Chip to get a list of macros to choose from.
http://www.ozgrid.com/forum/showthre…0941#post240941
Whadya think?
Re: Copy and paste without clipboard to another book
As Aaron has sent me a PM, I'll reply here.
The Copy Method is faster than copy and paste. The reason being the Copy Method bypasses the Clipboard.
The converting of formulas to values is best done like below IMO
Range("A1:A10")=Range("A1:A10").Value
In the case of time comparison test I see a few flaws in it.
1) PasteSpecial is best for very large ranges but not normal size ranges.
2) When you first run a Procedure all code in the same Module is Compiled. This means the second half your code would already be compiled.
Try this way
Sub Test1()
Dim Starttime As Date
Starttime = Timer
For lcount = 1 To 10
Sheet1.Range("A1:E10").Copy
Sheet2.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next lcount
MsgBox Format(Timer - Starttime, "00.0000") & "Seconds"
End Sub
Sub test()
Dim Starttime As Date
Starttime = Timer
For lcount = 1 To 10
Sheet2.Range("A1:E10").Value = Sheet1.Range("A1:E10").Value
Next lcount
MsgBox Format(Timer - Starttime, "00.0000") & "Seconds"
End Sub
Display More
QuoteAlso, half the recomendations on that page I would characterize more as modest code efficiencies
Hmm perhaps. However, lot's of little good habits amount to time saved. As the sayiong goes, 'if you are going to do something do it well'
Re: Copy and paste without clipboard to another book
Quote from Dave HawleyDisplay MoreAs Aaron has sent me a PM, I'll reply here.
The Copy Method is faster than copy and paste. The reason being the Copy Method bypasses the Clipboard.
The converting of formulas to values is best done like below IMO
Range("A1:A10")=Range("A1:A10").ValueIn the case of time comparison test I see a few flaws in it.
1) PasteSpecial is best for very large ranges but not normal size ranges.
2) When you first run a Procedure all code in the same Module is Compiled. This means the second half your code would already be compiled.Try this way
Code Display MoreSub Test1() Dim Starttime As Date Starttime = Timer For lcount = 1 To 10 Sheet1.Range("A1:E10").Copy Sheet2.Range("A1").PasteSpecial (xlPasteValues) Application.CutCopyMode = False Next lcount MsgBox Format(Timer - Starttime, "00.0000") & "Seconds" End Sub Sub test() Dim Starttime As Date Starttime = Timer For lcount = 1 To 10 Sheet2.Range("A1:E10").Value = Sheet1.Range("A1:E10").Value Next lcount MsgBox Format(Timer - Starttime, "00.0000") & "Seconds" End Sub
Hmm perhaps. However, lot's of little good habits amount to time saved. As the sayiong goes, 'if you are going to do something do it well'
First off... You all should know in my PM to Dave I also told him he could probably find 10x the number of things to shoot holes in on my website. So I should step very lightly here while hurling bricks out the windows of my glass house. :duck:
My last commentary...
Yeah OK, I'll give ya that on the, "good habits are a good thing" argument for the suggestions that are more geared toward tidy coding.
Also the shortcut of doing the copy/paste with a single line of code is certainly reasonable.
I don't think I was challenging those. I was concerned more about the last example that specifically tells people to avoid copy/pasting in favor of setting values equal.
The code above for setting small ranges of values equal I think you'd agree is a rare exception to the case rather than the norm. I think I even brought it up in the PM as one (and maybe the only) possible exception. Additionally, for that example I only see a differential of a few hundredths of a second.
In the end, I think it's considerably safer to assume that processing time slows down when values are set equal... and on rare occasions when it's faster it's by an unnoticeably small margin.
I posted one small example just to illustrate, using just 4 columns on a single sheet. Try converting this next macro to the most efficient set value equivalent and run it on a 3MB workbook:
http://www.ozgrid.com/forum/showthread.php?t=38064
Yeah agreed, it's a good-to-know method, and I use it frequently when dealing with small ranges where I know processing time is not going to be a factor. I would not recommend people always avoid copy/paste as a way to improve macro performance though. I think at least 9/10 times it's gonna slow down the macro. You're certainly allowed your opinion. Perhaps you prefer the performance hit for code esthetics?
I don't think you're going to sway me on this one.
Re: Copy and paste without clipboard to another book
Aaron, I'm not trying to sway anyone I only came to this Thread as you PM'd me. I would say it's more a case that you are trying to sway me The 'norm' in my opinion would be a range much much smaller than one using an entire column!
QuoteAdditionally, for that example I only see a differential of a few hundredths of a second.
So you agree it's faster then And the value to value method stays in front until the range get's to around Range("A1:E200"). Also, like I've said, a lot of small things make one big thing
IMO, the converting a formulas to values is normally done on small ranges and even single cells.
However, I will certainly concede that, if one codes always assumming entire columns etc are dirty, the PasteSpecial Method is better suited. For me though, I'll rarely do assume this.
Re: Copy and paste without clipboard to another book
I pointed you to the thread as a professional courtesy.
I didn't want to make commentaries on your recommendations without giving you a chance for rebuttle.
I think there are situations where the copy/paste method is faster even on the small ranges... The swaying I'm referring to is the recommendation to always avoid copy/paste.
Re: Copy and paste without clipboard to another book
You are forgetting about the Copy Method though.
Re: Copy and paste without clipboard to another book
Quote from Dave HawleyYou are forgetting about the Copy Method though.
No. I was referring to the copy method. Just shortened it to copy/paste. I guess I shoulda said copy/pastevalue then.
There are unquestionably times when it's faster. I think maybe even for smaller ranges.
Somehow I'm thinking I've recalled situations where the values weren't instantly array-stamped in the range, even smaller ranges. Like it was faster to copy/pastevalue a single row than array set the values.
I think maybe it loads values in a similar fashion to the way an array formula calculates... but I'm guessing a bit now.
Don’t have an account yet? Register yourself now and be a part of our community!