跳到主要内容

在 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 数据类型。

参数

名称 必需/可选 数据类型 说明
Paste 可选 XlPasteType 要粘贴的区域部分,例如 xlPasteAll 或 xlPasteValues
Operation 可选 XlPasteSpecialOperation 粘贴操作,例如 xlPasteSpecialOperationAdd
SkipBlanks 可选 Variant 如果为 True,则不将剪贴板上区域中的空白单元格粘贴到目标区域中。默认值为 False
Transpose 可选 Variant 如果为 True,则表示在粘贴区域时转置行和列。默认值为 False

XlPasteType 常量

指定要粘贴的区域部分。

名称 说明
xlPasteAll -4104 粘贴全部内容。但不包括列宽。
xlPasteAllExceptBorders 7 粘贴除边框外的全部内容。
xlPasteAllMergingConditionalFormats 14 将粘贴所有内容,并且将合并条件格式。
xlPasteAllUsingSourceTheme 13 使用源主题粘贴全部内容。
xlPasteColumnWidths 8 粘贴复制的列宽。
xlPasteComments -4144 粘贴批注。
xlPasteFormats -4122 粘贴复制的源格式。
xlPasteFormulas -4123 粘贴公式。
xlPasteFormulasAndNumberFormats 11 粘贴公式和数字格式。
xlPasteValidation 6 粘贴有效性。
xlPasteValues -4163 粘贴值。
xlPasteValuesAndNumberFormats 12 粘贴值和数字格式。

XlPasteSpecialOperation 常量

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

名称 说明
xlPasteSpecialOperationAdd 2 将目标单元格中的值与复制的数据相加。
xlPasteSpecialOperationDivide 5 将目标单元格中的值除以复制的数据。
xlPasteSpecialOperationMultiply 4 将目标单元格中的值与复制的数据相乘。
xlPasteSpecialOperationNone -4142 粘贴操作中不执行任何计算。
xlPasteSpecialOperationSubtract 3 将目标单元格中的值减去复制的数据。

示例 1:粘贴值

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

示例 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 的乘积。

原数据

1
2
A B C D
1 2 3 3
4 5 6

VBA 代码

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

效果

1
2
A B C D
3 6 9 3
12 15 18

示例 6:相除

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

原数据

1
2
A B C D
3 9
6

VBA 代码

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

效果

1
2
A B C D
3 3
2

示例 7:相加

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

原数据

1
2
3
A B C D
3 9
7
8

VBA 代码

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

效果

1
2
3
A B C D
3 12
10
11

示例 8:相减

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

原数据

1
2
3
A B C D
3 9
7
8

VBA 代码

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

效果

1
2
3
A B C D
3 6
4
5

示例 9:转置

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

原数据

1
2
A B C D
1
4

VBA 代码

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

效果

1
2
A B C D
1 1 4
4

示例 10:跳过空单元格

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

原数据

1
2
3
4
5
A B C D
1 0 0
0 0
3 0 0
0 0
5 0 0

VBA 代码

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

效果

1
2
3
4
5
A B C D
1 1 1
0
3 3 3
0
5 5 5

评论

您的电子邮件地址不会显示出来。*号为必填项。