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
Sheet1.Range("A1:A5").Copy
Sheet1.Range("B1").PasteSpecial xlPasteValues
Another method:
write the following function in the code window
Sub CopyValues(rngSource As Range, rngTarget As Range)
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End Sub
and call the above function using the following line of code wherever you need to paste values
Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
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("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.
- hivishy's blog
- Add new comment
- 520 reads
Excellent post.
I have always found this annoying, but never thought there was a good way to do it.
Thanks for sharing this.
Transpose
Have you got a smart way to do the transpose of this too ?
Transpose - How about this ?
Sub Transpose(rngSource As Range, rngTarget As Range)
rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = Application.Transpose(rngSource)
End Sub
Sub ExecuteTranspose()
'Calling Transpose function as...
Call Transpose(Sheet1.Range("D1:H2"), Sheet1.Range("A1"))
End Sub
copy values
that works well... fast too.
nice one
Can you do it between Excel and Word?
Is there a way to use a similar method to move data from Excel to Word? Sounds like it would make copying some Excel ranges into Word reports sooo much faster.