常见的复制粘贴，VBA是怎么做的

标签：VBA

引言：本文学习整理自powerspreadsheets.com，非常清晰透彻地讲解如何使用VBA进行复制粘贴操作。

复制粘贴可能是Excel中最常见的操作，不然Microsoft怎么会把它们放置在最显眼显顺手的位置。当然，使用Excel VBA时，复制操作单元格区域是一项基本技能。

本文主要讲解使用VBA复制粘贴单元格区域的几种不同方法。

预备

下面的所有示例都假设示例工作簿处于活动状态，并且整个操作都发生在活动工作簿上。此外，它们被设计为从特定的源工作表复制到该示例工作簿中的另一个目标工作表。

通过调整对象引用的构建方式，可以轻松修改这些行为。例如，通过限定指定目标单元格区域的对象引用，可以将单元格区域复制到其他工作表或工作簿。

例如，在本文包含的VBA代码示例中，源数据所在的单元格区域引用如下所示：

Worksheets(“Sample Data”).Range(“B5:M107”)

这个引用不是完全限定的对象引用。更准确地说，它假定复制和粘贴操作发生在活动工作簿中。

下面的引用等同于上述引用，但完全限定：

Workbooks(“Book1.xlsm”).Worksheets(“SampleData”).Range(“B5:M107”)

这个完全限定的引用并没有假定Book1.xlsm是活动工作簿。因此，无论哪个Excel工作簿处于活动状态，引用都能正常工作。

Excel功能区中的复制命令

在使用VBA代码复制单元格区域之前，看看Excel功能区中的“复制”按钮命令。“复制”按钮是一个拆分按钮，也就是说，可以直接单击该按钮执行复制操作（如下图1所示），也可以单击其右侧下拉箭头，会显示2个命令（如下图2所示）。

图1

图2

在VBA中，如果使用常规的“复制”命令，则使用Range.Copy方法；如果使用“复制为图片”命令，则使用Range.CopyPicture方法。

Excel VBA使用Range.Copy方法复制粘贴

Range.Copy方法的主要用途是复制特定的单元格区域。

在Excel中手工复制单元格区域操作时，使用Ctrl+C快捷键，该单元格区域被复制到剪贴板。在VBA中，使用Range.Copy方法做同样的事情。

并且，Copy方法提供了一个额外选项：将选定区域复制到另一个区域。可以通过适当地使用Destination参数来实现。

换句话说，可以使用Range.Copy用于将单元格区域复制到以下任一位置：剪贴板；某单元格区域。

Range.Copy方法的语法

Range.Copy方法的基本语法为：

expression.Copy(Destination)

其中，expression代表要复制的Range对象的变量的占位符。

Copy方法的唯一参数是Destination。此参数是可选的，允许指定将想复制区域复制到的地点。如果省略该参数，则复制的区域仅复制到剪贴板。

这意味着Copy方法应使用合适的语法（取决于实际目的）如下所示：

1.要复制Range对象到剪贴板，忽略参数Destination。这种情形使用下列语法：

expression.Copy

2.要复制Range对象到另一个区域（目标区域），使用参数Destination来指定目标区域。这种情形使用下列语法：

expression.Copy(Destination)

示例工作簿

对于这个特定的示例，创建了如下图3所示的表。此表显示了100名不同销售经理按单位数和总美元价值对特定项目（A、B、C、D和E）的销售额。第一行（主表上方）显示每个项目的单价，最后一列显示每位经理的销售总值。

图3

注：可以到powerspreadsheets.com或者知识星球完美Excel社群下载示例工作簿。

示例1：复制单元格区域到剪贴板

首先，让我们看看如何将示例工作表（表和单价）中的所有项目复制到剪贴板。代码只有一行：

Sub Copy_to_Clipboard()
    Worksheets("SampleData").Range("B5:M107").Copy
End Sub

示例2：复制单元格区域到目标区域

下面的简单过程在示例1的基础上添加了Destination参数，将工作表“Sample Data”中的单元格区域B5:M107复制到工作表“Example 2 - Destination”中的列B至列M中：

