Excel

您所在的位置：网站首页 › excel选择区域复制不能复制对象怎么解决方法 › Excel

Excel

#Excel| 来源: 网络整理| 查看: 265

一、对象模型

在VBE中“帮助（H）”——“Microsoft Visual Basic 帮助（H） F1”——“Visual Basic 语言参考”——“函数” 或者在VBE下快捷键“F1”

地址：https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel/object-model

二、Application对象（Excel顶层对象）

1、ScreenUpdating属性

是否控制屏幕更新，False表示关闭屏幕更新，True表示打开屏幕更新

设置ScreenUpdating=False 关闭屏幕更新，将看不到程序的执行过程，可以加快程序的执行速度，让程序显得更直观，专业。

示例（为关闭屏幕更新下，会弹出对话框）：

Sub InputTest() Cells.ClearContents '清除表中所有数据 Range("A1:A10") = 100 MsgBox "刚才在A1:A10输入数值100，你能看到结果吗？" Range("B1:B10") = 200 MsgBox "刚才在B1:B10输入数值200，你能看到结果吗？" End Sub

示例（关闭屏幕更新，看不到执行过程，程序最终执行完成才能看到最终结果）

Sub InputTest() Cells.ClearContents '清除表中所有数据 Application.ScreenUpdating = False '关闭屏幕更新 Range("A1:A10") = 100 MsgBox "刚才在A1:A10输入数值100，你能看到结果吗？" Range("B1:B10") = 200 MsgBox "刚才在B1:B10输入数值200，你能看到结果吗？" Application.ScreenUpdating = True '恢复屏幕更新 End Sub

2、DisplayAlterts属性

是否显示警告对话框，False为不显示，True为显示

Sub delSht() Dim sht As Worksheet Application.DisplayAlerts = False '不显示警告信息 For Each sht In Worksheets If sht.Name = ActiveSheet.Name Then '判断sht是不是活动工作表 sht.Delete '删除sht代表的工作表 End If Next Application.DisplayAlerts = True '恢复显示警告信息 End Sub

3、EnableEvents属性

启用或禁用事件，False为禁用（不让事件发生），True为启用

什么是事件？能被Excel认识的一个操作动作，例如“打开工作簿”、“关闭工作簿”等

示例1：编写一个程序，当选中工作表的单元格时，自动在单元格中写入该单元格的地址 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Value = Target.Address End Sub 示例2：选中活动单元格，记录对应单元格地址，并将活动单元格向下移动一个单元格 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Value = Target.Address Application.EnableEvents = False '禁用事件 Target.Offset(1, 0).Select '选中活动单元格下面的一个单元格 Application.EnableEvents = True '启用事件 End Sub

4、WorksheetFunction属性

使用WorksheetFunction调用Excel内置函数

示例1：统计A1:A50单元格中数值大于1000的单元格有多少个？ Sub CountTest() Dim mycount As Integer, rng As Range For Each rng In Range("A1:B50") If rng.Value > 1000 Then mycount = mycount + 1 Next MsgBox "A1:B50中大于1000的单元格个数为:" & mycount End Sub 示例2：统计A1:A50单元格中数值大于1000的单元格有多少个？使用COUNTIF函数 Sub CountTest() Dim mycount As Integer mycount = Application.WorksheetFunction.CountIf(Range("A1:B50"), ">1000") MsgBox "A1:B50中大于1000的单元格个数为:" & mycount End Sub

5、给Excel梳妆打扮

Excel工作表界面相关命令

Excel界面

6、Application的常用属性

三、Workbook对象

Workbook工作簿

Workbooks工作簿集合

1、怎么引用工作簿

引用工作簿，就是指明工作簿的位置及名称，共有两种方式

方式一：利用索引号引用工作簿，Workbook.Item(3)，这里的Item可以省略，即Workbook(3)

方式二：利用工作簿名称引用，Workbook("Book1")或Workbook("Book1.xls")，如果本地文件显示拓展名（且文件已经保存），则文件名必须带拓展名，否则会报错。

