Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Excel / VBA Jobs

Navigation

New Excel Experts

milagroscanterbury63
shanelsosa9583420
tiffinybeaver135352

Current Excel / VBA Jobs

More jobs

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

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.

Vishesh's blog
Login or register to post comments
ShareThis
147624 reads

I created a new thread in the

Submitted by nathanjw71 on 6 December, 2012 - 20:19.

I created a new thread in the Q&A forum

Excellent post.

Submitted by Nick on 11 January, 2010 - 09:18.

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?

Submitted by Anonymous on 11 March, 2010 - 22:46.

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.

Copy Values from Excel to Word

Submitted by Vishesh on 20 March, 2010 - 03:43.

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

Transpose

Submitted by Nick on 20 January, 2010 - 10:40.

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

Transpose - How about this ?

Submitted by Vishesh on 20 January, 2010 - 15:34.

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

Submitted by Nick on 26 January, 2010 - 10:32.

that works well... fast too.

nice one