andy-h
3/22/2017 - 8:36 PM

Three ways to copy & paste in Excel VBA. Be aware that `Range.Copy` and `Range.PasteSpecial` use the clipboard, so avoid copying anything el

Three ways to copy & paste in Excel VBA. Be aware that Range.Copy and Range.PasteSpecial use the clipboard, so avoid copying anything elsewhere (i.e., the desktop, file explorer, or any other application) while a macro is running that uses those methods.

'Be aware that `Range.Copy` and `Range.PasteSpecial` use the clipboard.

'copy everything (values, formats, comments, et al.)
Private Sub CopyPasteAll(fromRange As Range, toRange As Range)
    
    fromRange.Copy toRange
    
End Sub

'copy values only (without using the clipboard)
Private Sub CopyPasteValues(fromRange As Range, toRange As Range)
    
    Dim rows As Integer, columns As Integer, toCell As Range
    
    rows = fromRange.rows.Count
    columns = fromRange.columns.Count
    
    Set toCell = toRange.Cells(1, 1)
    Set toRange = toCell.Worksheet.Range(toCell, toCell.Offset(rows - 1, columns - 1))
    
    toRange.Value = fromRange.Value
    
End Sub

'use Paste Special to be more specific
Private Sub CopyPasteSpecial(fromRange As Range, toRange As Range, _
 Optional pasteType As XlPasteType = xlPasteAll, _
 Optional pasteOperation As XlPasteSpecialOperation = xlPasteSpecialOperationNone, _
 Optional skipBlanks As Boolean = False, _
 Optional transpose As Boolean = False)
    
    fromRange.Copy
    Call toRange.PasteSpecial(pasteType, pasteOperation, skipBlanks, transpose)
    
End Sub