2、Workbook名片信息

Sub wbMsg() Range("B2") = ThisWorkbook.Name '返回当前工作簿名称练习 -副本.xlsm Range("B3") = ThisWorkbook.Path '返回当前工作簿路径 C:\Users\ThinkPad\Desktop Range("B4") = ThisWorkbook.FullName '返回当期工作簿带名称的路径 C:\Users\ThinkPad\Desktop\练习 - 副本.xlsm End Sub

3、创建工作簿

使用方法：Workbooks.Add

　　如果不带任何参数，将创建包含一定数目空白工作表的新工作簿（数目由SheetsInNewWorkbook属性决定）

也可以给Add方法设置参数(参数表示现有Excel名称的字符串，选用该参数，新建的工作簿将以该文件作为模板)

　　Workbooks.Add "C:\Program Files\Microsoft Office\Templates\2052\ADDRESS\ADDRESS.XLS"

也可以通过参数指定新建工作簿中包含的工作类型

　　Workbooks.Add xlWBATChart '新建图表工作表

Excel一共有4种类型的工作表

　　可以在插入对话框里看到(选中工作表名称——鼠标右键单击——插入——即可显示)，如图（包含参数说明）：

4、打开工作簿

使用Workbooks的Open方法（参数名要写含路径的名称）

Sub OpenFile() Workbooks.Open Filename:="F:\Book1.xls" End Sub

参数名成可以省略不写(Open除了Filename参数外，还有14个参数，让用户决定以何种方式打开指定的文件，可以通过系统的帮助来查看更多的信息)

Sub OpenFile() Workbooks.Open "F:\Book1.xls" End Sub

5、激活工作簿

同事打开多个工作簿，但是同一时间只能有一个窗口是活动的，调用Workbooks对象的Active方法可以激活一个工作簿。

Sub JhWb() Workbooks("Book1.xls").Activate '激活工作簿 End Sub

6、保存工作簿

保存工作簿调用Workbooks的Save方法

Sub SaveWb() ThisWorkbook.Save '保存代码所在的工作簿 End Sub

如果想将文件另存为一个新的文件，或者第一次保存一个新建的工作簿，就用SaveAs方法。

参数指定文件保存的路径及文件名如果省略路径，则默认将文件保存在当前文件夹中

Sub SaveWb() ThisWorkbook.SaveAs Filename:="D:\test.xls" End Sub

使用SaveAs方法将工作簿另存为新文件后，将自动关闭原文件，打开新文件，如果希望继续保留原文件不打开新文件，可以用SaveCopyAs方法

Sub SaveWb() ThisWorkbook.SaveCopyAs Filename:="D:\test.xls" End Sub

7、关闭工作簿

关闭工作簿使用Workbooks的Close方法，如果不带参数，则关闭所有打开的工作簿

Sub CloseWb() Workbooks.Close '关闭所有打开的工作簿 End Sub

如果想关闭指定的工作簿，需要指定参数

Sub CloseWb() Workbooks("Book1.xls").Close '关闭Book1.xls End Sub

如果关闭之前被更改过的内容没有保存，关闭工作簿前Excel会询问用户是否保存更改，如果不想显示该对话框，可以给Close方法设置参数：

Sub CloseWb() Workbooks("Book1.xls").Close savechanges:=True '关闭并保存Book1.xls End Sub

关闭并保存的参数savechanges也可以省略不写：

Sub CloseWb() Workbooks("Book1.xls").Close True '关闭Book1.xls End Sub

8、ThisWorkbook与ActiveWorkbook

同是Application对象的属性，同是返回Workbook对象，但二者并不是等同的。

ThisWorkbook是对程序所在的工作簿的引用

ActiveWorkbook是对活动工作簿的引用

新建的工作簿总会成为活动工作簿

Sub wb() Workbooks.Add MsgBox "代码所在的工作簿为：" & ThisWorkbook.Name & Chr(13) _ & "当前活动工作簿为：" & ActiveWorkbook.Name ActiveWorkbook.Close savechanges:=False End Sub

