Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >常见的复制粘贴,VBA是怎么做的

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

作者头像
fanjy
发布于 2022-03-04 07:51:53
发布于 2022-03-04 07:51:53
13.3K03
代码可运行
举报
文章被收录于专栏:完美Excel完美Excel
运行总次数:3
代码可运行

标签: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:复制单元格区域到剪贴板

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
Sub Copy_to_Clipboard()
    Worksheets("SampleData").Range("B5:M107").Copy
End Sub

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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复制并粘贴全部(值、公式、格式)。在某些情况下,这正是你想要的。然而,在其他情况下,这是你不想要的。例如,下面的过程:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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方法:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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表示在粘贴时行列转置。

<未完待续>

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-01-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 完美Excel 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
常见的复制粘贴,VBA是怎么做的(续)
Worksheet.Paste(Excel VBA没有Range.Paste方法)在某种程度上与Range.PasteSpecial方法非常相似。Paste方法的主要目的是将剪贴板包含的内容粘贴到相关工作表上。
fanjy
2022/03/04
11.5K0
常见的复制粘贴,VBA是怎么做的(续)
ChatGPT与Excel结合_编写VBA宏
⭐Excel VBA宏(Visual Basic for Applications)是一种用于在Microsoft Excel中自动化和扩展功能的编程语言。VBA允许用户编写自定义的脚本或宏,以便通过执行一系列指令来自动完成特定任务。
天天Lotay
2023/10/15
1.1K0
ChatGPT与Excel结合_编写VBA宏
VBA: 提高 VBA 宏性能的 8 个技巧
文章背景: 作为VBA新手,在运行自己编写的代码时,可能会遇到VBA运行缓慢的问题。可以采取以下8个措施来提高运行效率。
Exploring
2022/12/18
4.2K0
VBA:  提高 VBA 宏性能的 8 个技巧
VBA专题02:使用代码进行复制操作
在Excel工作表中,复制粘贴是最常用的操作之一。在已经输入的数据中,找到并复制想要的数据,然后粘贴到指定的地方,是再自然不过的操作了。或者从工作表的一个单元格区域复制到同一工作表中另外的单元格区域,或者从工作表的一个单元格区域复制到另一工作表中的单元格区域,甚至从工作表的一个单元格区域复制到不同工作簿中的工作表单元格区域。那么,如何使用VBA代码来实现复制粘贴操作呢?本文将介绍常用的一些代码。
fanjy
2019/07/19
7.1K0
Excel VBA编程
在Excel中,数据只有文本,数值,日期值,逻辑值和错误值五种类型。但是在VBA中,数据类型跟Excel不完全相同。根据数据的特点,VBA将数据分为布尔型(boolean),字节型(byte),整数型(integer),单精度浮点型(single),双精度浮点型(double),货币型(currency),小数型(decimal),字符串型(string),日期型(date),对象型等等
全栈程序员站长
2022/08/11
47.6K0
Excel VBA编程
问与答118:如何使用VBA将多个工作表数据复制到PPT中?
遍历每个工作表,如果工作表的单元格S1中的值为“1”,则将该工作表的“Print_Area”(打印区域)复制并粘贴到一张空白幻灯片中。
fanjy
2021/05/07
5.9K1
Excel宏教程 (宏的介绍与基本使用)
大家好,又见面了,我是你们的朋友全栈君。 Excel宏教程 (宏的介绍与基本使用) Microsoft excel是一款功能非常强大的电子表格软件。它可以轻松地完成数据的各类数学运算,并用各种二维或三维图形形象地表示出来,从而大大简化了数据的处理工作。但若仅利用excel的常用功能来处理较复杂的数据,可能仍需进行大量的人工操作。但excel的强大远远超过人们的想象–宏的引入使其具有了无限的扩展性,因而可以很好地解决复杂数据的处理问题。 随着支持Windows的应用程序的不断增多和功能的不断增强,越来
全栈程序员站长
2022/08/23
7.3K0
VBA汇总文件夹中的多文件的工作表中不同单元格区域到总表
【问题】我们发了这样一个表格到各单位收集资料,各单位填写完后上交上来有许多个文件,我们现在想汇总成一年一个表,怎么办?
哆哆Excel
2022/10/25
2.6K0
VBA汇总文件夹中的多文件的工作表中不同单元格区域到总表
VBA: 将单元格区域作为邮件正文发送到指定邮箱
文章背景: 在工作中,有时需要将单元格区域的内容作为邮件正文发送到指定邮箱,如果希望邮件正文中的单元格区域带表格样式,则需要将其转换为html格式。
Exploring
2024/04/15
8170
VBA: 将单元格区域作为邮件正文发送到指定邮箱
VBA: 不连续单元格区域复制粘贴为数值
文章背景: 处理表格时,有时需要选中单元格区域,将区域内单元格的公式复制粘贴为数值。如果是连续的单元格区域,这样的操作是可行的。如果是不连续的单元格区域,这样的操作会报错。
Exploring
2022/09/20
1.6K0
VBA: 不连续单元格区域复制粘贴为数值
Python-Excel-09-复制单元格区域
系统:Windows 7 语言版本:Anaconda3-4.3.0.1-Windows-x86_64 编辑器:pycharm-community-2016.3.2
zishendianxia
2019/10/23
1.7K0
Python-Excel-09-复制单元格区域
VBA基础:复制格式、选取单元格及复制工作表的示例代码
fanjy
2024/05/25
6330
VBA基础:复制格式、选取单元格及复制工作表的示例代码
Range单元格对象常用方法(一)
今天介绍的单元格对象方法在平时使用excel也是常规的操作,如单元格的复制、剪切、删除、清除内容等。只是在VBA编程中是通过代码的形式来运行。
无言之月
2019/10/13
2.3K0
啰哩啰嗦地讲透VBA中引用单元格区域的18个有用方法--Range属性
引言:本文学习整理自powerspreadsheets.com,讲解得很细致,一些知识点反复强调,对于熟悉VBA的朋友来说,感觉有点啰嗦,但是对于VBA初学者来说,对快速掌握Range对象的引用,却很有好处。
fanjy
2022/01/07
7K0
啰哩啰嗦地讲透VBA中引用单元格区域的18个有用方法--Range属性
EXCEL VBA语句集300
        定制模块行为 (1) Option Explicit ‘强制对模块内所有变量进行声明 Option Private Module ‘标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示  Option Compare Text ‘字符串不区分大小写  Option Base 1 ‘指定数组的第一个下标为1 (2) On Error Resume Next ‘忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler ‘当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 ‘恢复正常的错误提示 (5) Application.DisplayAlerts=False ‘在程序执行过程中使出现的警告框不显示 (6) Application.ScreenUpdating=False ‘关闭屏幕刷新 Application.ScreenUpdating=True ‘打开屏幕刷新 (7) Application.Enable.CancelKey=xlDisabled ‘禁用Ctrl+Break中止宏运行的功能  工作簿 (8) Workbooks.Add() ‘创建一个新的工作簿 (9) Workbooks(“book1.xls”).Activate ‘激活名为book1的工作簿 (10) ThisWorkbook.Save ‘保存工作簿 (11) ThisWorkbook.close ‘关闭当前工作簿 (12) ActiveWorkbook.Sheets.Count ‘获取活动工作薄中工作表数 (13) ActiveWorkbook.name ‘返回活动工作薄的名称 (14) ThisWorkbook.Name ‘返回当前工作簿名称 ThisWorkbook.FullName ‘返回当前工作簿路径和名称 (15) ActiveWindow.EnableResize=False ‘禁止调整活动工作簿的大小 (16) Application.Window.Arrange xlArrangeStyleTiled ‘将工作簿以平铺方式排列 (17) ActiveWorkbook.WindowState=xlMaximized ‘将当前工作簿最大化  工作表 (18) ActiveSheet.UsedRange.Rows.Count ‘当前工作表中已使用的行数 (19) Rows.Count ‘获取工作表的行数(注:考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum” ‘将Sheet1命名为Sum (21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) ‘添加一个新工作表在第一工作表前 (22) ActiveSheet.Move After:=ActiveWorkbook. _ Sheets(ActiveWorkbook.Sheets.Count) ‘将当前工作表移至工作表的最后 (23) Worksheets(Array(“sheet1”,”sheet2”)).Select ‘同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或 Sheets(1).Delete ‘删除工作表1 (25) ActiveWorkbook.Sheets(i).Name ‘获取工作表i的名称 (26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines ‘切换工作表中的网格线显示,这种方法也可以用在其它方面进行相互切换,即相当于开关按钮 (27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings ‘切换工作表中的行列边框显示 (28) ActiveSheet.UsedRange.FormatConditions.Delete ‘删除当前工作表中所有的条件格式 (29) Cells.Hyperlinks.Delete ‘取消当前工作表所有超链接 (30) ActiveSheet.PageSetup.Orientation=xlLandscape 或ActiveSheet.PageSetup.Orientation=2 ‘将页面设置更改为横向 (31) ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName ‘在页面设置的表尾中输入文件路径 ActiveSheet.PageSetup.Le
Tony老师
2020/03/05
2.5K0
Vba实现彻底禁止表格内容复制,表格到期自动删除
分析:表格内容复制的方法  直接选择单元格进行复制。  复制表格到新的或者空白的工作簿 另存为其他格式表格 如何强制使用VBA 将所有表格的Visible属性设置为完全不可见,编写表格打开事件代码,打开时自动显示表格。不运行Vba时只显示空白表格。 Vba工程资源设置密码。  退出表格时将所有数据表改为完全不可见。 打开时调用工作簿结构保护,退出时解锁隐藏所有表。 禁止单元格复制 编写表格选择事件,让只有指定范围的单元格才能被选中。 编写定时任务,以1秒一下的运行速度,定时清空剪贴板。 禁止表格复制 编写E
房东的狗丶
2023/02/17
3.1K0
C# 解决Excel边框样式无法复制问题及实现格式刷功能
在运行数据表数据导出到 EXCEL 数据输出时遇到了一个问题,开发者设计了单行细线下边框的输出模板,如下图设计:
初九之潜龙勿用
2024/10/01
1480
C# 解决Excel边框样式无法复制问题及实现格式刷功能
VBA对象模型和对象引用
EXCEL中的各种对象并不是孤立存在的,彼此之间都是有联系和层级关系,对象模型的概念就是用来描述对象之间关系的,构建了一个完整的体系。
无言之月
2019/10/14
2.4K0
VBA对象模型和对象引用
使用VBA将图片从一个工作表移动到另一个工作表
今天跟大家分享的技巧来自thesmallman.com,一个分享Excel技巧技术的网站。
fanjy
2022/11/16
4.5K0
使用VBA将图片从一个工作表移动到另一个工作表
VBA实战技巧20:选取不同工作表中不同单元格区域时禁止用户执行复制剪切粘贴操作
在《VBA实战技巧19:根据用户在工作表中的选择来隐藏/显示功能区中的剪贴板组》中,我们讲解了根据用户在工作表中的选择来决定隐藏或者显示功能区选项卡中的特定组的技术。在这里就要派上用场了。
fanjy
2021/03/12
2.5K0
推荐阅读
相关推荐
常见的复制粘贴,VBA是怎么做的(续)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验