Sub Copy_to_Range()
    Worksheets("Sample Data").Range("B5:M107").Copy_
       Destination:=Worksheets("Example 2 -Destination").Range("B5:M107")
    Worksheets("Example 2 -Destination").Columns("B:M").AutoFit
End Sub

注意到，该过程中还有一条语句，使用Range.AutoFit方法调整数据至合适的列宽。

何时复制到剪贴板，何时使用Destination参数

如果可以在不复制到剪贴板的情况下实现目的，那么简单地使用Range.Copy的Destination参数即可。通常，使用Destination参数比复制到剪贴板然后使用Range.PasteSpecial或者Worksheet.Paste更有效。复制到剪贴板并粘贴（使用Range.PasteSpecial或Worksheet.Paste方法）需要两个步骤：复制；粘贴。这两个步骤的处理通常：增加过程的内存需求；导致（稍微）低效的过程。

如果其他应用程序同时使用剪贴板，尽可能避免使用剪贴板可能是降低数据丢失或信息泄漏风险的好办法。

然而，使用带有参数Destination的Range.Copy方法可能不是最合适的解决方案。为了确定Destination参数何时允许你达到所需目的，了解Range.Copy方法是如何工作的非常重要，特别是它可以（和不能）做的事情。以一个例子来说明：

如果查看示例1（复制到剪贴板）和示例2（复制到目标区域）的结果，会注意到目标工作表与源工作表看起来几乎相同。换句话说，Excel复制并粘贴全部（值、公式、格式）。在某些情况下，这正是你想要的。然而，在其他情况下，这是你不想要的。例如，下面的过程：

Sub Copy_to_Range()
    Worksheets("SampleData").Range("B5:M107").Copy _
       Destination:=Worksheets("Example 2 -Destination").Range("B1")
    Worksheets("Example 2 -Destination").Columns("B:M").AutoFit
End Sub

将上文的Copy_to_Range过程的Destination参数进行了修改，使用了Range("B1")。执行过程后，你会看到有点不对劲。显然，所有商品的总销售额都不准确。

图4

原因是，在原始表中，使用了混合引用，以引用项目的单价。例如，Sarah Butler（表中第一位销售经理）计算A项目总销售额的公式：

图5

只要目标单元格与源单元格完全相同，这些公式就不是问题。上述示例1和示例2中都是这种情况，其中，尽管工作表发生了更改，目标仍然是单元格B5到M107。这保证了混合引用继续指向正确的单元格。

然而，一旦目标单元格区域发生更改（如上面的示例所示），原始的混合引用就会对工作表造成严重破坏。例如，看看销售经理Walter Perry（表中第二位）计算B项目总销售额时使用的公式：

图6

该公式不使用项目B（显示在单元格F1中）的单价来计算销售额。相反，它使用单元格F5作为从源工作表复制的混合引用的结果。这将导致（i）错误的结果和（ii）循环引用。

在这种（和其他类似）情况下，可能不希望仅依赖带有Destination参数的Range.Copy方法。换句话说：在某些情况下，不希望复制和粘贴源单元格区域的所有内容。例如，在某些情况下，可能希望：复制包含公式的单元格区域，并在目标单元格区域粘贴值。

这正是上面例子中发生的情况。在这种情况下，可能只希望粘贴值（无公式）。

为了在使用VBA时控制在特定目标单元格区鞓内复制的内容，必须了解Range.PasteSpecial方法。

使用Range.PasteSpecial来复制粘贴

通常，每当想要控制Excel在特定目标区域内复制的内容时，都依赖于“选择性粘贴”选项。可以通过“选择性粘贴”对话框访问这些选项。

图7

使用VBA时，通常依赖于Range.PasteSpecial方法来控制目标区域内复制的内容。

一般来说，Range.PasteSpecial方法允许将特定的Range对象从剪贴板粘贴到相关目标区域。Range.PasteSpecial方法的强大来源于其参数，它们允许进一步确定Excel执行粘贴的方式。

