Excel VBA Tip - Bypassing Clipboard while copying values or formula in Excel
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.
- hivishy's blog
- Add new comment
- 1891 reads
Excellent post.
I have always found this annoying, but never thought there was a good way to do it.
Thanks for sharing this.
Transpose
Have you got a smart way to do the transpose of this too ?
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
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.
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
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
Copy Values
Try this:
Call CopyValues(Thisworkbook.Worksheets("OpenOrders").Range("A:K"), Workbooks("ReconcileFile.xls").Worksheets("GIMII").Range("A1"))