RunCopyUsedRangeValues will copy the values from the sheet with codename shSource to shDestination. It is extremely fast, and very useful when I don’t need formatting or formulas.
You can download the Excel workbook at the bottom of the article
Usage example
The source and destination sheets can be in the same or in separate workbooks
'Testing CopyUsedRangeValues
Sub RunCopyUsedRangeValues()
CopyUsedRangeValues shSource, shDestination, True
End Sub
CopyUsedRangeValues code
Sub CopyUsedRangeValues(sh1 As Worksheet, sh2 As Worksheet, Clear As Boolean)
'Clear destination sheet
If Clear = True Then
sh2.Cells.Clear
End If
Dim rng1 As Range
Dim rng2 As Range
Dim d As Boolean
'Remove =='s
With sh1
d = .UsedRange.Replace(what:="==", replacement:="", lookat:=xlPart)
Set rng1 = .UsedRange
End With
'Set rng2 size equal to rng1
With sh2
Set rng2 = .Range(.Cells(1, 1), .Cells(rng1.Rows.Count, rng1.Columns.Count))
End With
'Assign values from source range to destination range
rng2.Value = rng1.Value
End Sub
Why?!
Why remove double equal signs? It’s just to make it work. The routine suddenly failed, and the use of a bunch of equal signs as separator in a .csv-file was the problem. So just had to remove them.
'Remove =='s
With sh1
d = .UsedRange.Replace(what:="==", replacement:="", lookat:=xlPart)
Set rng1 = .UsedRange
End With
Download the Excel Workbook: FastestWaySoFarToCopyValues (393 downloads )