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

Vishesh'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.

Code change?

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?

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...

Use this...

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

Copying to a range

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?

Thanks

Vishesh's picture

No, this approach cannot copy

No, this approach cannot copy the width and wrapping.

Thanks for your quick reply.

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

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

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

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

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.

Vishesh's picture

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

Vishesh's picture

Copy Values

Try this:
 
Call CopyValues(Thisworkbook.Worksheets("OpenOrders").Range _ 
   ("A:K"), Workbooks("ReconcileFile.xls").Worksheets _ 
   "GIMII").Range("A1")) 

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.

Vishesh's picture

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 ?

I created a new thread in the

I created a new thread in the Q&A forum

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.

 

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.

Vishesh's picture

Copy Values from Excel to Word

Right now I could only come up with the following...its not as efficient as Excel to Excel copy but just give it a try...I haven't put any validations to check blank cells in the complete range.
 Sub Execute()
 
    Call CopyToWord(Sheet1.UsedRange)
 
End Sub
 
 
 
Sub CopyToWord(rngCopy As Range)
 
    Dim appWD           As Object 'Word Application Object

    Dim arr()
 
    Dim lngRow          As Long
 
    Dim lngCol          As Long
 
 
 
    arr() = rngCopy
 
 
 
    Set appWD = CreateObject("Word.Application.8")
 
    Application.ScreenUpdating = False
 
 
 
    appWD.Documents.Add
 
 
 
    For lngRow = 1 To UBound(arr(), 1)
 
        For lngCol = 1 To UBound(arr(), 2)
 
            If lngCol = UBound(arr(), 2) Then
 
                appWD.Selection.typetext Text:=CStr(arr(lngRow, lngCol))
 
            Else
 
                appWD.Selection.typetext Text:=CStr(arr(lngRow, lngCol)) & vbTab
 
            End If
 
        Next lngCol
 
        If lngRow <> UBound(arr(), 1) Then
 
            appWD.Selection.TypeParagraph
 
        End If
 
    Next lngRow
 
 
 
    appWD.Selection.WholeStory
 
    appWD.Selection.ConvertToTable Separator:=1, NumColumns:=UBound(arr(), 2), _
 
        NumRows:=UBound(arr(), 1), AutoFitBehavior:=0
 
    With appWD.Selection.Tables(1)
 
        .Style = "Table Grid"
 
        .ApplyStyleHeadingRows = True
 
        .ApplyStyleLastRow = True
 
        .ApplyStyleFirstColumn = True
 
        .ApplyStyleLastColumn = True
 
    End With
 
    appWD.Selection.EndKey Unit:=6
 
 
 
    appWD.Visible = True
 
 
 
    Set appWD = Nothing
 
    Application.ScreenUpdating = True
 
End Sub
Nick's picture

Transpose

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

Vishesh'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
Nick's picture

copy values

that works well... fast too.

nice one