Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel
In VBA, to Copy Values from range A1:A5 to B1 you will generally write
Another method:
write the following function in the code window
and call the above function using the following line of code wherever you need to paste values
The source or target can be anywhere on any sheet or any workbook
Copy Formula
You can use the similar approach like following:
Sheet1.Range("A1:A5").Copy Sheet1.Range("B1").PasteSpecial xlPasteValues
Sub CopyValues(rngSource As Range, rngTarget As Range) rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value End Sub
Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
Sheet1.Range("A3:A5").Formula = Sheet1.Range("A2").FormulaR1C1
Advantage of using this method of Copying Value and Formula
1. No use of Clipboard - just assigning values/formula; hence it is faster.
2. You must have observed screen flickering in some of the Excel Tools which at times is there even after using screenupdating - but using this there is no screen flicker.
3. Less use of memory.
»
- Vishesh's blog
- Login or register to post comments
- 146169 reads
Great Code
I used this code and it worked wonderfully. Can't thank you enough!
There are caveats
When assigning to the .Value property from the .Value property, numbers stored as text are copied and converted to numbers (just as it would if you manually typed them in), which is not always desirable.
To get it right, one must assign it to the Range object directly:
Range("ToRng") = Range("FromRng")
However, for some reason that only works one cell at a time. I had to implement a double For loop to do it for each cell. I thought performance was to going to degrade, but it held up pretty good.
Copy values and format
If you want to retain the format the use .value as suggested in this blog and then copy and paste the format.
This way you could avoid the loop. Of course, for this the range should be continuous. Or, use pastespecial as the last resort.
I was not talking about the
I was not talking about the cell format, only values. If you have, say, in cell A3 the value 0001 (a string consisting of 4 characters), and you copy the value using Range("A4").Value = Range("A3").Value, the A4 cell will now have a numeric value of 1 instead of a string value of 0001.
Oh yes, sorry I didn't
Oh yes, sorry I didn't understand your question. I agree it doesn't take care of that.
Keep cell formatting
Great advice.
I just have one issue using this procedure: the PasteSpecial xlPasteValues method keeps the formatting of the original data, which is not the case with the procedure you propose. Some of my data is automatically (and incorrectly) converted to dates after "pasting". How can I avoid that and keep my data in its original format?
Thanks
Problem using this function
Hi Hivishy, I've used this amazing function a number of times but for some reason in the latest context I get the following error message: Run-time error '1004': Application-defined or object-defined error The odd thing is that the code works - i.e. the data is copied to the target area but then the error message appears. Here is the line of code: Call CopyValues(Worksheets("OpenOrders").Range("A:K"), Workbooks(ReconcileFile).Worksheets("GIMII").Range("A1")) Any ideas of why this might be happening? Thanks for your help, Emmeline Butt
Copy Values
I've also been using this
I've also been using this excellent method of copying data between sheets and it has decreased the overall processing time of my report. However I've now hit a problem whereby anything over about 27200 rows throws me the same error. Is this method limited to size, or something, somehow?
I'm working in Excel 2010 and am using the formula as you first described it in the original post.
Not sure if you monitor this thread still but thanks in advance.
There is so such limit set to
There is so such limit set to it. Can you please explain what error are you getting ? Still better if you could upload it in the question forum ?