四、Worksheet对象

Worksheet表示一张普通的工作表，Worksheets表示多个Worksheet对象的集合。

1、引用工作表

可以使用工作表的索引号或者标签名称引用它

Worksheets.Item (1) '引用工作表里的第一张工作表 Worksheets (1) '引用工作表里的第一张工作表 Worksheets ("Sheet1") '引用工作簿里标签名称为"Sheet1"的工作表

　　因为代码名称只能在【属性窗口】里修改，不会随着工作表标签名称或索引号的变化而变化。因此，当工作表的索引号或标签名称经常变化时，使用代码名称引用工作表会更方便。

　　使用代码名称引用工作表，只需直接写代码名称

　　例如：第一张工作表的A1单元格输入100，代码为：Sheet1.Range("A1")=100

　　查看工作表的代码名称，可以读取它的CodeName属性，如果想知道活动工作表的代码名称，代码为：

Sub ShowShtCode() MsgBox ActiveSheet.CodeName End Sub

2、新建工作表

新建工作表使用Worksheets的Add方法

不带任何参数，将在活动工作表新建一张工作表

　　Worksheets.Add

可以用参数给新建的工作表指定位置

　　Worksheets.Add before:=Worksheets(1) '在第一张工作表前插入一张新的工作表

　　Worksheets.Add after:=Worksheets(1) ‘在第一张工作表后插入一张新的工作表

还可以同时插入多张工作表

　　Worksheets.Add Count:=3 '在活动工作表前插入3张工作表，Count参数的缺省值为1

可以同时使用多个参数，不同参数之间用英文逗号隔开 Sub shtAdd() Worksheets.Add after:=Worksheets(1), Count:=3 End Sub

　　在最后一张工作表后插入两张工作表

Sub shtAdd() '在最后一个工作表后插入两张工作表 Worksheets.Add before:=Worksheets(Worksheets.Count), Count:=2 End Sub Add方法有哪些参数？请看VBE的提示

3、更改工作表标签名称

更改工作表标签名称，设置工作表Name属性

　　Worksheets(2).Name="工资表" '更改第二张工作表的标签名称为“工资表”

新建工作表时在程序中更改标签名称 Sub shtAdd() Worksheets.Add Before:=Worksheets(1) ActiveSheet.Name = "工资表" End Sub 新建工作表同时指定它的标签名称 Sub shtAdd() '在第一张工作表前插入一个名称为“工资表”的工作表 Worksheets.Add(before:=Worksheets(1)).Name = "工资表" End Sub 如果同时添加多张工作表（即Count参数值大于1），并不能使用一句代码同时命名

4、删除工作表

删除工作表使用Worksheets对象的Delete方法

Worksheets("Sheet1").Delete '删除Sheet1工作表

5、激活工作表

激活工作表可以使用Activate方法和Select方法

Worksheets(1).Activate '激活第一张工作表Worksheets(1).Select '激活第一张工作表

6、复制工作表

复制工作表使用Copy方法

Sub shtCopy() '这里的工作表名称一定要存在，否则执行会报错 Worksheets("工资条").Copy '不带参数复制工作表，同时新建工作簿用于存放copy来的工作表（未保存状态） Worksheets("工资条").Copy before:=Worksheets("Sheet1") '带参数复制工作表，存放在当前工作簿的工作表Sheet1之前 Worksheets("工资条").Copy after:=Worksheets("Sheet1") '带参数复制工作表，存放在当前工作簿的工作表Sheet1之后 End Sub

7、移动工作表

移动工作表与复制工作表类似，使用方法Move

Sub shtMove() Worksheets("工资条").Move '不指定参数，将把工作表移动到新的工作簿中（新建工作簿） Worksheets("工资条").Move before:=Worksheets("Sheet1") '复制工作表，存放在当前工作簿的工作表Sheet1之前 Worksheets("工资条").Move after:=Worksheets("Sheet1") '复制工作表，存放在当前工作簿的工作表Sheet1之后 End Sub

