Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel
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.