正如在上篇文章中谈到的,复制、剪切、粘贴是Excel中最常用的操作,尤其是粘贴功能。本文将详细介绍如何在VBA中编写粘贴代码。
在下图所示的工作表中执行代码: Range('A1').Copy Range('A3').PasteSpecial 其结果如图中下方所示。 也可以直接使用Copy方法: Range('A1').Copy Range('A3') 达到同样的效果。唯一不同的是,使用PasteSpecial方法后,原数据单元格四周虚框不会消失,而使用Copy方法则没有虚框。
理解选择性粘贴和PasteSpecial方法 既然结果相同,为什么还要使用PasteSpecial方法呢?我们在Excel中单击“粘贴”按钮下方的小箭头,如下图所示: 可以看出,“粘贴”功能很强大,可以实现多种形式的粘贴。这就是PasteSpecial的一大优势,其实就是用VBA实现Excel的“选择性粘贴”功能。。
我们来看看PasteSpecial方法的语法: Range对象.PasteSpecial(Paste,Operation,SkipBlanks,Transpose) 说明:
此外,还有一个xlPasteAllMergingConditionalFormats常量,粘贴全部内容且合并条件格式(注:本文使用的是Excel 2007版本)。
为方便理解,再将上图详细解释如下: 全部:粘贴所有内容,包括文本、格式、边框、公式、批注等,相当于直接粘贴。 公式:只粘贴文本和公式,不粘贴其它如格式、边框、批注等。如果复制的单元格中的公式相对引用了其它单元格,那么粘贴的单元格相应引用的单元格的相对位置与源公式相同。 数值:只粘贴数值,尽管所复制的单元格中的内容是公式计算的结果。 格式:只粘贴格式,不改变粘贴单元格内容。 批注:只粘贴所复制单元格的批注,不改变粘贴单元格内容和格式。 有效性验证:只粘贴有效性验证的内容。 边框除外:粘贴除边框外的所有内容。 列宽:使粘贴单元格与所复制的单元格列宽相同。 公式和数字格式:只粘贴公式和数字格式。 值和数字格式:粘贴值并带数字格式。 有兴趣的朋友可以在Excel中随意设置数据,使用不同的选项粘贴,看看实际效果。
当在粘贴中选格式、批注或有效性验证时,运算中的选项不可用。 运算中的选项可用来实现单元格区域中的数据都加、减、乘、除一个数,或者两个单元格区域之间的加减乘除的计算。
跳过空单元格:粘贴时,复制的单元格区域中为空的单元格不会覆盖粘贴区域相应单元格。 转置:行与列互换。
粘贴链接:粘贴后,所复制的单元格内容改变,则粘贴的单元格也相应改变,即两个单元格保持一致。
示例1:只粘贴格式而不粘贴值 如图所示,只是将单元格区域C2:C4中的格式粘贴到单元格区域E2:E4。 代码如下: Sub testPasteSpecial1() Range('C2:C4').Copy Range('E2').PasteSpecialPaste:=xlPasteFormats End Sub
示例2:只粘贴值 有时,我们只需要粘贴的结果只单纯的值,而不需要其它,如下图所示。 代码如下: Sub testPasteSpecial2() Range('C2:C4').Copy Range('F2').PasteSpecialPaste:=xlPasteValues End Sub
示例3:粘贴值并保持列宽 使用语句将单元格区域A1:A3中的值复制到单元格区域C1:C3中: Range('A1:A3').Copy Range('C1') 此时,如果列A中的单元格已设置为合适的列宽,复制到C列中后并没有合适的列宽容纳复制过来的 值,也就是说,有些单元格的内容可能会延至单元格区域外,如下图所示:
有时,我们需要在粘贴的区域保持原始数据的列宽。使用PasteSpecial方法并设置Paste参数,可以复制值并保持列宽。代码如下: Sub testPasteSpecial3() Range('A1:A3').Copy Range('C1').PasteSpecialPaste:=xlPasteColumnWidths Range('C1').PasteSpecialPaste:=xlPasteValues End Sub 运行代码后的效果如图:
也可以将PasteSpecial方法与Copy方法结合: Sub testPasteSpecial4() Range('A1:A3').Copy Range('C1').PasteSpecialPaste:=xlPasteColumnWidths Range('A1:A3').CopyRange('C1') End Sub
示例4:对粘贴的区域进行运算 如图所示,需要对单元格区域A1:A3中的值同时乘以3。 代码如下: Sub testPasteSpecial5() Range('C1').Copy Range('A1:A3').PasteSpecialOperation:=xlPasteSpecialOperationMultiply End Sub
示例5:将行列转置 有时,我们需要将所复制的单元格区域的行列互换,如下图所示: 代码如下: Sub testPasteSpecial6() Range('A1:A3').Copy Range('C1').PasteSpecialTranspose:=True End Sub
陷阱 1.使用PasteSpecial方法时剪贴板中没有数据 经常会看到有人提出疑惑,使用PasteSpecial方法时会出错。例如,执行下面的代码: Sub testPasteSpecial7() Selection.PasteSpecialPaste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub VBA会提示出错:
这是因为剪贴板中没有可供粘贴的数据。特别是,当你在工作表中复制单元格区域后,在功能区中选择“开发工具”——“宏”或者按Alt F8调出宏对话框时,Excel会清除剪贴板。(事实上,在单元格中使用复制命令后,访问Excel中的任何对话框,剪贴板中的数据都会被清除)因此,再执行上述代码的话,肯定会出错了! 为了避免出现上述情况,可以将上面的宏过程赋给一个按钮,直接单击按钮执行;或者在VBE中按F5键执行。 2.使用Range(“A1”).Paste形式 一般认为,有Copy方法,一定有相应的Paste方法,所以就会写出下面的错误代码: Range('A1').Copy Range('C1').Paste 事实上,Range对象有Copy方法,但是没有Paste方法,其粘贴的方法就是我们这里介绍的PasteSpecial方法,而Paste方法属于Worksheet对象(在介绍Worksheet对象时将详细讲解Paste方法)。
-------------------------------------- 如果您对本文介绍的内容有什么建议或好的示例,欢迎发送邮件给我:xhdsxfjy@163.com。 您也可以在本文下发表留言,留下您的足迹。 本文属原创文章,转载请联系我或者注明出处。
关注《完美Excel》微信公众账号: |
|