8、隐藏和显示工作表

使用工作表的Visible属性显示或隐藏工作表

'以下这三行代码作用一样，等同于从【格式】菜单中隐藏工作表 Worksheets("工资条").Visible = False Worksheets("工资条").Visible = xlSheetHidden Worksheets("工资条").Visible = 0

用下面方法隐藏的工作表，跟上面3种方法不一样，且通过这种方法隐藏的工作表，无法通过菜单取消隐藏，只能通过VBA在属性窗口设置或者用代码取消隐藏

Worksheets("工资条").Visible = xlSheetVeryHidden Worksheets("工资条").Visible = 2

无论以何种方式隐藏了工作表，都可以用如下代码中的任意一句显示它

Worksheets("工资条").Visible = True Worksheets("工资条").Visible = xlSheetVisible Worksheets("工资条").Visible = 1 Worksheets("工资条").Visible = -1

9、获取工作表的数目

使用Worksheets.Count

Dim mycount% mycount=Worksheets.Count

10、Sheets与Worksheets

不同的命令，返回相同的结果 Sheets(2).Name Worksheets(2).Name Sheets.Count Worksheets.Count 分别代表两种不同的集合

　　Excel里共有4中不同类型的工作表，Sheets表示公祖不里所有类型的工作表的集合，而Worksheets只表示普通工作表的集合。　　Sheets和Worksheets集合里的对象都有标签名称Name、代码名称CodeName、索引号Index等属性，也有Add、Delete、Copy和Move等方法，设置属性和调用方法类似。但是因为Sheets集合包含更多类型的工作表，所有其包含的方法和属性比Worksheets集合多。

五、Range对象

1、Worksheet(或Range)对象的Range属性

引用单元格并赋值

Worksheets("sheet1").Range("A1").Value=50

Sub rng() Range("A1:A10").Value = 200 '在活动工作表的A1:A10输入值为200 Dim n As String n = "B1:B10" Range(n) = 100 '在活动工作表的B1:B10输入值为100 End Sub 通过设置“单元格区域名称”调用Range Sub rng() Range("date").Value = 200 End Sub

引用多个不连续的区域，用逗号隔开 Sub rng() Range("A1:A10,A4:E6,C3:D9").Value = 200 End Sub 用空格而不是逗号，则表示选中区域交集部分 Sub rng() Range("A1:B10 A4:D9").Value = 200 End Sub

2、Worksheet(或Range)对象的Cells属性

指定单元格 Sub shtCells() ActiveSheet.Cells(3, 4).Value = 20 '在第3行，第4列香蕉的单元格输入20 ActiveSheet.Cells(3, "D").Value = 30 '在第3行，第D列相交的单元格输入30 Range("B3:F9").Cells(2, 3) = 40 '在区域“B3:F9”区域中的第2行，第3列相交的单元格，即D4 ActiveSheet.Cells(2).Value = 50 '在活动工作表的第二个单元格输入50,这里使用的数字2是单元格序号，序号是按照单元格区域内由左向右递增 '选中活动工作表的A1:E10 Range(Cells(1, 1), Cells(10, 5)).Select '以下两个语句等价 Range("A1", "E10").Select Range(Range("A1"), Range("E10")).Select End Sub 全部单元格 Sub shtCells() ActiveSheet.Cells.Select '选中活动工作表的所有单元格 Range("B3:E9").Select '选中活动工作表中B3:E9单元格区域 End Sub 更简短的快捷方式 Sub shtCells() [A1] = 10 [A1:B10] = 20 [B3:D10 A4:G8] = 100 '公共交叉区域，如果两个区域参数没有逗号，表示一个参数，而参数表示的区域没有交集的话会报错 [A1:A10,C1:C10,E1:E10] = 200 '合并区域 [area] = 300 '名称are代表单元格，即单元格名称为area End Sub

