Skip to content

Instantly share code, notes, and snippets.

@wizard04wsu
Last active March 27, 2017 00:24
Show Gist options
  • Save wizard04wsu/edef38207f6c115bc07e7e1fae722987 to your computer and use it in GitHub Desktop.
Save wizard04wsu/edef38207f6c115bc07e7e1fae722987 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment