Home » Blogs » Vishesh's blog

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

Vishesh's picture
Submitted by Vishesh on 9 January, 2010 - 18:04
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.

»

Code change?

Submitted by Shep on 2 September, 2013 - 16:21.

The code above says this:

Case copyByFormula
If blnTranspose Then
rngTarget.Resize(rngSource.columns.Count, rngSource.rows.Count).Value = Application.Transpose(rngSource.Value)

shouldn't it say this:

Case copyByFormula
If blnTranspose Then
rngTarget.Resize(rngSource.columns.Count, rngSource.rows.Count).Formula = Application.Transpose(rngSource.FormulaR1C1)

?

»

Code not executing?

Submitted by xman2012 on 12 September, 2012 - 05:52.

Hi Vinesh, I've modified your example to copy formulas only (with formulas in A1:A5).  The code is not executing?  What am i doing wrong???  Thanks.

Sub CopyValues(rngSource As Range, rngTarget As Range)
 
    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Formula = rngSource.Formula
  
End Sub

Sub UpdateCopyValues()

    Sheet1.Activate
    Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1"))
   
End Sub
 

»
Vishesh's picture

Use this...

Submitted by Vishesh on 12 September, 2012 - 14:09.

Use this...

rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Formula = rngSource.FormulaR1C1

»

Copying to a range

Submitted by xman2012 on 13 September, 2012 - 02:04.

Thanks Vitesh, that worked. I'm trying to copy formulas to a cell range using:

Call CopyValues(Sheet1.Range("A1:A5"), Sheet1.Range("B1:D5"))

It only seems to copy into the B1:B5 and not B1:D5 completely?

Any ideas?

»

HI, Can this approach copy the width and wrapping?

Submitted by Anonymous on 17 November, 2011 - 13:40.

Thanks

»
Vishesh's picture

No, this approach cannot copy

Submitted by Vishesh on 17 November, 2011 - 15:15.

No, this approach cannot copy the width and wrapping.

»

Thanks for your quick reply.

Submitted by Anonymous on 17 November, 2011 - 16:13.

so I still need to use Copy and paste to copy the width and wrapping? also color?

Thank you

»
Nick's picture

have you considered making a

Submitted by Nick on 17 November, 2011 - 17:47.

have you considered making a copy of the sheet - that will copy everything...

Sheets("XXX").Copy Before:=Sheets(1)

»
Vishesh's picture

Improved code to copy without

Submitted by Vishesh on 12 October, 2011 - 07:29.

Improved code to copy without clipboard. Copy the following code and call 'CopyPasteWithoutClipboard' from any module. As an example, a 'Test' procedure is given in the end.

Enum PasteAs
copyByValue = 1
copyByFormula = 2
End Enum

Sub CopyPasteWithoutClipboard(rngSource As Range, rngTarget As Range, lngPasteType As PasteAs, Optional blnTranspose As Boolean = False)

'Do not use this procedure with filtered/hidden rows as it considers all hidden/filtered cells
'While transposing only values can be transposed not formulae
'Merged cells are not considered

Dim lngCalc As Long
Dim lngEvents As Long

With Application
lngCalc = .Calculation
lngEvents = .EnableEvents
If Not .EnableEvents = False Then .EnableEvents = False
If Not .Calculation = xlCalculationManual Then .Calculation = xlCalculationManual
End With

Select Case lngPasteType
Case copyByValue
If blnTranspose Then
rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = Application.Transpose(rngSource.Value)
Else
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
End If
Case copyByFormula
If blnTranspose Then
rngTarget.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = Application.Transpose(rngSource.Value)
Else
rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Formula = rngSource.FormulaR1C1
End If
End Select

With Application
If Not .Calculation = lngCalc Then .Calculation = lngCalc
If Not .EnableEvents = lngEvents Then .EnableEvents = lngEvents
End With
End Sub

Sub Test()
Call CopyPasteWithoutClipboard(Sheet1.Range("A1").CurrentRegion, Sheet2.Range("A1"), copyByValue)
End Sub

»

Formulas over a range

Submitted by manch on 11 October, 2012 - 11:22.

Vishesh,
Using:
Call CopyPasteWithoutClipboard(Sheets(Target_sheet).Range("N5:W5"), Sheets(Target_sheet).Range("N6:W2307"), copyByFormula)

It only seems to copy into the N6:W6 and not across the full range N6:W2307?

Any help would be great, Cheers

»