　　[]是Application对象的Evaluate方法的简写形式，这种简写形式非常适合饮用一个固定的Range对象，但是因为不能再方括号中使用变量，所以这种引用方式缺少灵活性。

4、其他获取单元格的方式（除了Range、Cells外）—Rows

ActiveSheet.Rows '选中活动工作表的所有行 ActiveSheet.Rows(3).Select '选中活动工作表的第3行 ActiveSheet.Rows("3:3").Select '选中活动工作表的第3行 ActiveSheet.Rows("3:5").Select '选中活动工作表的第3行到第5行 Rows("3:10").Rows("1:1").Select '选中第3行到第10行区域内的第一行

5、其他获取单元格的方式（除了Range、Cells外）—Columns

ActiveSheet.Columns '选中活动工作表的所有列 ActiveSheet.Columns (6) '选中活动工作表中的第6列 ActiveSheet.Columns ("F:G") '选中活动工作表中的F至G列 Columns("B:G").Columns("B:B").Select '选中B:G区域中的第2列

6、Application的Union方法

Union方法像一支强烈的粘合剂，将不连续的多个单元格区域粘在一起，可以同时对其进行操作。

Sub rngUnion() Application.Union(Range("A1:A10"), Range("D1:D5")).Select '入参至少为2个区域，至多30个区域，区域之间用逗号分隔 Union(Range("A1:A10"), Range("D1:D5")).Select 'application可以省略不写 End Sub

7、Range对象的Offset属性

Offset属性用来基于基于单元格的位置移动

Offset(x,y)两个参数，x表示行移动，即x>0表示向下移动，x0表示向右移动，y 0 Then MsgBox "工作簿已经存在" Else MsgBox "工作簿不存在" End If End Sub

4、向未打开的工作簿中录入数据

Sub WbInput() '在本工作簿所在的文件夹下“员工花名册”里添加一条记录 Dim wb As String, xrow As Integer, arr wb = ThisWorkbook.Path & "\员工花名册.xls" Workbooks.Open (wb) With ActiveWorkbook.Worksheets(1) xrow = .Range("A1").CurrentRegion.Rows.Count + 1 arr = Array(xrow - 1, "张娇", "女", "#7/8/1987#", "#9/1/2010#", "10年新招") .Cells(xrow, 1).Resize(1, 6) = arr End With ActiveWorkbook.Close savechanges:=True End Sub

5、隐藏活动工作表外的所有工作表

Sub ShtVisible() '隐藏活动工作表外的所有工作表 Dim sht As Worksheet For Each sht In Worksheet If sht.Name ActiveSheet.Name Then sht.Visible = xlSheetVeryHidden '深度隐藏，不能通过“格式”菜单显示它 End If Next End Sub

6、批量新建工作表

Sub shtAdd() '一张成绩表中保存不同班级的数据，需要以班级名命名 '根据C列的班级名新建不同的工作表 Dim i As Integer, sht As Worksheet i = 2 Set sht = Worksheets("成绩表") Do While sht.Cells(i, "C") "" Worksheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = sht.Cells(i, "C").Value i = i + 1 Loop End Sub

7、批量对数据分类

Sub fenLei() '把成绩按班级分到各个工作表中 Dim i As Long, bj As String, rng As Range i = 2 bj = Cells(i, "C").Value Do While bj "" '将分表中A列第一个空单元格赋给rng Set rng = Worksheets(bj).Range("A65536").End(xlUp).Offset(1, 0) Cells(i, "A").Resize(1, 7).Copy rng '将记录赋值到对应的工作表中 i = i + 1 bj = Cells(i, "C").Value Loop End Sub

清除工作表内容

Sub shtClear() Dim sht As Worksheet For Each sht In Worksheets If sht.Name "成绩表" Then sht.Range("A2:G65536").ClearContents End If Next End Sub

8、将工作表保存为新工作簿

