VBA Cut, Copy, Paste and PasteSpecial with Examples

Part of the free Module 12: Excel VBA Course · Lesson 12 of 18 · Full Excel course

Cut, Copy and Paste in VBA move or duplicate ranges without touching the keyboard: Range.Cut and Range.Copy accept a Destination, PasteSpecial pastes only values, formats or formulas, and Insert shifts existing cells to make room. In this lesson you will learn how to cut, copy and paste in Excel VBA within a sheet, to another sheet or workbook, transpose data and insert cut or copied cells.

Table of Contents

How copying works in VBA

With a Destination argument, Cut and Copy move data directly and never use the Windows clipboard, which is faster and leaves the user’s clipboard untouched. Without a destination the range goes to the clipboard, and you then call PasteSpecial or Insert on the target range. When only values are needed, skip copying altogether and assign target.Value = source.Value; it is the fastest method of all. Always clear the marching ants afterwards with Application.CutCopyMode = False.

Cut and paste

The Sales figures in C4:C12 are moved to column F.

Sub Cut_Paste()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("C4:C12").Cut Destination:=sh.Range("F4")
End Sub

Cut with Destination moves values, formulas and formats in one step and leaves the source cells empty.

Copy and paste

Used range copied and pasted at F1 with VBA — Copy the used range and paste at F1

Sub Copy_Paste()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.Copy Destination:=sh.Range("F1")
    sh.Range("F1:H1").EntireColumn.AutoFit
End Sub

Copy with Destination duplicates the whole used range starting at F1 and the AutoFit line tidies the new columns.

Copy to another worksheet

Sub Copy_To_Another_Worksheet()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Sheet1")
    Set sh2 = ThisWorkbook.Sheets("Sheet2")
    sh1.UsedRange.Copy Destination:=sh2.Range("A1")
End Sub

Source and destination are on different sheets; no sheet needs to be selected or activated.

Copy to another workbook

Sub Copy_To_Another_Workbook()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = ThisWorkbook.Sheets("Sheet1")
    Set sh2 = Workbooks("Book1.xlsx").Sheets("Sheet1")   'must already be open
    sh1.UsedRange.Copy Destination:=sh2.Range("A1")
End Sub

The same statement works across files as long as the target workbook is open; combine it with Workbooks.Open from the Workbooks chapter for a full import routine.

Paste Special

Sub Paste_Special()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1:A10").Copy
    sh.Range("D1").PasteSpecial xlPasteValues                 'values only
    sh.Range("D1").PasteSpecial xlPasteFormats                'formatting only
    sh.Range("D1").PasteSpecial xlPasteFormulas               'formulas only
    sh.Range("D1").PasteSpecial xlPasteComments               'notes
    sh.Range("D1").PasteSpecial xlPasteValuesAndNumberFormats
    sh.Range("D1").PasteSpecial xlPasteValidation             'data validation
    Application.CutCopyMode = False
End Sub

Copy the source once, then paste as many aspects as you need; the constants match the options in the Paste Special dialog.

Sub Paste_Special_Transpose()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("A1:A10").Copy
    sh.Range("D1").PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False
End Sub

Transpose:=True turns the column A1:A10 into the row D1:M1.

Insert cut or copied cells

Column C must move to become column A, shifting the other columns right.

Data table where column C will be moved to column A using insert cut cells in VBA — Column C to be moved to column A

Sub Insert_Cut_Cells()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("C:C").Cut
    sh.Range("A1").Insert
End Sub

Cut without a destination places the column on the clipboard and Insert pushes it in at A, exactly like right-click Insert Cut Cells.

Result after column C has been moved to column A — Column C moved to column A

To copy rather than move the column into position, replace Cut with Copy.

Data table where column C will be copied into column A using insert copied cells — Column C to be copied into column A

Sub Insert_Copied_Cells()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.Range("C:C").Copy
    sh.Range("A1").Insert
    Application.CutCopyMode = False
End Sub

The original column stays in place and a duplicate is inserted at A.

Result after column C has been copied into column A — Column C copied into column A

Complete example: append new records to a master sheet

Sub Append_To_Master()
    Dim src As Worksheet, dest As Worksheet
    Dim lastSrc As Long, nextDest As Long

    Set src = ThisWorkbook.Sheets("Entry")
    Set dest = ThisWorkbook.Sheets("Master")

    lastSrc = src.Cells(src.Rows.Count, "A").End(xlUp).Row
    If lastSrc < 2 Then Exit Sub
    nextDest = dest.Cells(dest.Rows.Count, "A").End(xlUp).Row + 1

    'values only, no clipboard
    dest.Cells(nextDest, 1).Resize(lastSrc - 1, 6).Value = src.Range("A2").Resize(lastSrc - 1, 6).Value

    'copy formats from the master's first data row
    dest.Rows(2).Copy
    dest.Rows(nextDest).Resize(lastSrc - 1).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    src.Range("A2").Resize(lastSrc - 1, 6).ClearContents
    MsgBox lastSrc - 1 & " records appended", vbInformation
End Sub

This runnable macro transfers values with a direct assignment, copies only the formatting from an existing row, clears the entry sheet and reports the count.

Tips and common mistakes

Use Destination or Value assignment whenever possible; clipboard pastes are slower and fail if another program grabs the clipboard.
PasteSpecial needs a prior Copy. Error 1004 “PasteSpecial method of Range class failed” means the clipboard is empty.
Do not Select before copying. Range("A1").Select: Selection.Copy is recorder style; write Range("A1").Copy.
Cut cannot be used with PasteSpecial; use Cut with Destination, or Copy then delete the source.
Merged cells must match in size at the destination or the paste fails.

Practice and real-world use

Write a macro that copies the visible rows of a filtered table to a new sheet as values, then one that transposes a monthly column into a header row. Data consolidation, archive routines and report snapshots are all built from these copy and paste commands.

Watch the step-by-step video tutorial

Click here to download the practice file.

Related lessons

Frequently asked questions

How do I copy only values in VBA?

Either assign directly, target.Value = source.Value, or copy the source and use target.PasteSpecial xlPasteValues. The direct assignment is faster and does not use the clipboard.

How do I copy data to another workbook in VBA?

Open or reference the target workbook, then use source.Copy Destination:=Workbooks(“Target.xlsx”).Sheets(“Sheet1”).Range(“A1”). Both workbooks must be open in the same Excel instance.