Range.PasteSpecial方法的语法

Range.PasteSpecial方法的基本语法为：

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

其中，expression代表Range对象；其4个参数均可选。

注意，这些参数对应图7“选择性粘贴”对话框的各个部分和选项，“粘贴链接”按钮除外。

参数Paste允许指定实际粘贴的内容，大致相当于“选择性粘贴”对话框中的“粘贴”部分。例如，此参数允许指定仅将值（或公式）粘贴到目标区域中。

Paste参数可以采用XlPasteType枚举中指定的12个值中的任意一个：xlPasteAll（或-4104），粘贴全部；xlPasteFormulas（或-4123），粘贴公式；xlPasteValues（或-4163），粘贴值；xlPasteFormats（或-4122），复制源格式；xlPasteComments（或-4144），粘贴批注；xlPasteValidation（或6），粘贴数据验证；xlPasteAllUsingSourceTheme（或13），结果是（i）粘贴所有内容，和（ii）使用源主题；xlPasteAllExceptBorders（或7），粘贴除边框外所有内容；xlPasteColumnWidths（或8），粘贴列宽；xlPasteFormulasAndNumberFormats（或11），粘贴（i）公式和（ii）数字格式；xlPasteValuesAndNumberFormats（或12），粘贴（i）值和（ii）数字格式；xlPasteAllMergingConditionalFormats（或14），（i）粘贴所有内容和（ii）合并条件格式。

Operation参数允许指定是否对目标单元格执行数学运算，此参数大致相当于“选择性粘贴”对话框的“运算”部分。可以接受XlPasteSpecialOperation枚举中的任意值：xlPasteSpecialOperationNone（或-4142），表示粘贴时不执行运算；xlPasteSpecialOperationAdd（或2），将复制的数据与目标单元格中的值相加；xlPasteSpecialOperationSubtract（或3），表示从目标单元格中的值中减去复制的数据；xlPasteSpecialOperationMultipy（或4），将复制的数据与目标单元格中的值相乘；xlPasteSpecialOperationDivide（或5），将目标单元格内的值除以复制的数据。

SkipBlanks参数指定复制区域中的空白单元格是否应粘贴在目标区域内，可以设置为True或Flase。如果SkipBlanks设置为True，则被复制的单元格区域中的空单元格不会粘贴到目标单元格区域；如果设置为False，则粘贴空单元格。该参数默认值为False。如果忽略该参数，则会在目标区域中粘贴空单元格。

Transpose参数允许指定粘贴时是否转置复制区域的行和列（交换位置），可以设置为True或Flase。如果Transpose设置为True，粘贴时行列转置；如果设置为False，Excel不会转置任何内容。该参数默认值为False。如果忽略该参数，Excel不会转置复制区域的行和列。

示例3：复制并选择性粘贴

下面以示例说明Range.PasteSpecial方法：

Sub Copy_PasteSpecial()
    Worksheets("SampleData").Range("B5:M107").Copy
    Worksheets("Example 3 -PasteSpecial").Range("B5").PasteSpecial _
       Paste:=xlPasteValuesAndNumberFormats, _
       Operation:=xlPasteSpecialOperationNone, _
        SkipBlanks:=False, _
        Transpose:=True
    Worksheets("Example 3 -PasteSpecial").Columns("B:CZ").AutoFit
End Sub

使用Range.Copy方法将要复制的单元格区域复制到剪贴板（如上所示），可在过程的末尾使用语句“Application.CutCopyMode=False”，可以取消剪切或复制模式并删除移动的边框。

使用PasteSpecial方法粘贴，其中参数Paste:=xlPasteValuesAndNumberFormats表示只粘贴值和数字格式，参数Operation:=xlPasteSpecialOperationNone表明在执行粘贴操作时不执行运算，参数SkipBlanks:=False，即取默认值，表明粘贴空格，参数Transpose:=True表示在粘贴时行列转置。

<未完待续>