Sub SaveToFile() '把各个工作表以单独的工作簿文件保存在本工作簿所在的文件夹下的“班级成绩表”文件夹下 Application.ScreenUpdating = False '关闭屏幕更新 Dim folder As String folder = ThisWorkbook.Path & "\班级成绩表" '如果文件夹不存在，则新建文件夹 If Len(Dir(folder, vbDirectory)) = 0 Then mkdir folder Dim sht As Worksheet For Each sht In Worksheets sht.Copy ActiveWorkbook.SaveAs folder & "\" & sht.Name & ".xlsx" ActiveWorkbook.Close Next Application.ScreenUpdating = True End Sub

换种写法：

Sub 自动拆分工作表() ' ' 自动拆分工作表宏 ' ' 快捷键: Ctrl+m ' '把各个工作表以单独的工作簿文件保存在本工作簿所在的文件夹下的“拆分工作簿”文件夹下 Application.ScreenUpdating = False '关闭屏幕更新 Dim folder As String folder = Application.ActiveWorkbook.Path & "\拆分工作簿" 'folder = ThisWorkbook.Path & "\拆分工作簿" '如果文件夹不存在，则新建文件夹 If Len(Dir(folder, vbDirectory)) = 0 Then MkDir folder Dim sht As Worksheet For Each sht In Worksheets sht.Copy ActiveWorkbook.SaveAs folder & "\" & sht.Name & ".xlsx" ActiveWorkbook.Close Next Application.ScreenUpdating = True End Sub

9、快速合并多表数据

Sub HeBing() '把各班级成绩表合并到“总成绩”工作表中 Rows("2:25536").Clear '删除原有记录 Dim sht As Worksheet, xrow As Integer, rng As Range For Each sht In Worksheets '遍历工作簿中所有工作表 If sht.Name ActiveSheet.Name Then Set rng = Range("A65536").End(xlUp).Offset(1, 0) '获得A列第一个空单元格 xrow = sht.Range("A1").CurrentRegion.Rows.Count - 1 '记录分表中记录条数 sht.Range("A2").Resize(xrow, 7).Copy rng '粘贴记录到汇总表 End If Next End Sub

10、汇总同文件夹下多个工作簿数

Sub HzwWb() '把目前下各个工作簿的信息汇总到同文件夹下的另一个工作簿的同一张工作表里 Dim r, c As Long r = 1 '表头的行数 c = 8 '表头的列数 Range(Cells(r + 1, "A"), Cells(65536, c)).ClearContents '清空汇总表中原数据 Application.ScreenUpdating = False '关闭屏幕更新 Dim FileName As String, wb As Workbook, sht As Worksheet, Erow As Long, fn As String, arr As Variant FileName = Dir(ThisWorkbook.Path & "\" & "*.xlsx") Do While FileName "" If FileName ThisWorkbook.Name Then '判断文件是否是本工作簿 Erow = Range("A1").CurrentRegion.Rows.Count + 1 '取得汇总表中第一条空行行号 fn = ThisWorkbook.Path & "\" & FileName Set wb = GetObject(fn) '将fn代表的工作簿对象赋给变量 Set sht = wb.Worksheets(1) '汇总的是第一张工作表 '将数据表中的记录保存在arr数组里 arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(65536, "B").End(xlUp).Offset(0, 8)) '将数组arr中的数据写入工作表 Cells(Erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr wb.Close False End If FileName = Dir '用Dir函数取得其他文件名，并赋值给变量 Loop Application.ScreenUpdating = True '恢复屏幕更新 End Sub

11、为工作表建立目录

Sub mkdir() '为工作簿中所有工作表建立目录 Rows("2:65536").ClearContents Dim sht As Worksheet, irow As Integer irow = 2 For Each sht In Worksheets '遍历工作表 Cells(irow, "A").Value = irow - 1 '写入序号 '写入工作表名，并建立超链接 ActiveSheet.Hyperlinks.Add anchor:=Cells(irow, "B"), Address:="", _ SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name irow = irows + 1 '行号加1 Next End Sub

【本文地址】

Excel

Excel

今日新闻

推荐新闻