Excel VBA: CopyUsedRangeValues – fastest way to copy values from sheet to sheet.

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 (438 downloads )