Runtime Error 1004: “The information cannot be pasted because the copy area and the paste area are not the same size.”

VBA throws Runtime Error 1004: “The information cannot be pasted because the copy area and the paste area are not the same size” when a .Paste or .PasteSpecial command attempts to transfer a source range into a destination range that has an incompatible shape or contains merged cells.

Quick Answer

To resolve this error, specify only the top-left cell of the destination range instead of selecting the entire target block, or unmerge any merged cells within the destination area. Changing Range("A1:C10").PasteSpecial to Range("A1").PasteSpecial eliminates dimension mismatches immediately.

---

Why Dimension Mismatches Occur

When copying and pasting in Excel, the destination range must follow strict dimensional rules:

1. It must match the source dimensions exactly (e.g., copying a 3×5 block requires a 3×5 target), OR
2. It must be defined as a single cell representing the top-left origin (e.g., cell A1).

If your code explicitly defines a multi-cell destination area that does not align with the source range dimensions, Excel rejects the paste.

' Fails with Error 1004:
Worksheets("Sheet1").Range("A1:B5").Copy ' Size: 5 rows x 2 columns (10 cells)
Worksheets("Sheet2").Range("D1:D10").PasteSpecial xlPasteValues ' Size: 10 rows x 1 column

---

How to Fix the Error

Fix 1: Target the Top-Left Cell Only (Best Practice)

When using .PasteSpecial or the Destination parameter of .Copy, you never need to define the entire landing block. Excel automatically calculates the boundary based on the copied source.

Sub CopyValuesCorrectly()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDest = ThisWorkbook.Worksheets("Report")

    ' Direct copy method:
    wsSource.Range("A1:C50").Copy Destination:=wsDest.Range("A1")

    ' PasteSpecial method:
    wsSource.Range("A1:C50").Copy
    wsDest.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Fix 2: Remove Merged Cells in the Destination

Merged cells alter the structural geometry of the grid. If the copied range contains single cells, but the target destination contains cells merged together (such as B2:C2 merged), Excel cannot map individual source values into the merged partition.

• Symptom: Pasting to a single top-left cell still produces this exact 1004 message.
• Diagnosis: Check if any cells in the target area are merged:

If DestinationRange.MergeCells Then
    MsgBox "Merged cells detected in paste target."
End If

• Resolution: Unmerge the destination range before pasting:

wsDest.Range("A1:Z100").UnMerge
wsSource.Range("A1:C50").Copy Destination:=wsDest.Range("A1")

If merged cells frequently disrupt your automated templates, read #N/A in Lookup Tables with Merged Cells and #SPILL! Error: The “Merged Cell” Blockage.

Fix 3: Direct Memory Transfer (Bypass the Clipboard)

If you only need to transfer raw values (not formats or formulas), skip .Copy and .Paste entirely. Assigning values directly between equal-sized arrays or ranges is faster and completely avoids clipboard size errors.

Sub DirectTransfer()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim rowCount As Long, colCount As Long

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDest = ThisWorkbook.Worksheets("Report")

    rowCount = 50
    colCount = 3

    ' Direct memory assignment:
    wsDest.Range("A1").Resize(rowCount, colCount).Value = _
        wsSource.Range("A1").Resize(rowCount, colCount).Value
End Sub