Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel

hivishy's picture

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.

 

Nick's picture

Excellent post.

I have always found this annoying, but never thought there was a good way to do it.

Thanks for sharing this.

 

Nick's picture

Transpose

Have you got a smart way to do the transpose of this too ?

hivishy's picture

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

Thanks for asking this question...I hadn't thought of this before.

 

Nick's picture

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.