在 VBA 中复制和粘贴单元格区域

在 Excel VBA 中最常见操作之一便是复制和粘贴一系列数据。手动执行复制和粘贴非常容易，在 Excel 中，我们使用 Ctrl+C 复制所选数据，然后使用 Ctrl+V 将所选数据粘贴到目标单元格中。通过 VBA 复制和粘贴也同样容易。

Range.Copy 方法

Range.Copy 方法是一种非常方便的方法，可以将单元格区域复制到目标位置或者是剪贴板。所有格式和公式都将被复制和粘贴。

语法

expression.Copy (Destination)

Destination 参数是可选的。如果省略此参数，Excel 会将单元格区域复制到剪贴板。它返回 Variant 数据类型。

示例 1：复制并粘贴到另一个工作表

下面的代码将工作表 Sheet1 的单元格 A1 复制到工作表 Sheet2 的单元格 B1。

Sheets("Sheet1").Range("A1").Copy (Sheets("Sheet2").Range("B1"))

如果要复制单元格区域，您只需指定目标位置左上角的单元格。

Sheets("Sheet1").Range("A1:B1").Copy Sheets("Sheet2").Range("B1")

示例 2：复制行

以下示例代码将工作表 Sheet1 的第 1 到 2 行复制到工作表 Sheet2 的第 6 行。

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Range("A6"))

或者

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:6"))

或者

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:7"))

示例 3：复制整行并插入复制行

以下示例代码将活动工作表的第 1  行复制并插入到第 3 行。

Range("A1").EntireRow.Copy
Range("A3").Insert

示例 4：复制列

以下示例代码将 Sheet1 的 A:B 列复制到 Sheet2 的 B:C 列（因为它粘贴在 B1）。

Sheets("Sheet1").Columns("A:B").Copy Sheets("Sheet2").Range("B1")

示例 5：复制多重单元格区域到新工作簿

如果复制的行（或列）彼此不相邻，建议设置一个变量以组合这些行（或列）。

以下示例代码将列 A:B 和 列 E:F 赋值给变量 range1 然后粘贴到新工作簿的 A1。

Sub CopyToNewWorkbook()
     Set range1 = Range("A:B, E:F")
     range1.Copy
     Set newbook = Workbooks.Add
     Range("A1").PasteSpecial
 End Sub

示例 6：省略目标参数

以下示例代码检查 Sheet1 上每一行的 D 列中的值。如果 D 列中的值等于 A，则将整行复制到 SheetA 的下一个空行中。如果该值等于 B，则将该行复制到 SheetB 上。注意，代码未优化。

Sub CopyRows()
    Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop through each row
    For i = 2 To FinalRow
        ' Decide if to copy based on column D
        ThisValue = Cells(i, 4).Value
        If ThisValue = "A" Then
            Cells(i, 1).Resize(1, 33).Copy
            Sheets("SheetA").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        ElseIf ThisValue = "B" Then
            Cells(i, 1).Resize(1, 33).Copy
            Sheets("SheetB").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        End If
    Next i
End Sub

Range.PasteSpecial 方法

如果你想用 VBA 在 Excel 中复制公式并将其粘贴为值，您可以使用 PasteSpecial 方法。

语法

expression.PasteSpecial (Paste, Operation, SkipBlanks, Transpose)

它返回 Variant 数据类型。

参数

XlPasteType 常量

指定要粘贴的区域部分。

XlPasteSpecialOperation 常量

指定如何使用工作表上的目标单元格计算数值数据。

示例 1：粘贴值

Sub PasteSpecialValues()
    Sheets("Sheet1").Range("A1:D5").Copy
    Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteValues
    '取消复制单元格周围的动画
    Application.CutCopyMode = False
End Sub

Application.CutCopyMode = False

示例 2：粘贴格式

Sheets("Sheet1").Range("A1:D5").Copy
Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteFormats

示例 3：粘贴公式

Sheets("Sheet1").Range("A1:D5").Copy
Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteFormulas

示例 4：整行复制和粘贴

以下示例代码将工作表 Sheet1 的第 3 行复制到第 8 行（PasteSpecial 方法省略参数，格式也会一并粘贴，但不包括列宽）。

Sub EntireRowCopy()
    'Paste the data of row 3 in row 8
    Worksheets("Sheet1").Activate
    Range("A3").EntireRow.Copy
    'PasteSpecial Method without argument copies the format as well.
    Range("A8").EntireRow.PasteSpecial
End Sub

示例 5：相乘

以下示例将单元格区域 A1:C2 中的数据替换为现有内容和单元格 D1 的乘积。

原数据

VBA 代码

Sub pasteMultiply()
    Range("D1").Copy  'D1 contains value 3
    Range("A1:C2").PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End Sub

效果

示例 6：相除

以下示例将单元格区域 C1:C2 中的数据替换为现有内容与单元格 A1 相除的值。

原数据

VBA 代码

Sub pasteDivide()
    Range("A1").Copy  'A1 contains value 3
    Range("C1:C2").PasteSpecial Operation:=xlPasteSpecialOperationDivide
End Sub

效果

示例 7：相加

以下示例将单元格区域 C1:C3 中的数据替换为现有内容与单元格 A1 相加的和。

原数据

VBA 代码

Sub pasteAdd()
    Range("A1").Copy  'A1 contains value 3
    Range("C1:C3").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End Sub

效果

示例 8：相减

以下示例将单元格区域 C1:C3 中的数据替换为现有内容与单元格 A1 相减的差。

原数据

VBA 代码

Sub pasteSubtract()
    Range("A1").Copy  'A1 contains value 3
    Range("C1:C3").PasteSpecial Operation:=xlPasteSpecialOperationSubtract
End Sub

效果

示例 9：转置

以下示例将单元格区域 A1:A2 中的数据复制到 C1 并转置。

原数据

VBA 代码

Sub PasteSpecialTranspose()
    With Worksheets("Sheet1")
        .Range("A1:A2").Copy
        .Range("C1").PasteSpecial Transpose:=True
    End With
End Sub

效果

示例 10：跳过空单元格

以下示例将单元格区域 A1:A5 中的数据复制到 B1 并跳过空单元格，复制到 C1 不跳过空单元格。

原数据

VBA 代码

Sub PasteSpecialSkipBlanks()
    With Worksheets("Sheet1")
        .Range("A1:A5").Copy
        .Range("B1").PasteSpecial SkipBlanks:=True
        .Range("C1").PasteSpecial SkipBlanks:=False
    End With
End Sub

效果