在 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 行。
如果将行复制到目标,则目标单元格区域必须是 A
列中的行或单元格。
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
如果将行复制并插入到目标,则目标单元格区域必须是 A
列中的行或单元格。
示例 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
要取消复制单元格周围的动画,请添加以下代码:
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
效果
|
|