如何把多张工作表内容快速复制到一张表.docx

如何把多张工作表内容快速复制到一张表.docx

《如何把多张工作表内容快速复制到一张表.docx》由会员分享，可在线阅读，更多相关《如何把多张工作表内容快速复制到一张表.docx（19页珍藏版）》请在冰豆网上搜索。

如何把多张工作表内容快速复制到一张表

如何把多张工作表内容快速复制到一张表

我有一份教师工资表,一个工作簿里有好多张工作表的,现在我想把各工作表里的内容全部合到一张表中,除了用复制粘贴的方法外,请问还能有什么好的方法快速合到一起吗?

（首选）

Subyy（）’声明过程名称为“yy”。

当要声明一个过程时，通常是以关键字“SUB”开头，并且以关键字“END”结束，在SUB关键字之前是用来指定此过程的作用域，例如public,static,或protected等。

在SUB关键字之后为过程最重要的三大部分：

过程名称、参数以及在SUB和ENDSUB之间的主体程序代码内容。

过程是可以执行的语句序列单元，所有可执行的代码必须包含在某个过程中，任何过程都不能嵌套在其它过程中，过程的名称必须在模块级别进行定义。

Sheets.Addafter:

=Sheets（Sheets.Count）'新建一个工作表放在最后。

参数值和参数名之间应该使用“：

=”符号，而不是等号。

在代码中，属性和方法都是通过连接符“.”来和对象连接的。

Add是方法名，after是自变量，方法名与自变量之间用半角空格隔开。

Fori=1ToSheets.Count-1'从第一个工作表到倒数第二个工作表

WithSheets（i）’with语句可以在一个单一对象或一个用户定义类型上执行一系列的语句。

使用with语句不仅可以简化程序代码，而且可以提高代码的运行效率。

With/endwith语句结构中以“.”开头的语句相当于引用了with语句中指定的对象。

当程序一旦进入with/endwith结构，with语句指定的对象就不能改变。

因此不能用with语句来设置多个不同的对象。

n=.[c65536].End（xlUp）.Row'求出c列最大行号。

求出某列最大行号的目的是确定有数据区域的最下边界，为此，选择求最大行号列时，选择的列数据最下边界要最大限度包含所有的数据。

End和Row都是属性，此语句取得属性值后赋值给变量n。

此程序事先没有对变量进行声明。

在程序中取得对象的属性的代码结构：

变量=对象·属性。

.Range（"a2:

V"&n）.CopyActiveSheet.[c65536].End（xlUp）.Offset（1,-2）'取a2开始到最后的数据copy到新表数据的下一行。

n=.[c65536].End（xlUp）.Row得到C列最后一个非空单元格所在的行号，“.Offset（1,-2）”偏移“[c65536].End（xlUp）”得到单元格的位置开始复制后数据的粘贴位置。

由C列移到A列。

[c65536].End（xlUp）：

求出C列最下边有数据的单元格，粘贴复制的数据时为了前面的数据不被后面粘贴上的数据覆盖，开始粘贴数据的位置在最下边有数据的单元格位置的基础上要用offset偏移。

EndWith

Next

EndSub’注：

在VISUALBASIC中要加入注释，直接在注释文字前加上’符号即可，在’符号之后的文字会被编译器忽略，也可以使用REM来进行注释，但使用’符号会更快捷一些，还可以节省内存及加快编译的效率。

以上代码粘贴位置、复制范围可以根据实际需要可变。

2010年5月用于林业旱灾调查一个工作簿上的所有工作表汇总到一张工作表的代码：

Subyy（）'声明过程名称为"yy"。

当要声明一个过程时，通常是以关键字"SUB"开头，并且以关键字"END"结束，在SUB关键字之前是用来指定此过程的作用域，例如public,static,或protected等。

在SUB关键字之后为过程最重要的三大部分：

过程名称、参数以及在SUB和ENDSUB之间的主体程序代码内容。

过程是可以执行的语句序列单元，所有可执行的代码必须包含在某个过程中，任何过程都不能嵌套在其它过程中，过程的名称必须在模块级别进行定义。

Sheets.Addafter:

=Sheets（Sheets.Count）'新建一个工作表放在最后。

参数值和参数名之间应该使用"：

="符号，而不是等号。

在代码中，属性和方法都是通过连接符"."来和对象连接的。

Add是方法名，after是自变量，方法名与自变量之间用半角空格隔开。

Fori=1ToSheets.Count-1'从第一个工作表到倒数第二个工作表

WithSheets（i）'with语句可以在一个单一对象或一个用户定义类型上执行一系列的语句。

使用with语句不仅可以简化程序代码，而且可以提高代码的运行效率。

With/endwith语句结构中以"."开头的语句相当于引用了with语句中指定的对象。

当程序一旦进入with/endwith结构，with语句指定的对象就不能改变。

因此不能用with语句来设置多个不同的对象。

n=.[a65536].End（xlUp）.Row'分别求出最大行号。

End和Row都是属性，此语句取得属性值后赋值给变量n。

此程序事先没有对变量进行声明。

在程序中取得对象的属性的代码结构：

变量=对象·属性。

.Range（"a3:

v"&n）.CopyActiveSheet.[a65536].End（xlUp）.Offset（1,0）'取a2开始到最后的数据copy到新表数据的下一行。

n=.[c65536].End（xlUp）.Row得到C列最后一个非空单元格所在的行号，".Offset（1,-2）"偏移"[c65536].End（xlUp）"得到单元格的位置开始复制后数据的粘贴位置。

由C列移到A列。

EndWith

Next

EndSub

注：

在VISUALBASIC中要加入注释，直接在注释文字前加上’符号即可，在’符号之后的文字会被编译器忽略，也可以使用REM来进行注释，但使用’符号会更快捷一些，还可以节省内存及加快编译的效率。

如果当前活动工作表的A列填写的是学生的姓名，现在我们想在表中A列最后一个学生的后面增加一个叫“张青”的学生，则代码为：

　代码:

ActiveSheet.Cells（[A65536].End（xlUp）.Row+1,1）.Value="张青"

“[A65536].End（xlUp）.Row”语句来返回A列的最后一个非空单元格的行号

Sheet1.range（"C1"）.End（xlDown）.Row返回第一次遇到空单元格前的单元格的数量。

（注：

当C列有空白单元格时用：

myEndRow=sheets（"sheet1"）.range（"C65536"）.End（xlUp）.row）

[a65536].End（xlUp）取得A列最后一个数据的位置

本示例选定包含单元格B4的区域中B列顶端的单元格。

Range（"B4"）.End（xlUp）.Select

Range（"b65536"）.End（xlUp）.Row问题

高手赐教其含义与区别

Range（"a65536"）.End（xlUp）.Row

Range（"b65536"）.End（xlUp）.Row

Range（"d65536"）.End（xlUp）.Row

Range（"k65536"）.End（xlUp）.Row

看一下vba中END的帮助就知道了。

End属性

参阅应用于示例特性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。

等同于按键End+向上键、End+向下键、End+向左键或End+向右键。

Range对象，只读。

expression.End（Direction）

expression必需。

该表达式返回“应用于”列表中的对象之一。

DirectionXlDirection类型，必需。

所要移动的方向。

XlDirection可为XlDirection常量之一。

xlDown

xlToRight

xlToLeft

xlUp

示例

本示例选定包含单元格B4的区域中B列顶端的单元格。

Range（"B4"）.End（xlUp）.Select

本示例选定包含单元格B4的区域中第4行尾端的单元格。

Range（"B4"）.End（xlToRight）.Select

本示例将选定区域从单元格B4延伸至第四行最后一个包含数据的单元格。

Worksheets（"Sheet1"）.Activate

Range（"B4",Range（"B4"）.End（xlToRight））.Select

Range（"a65536"）.End（xlUp）.RowA列数据区域最后一行的行号

Range（"b65536"）.End（xlUp）.RowB列数据区域最后一行的行号

Range（"d65536"）.End（xlUp）.RowD列数据区域最后一行的行号

Range（"k65536"）.End（xlUp）.RowK列数据区域最后一行的行号

Range（"a65536"）.End（xlUp）.Row

A列第65536行之上的最后有数据的单元格所在行数

回复

Range（"b65536"）.End（xlUp）.Row=B列最后一个非空单元格的行数

Range（"a65536"）.End（xlUp）.Row=A列最后一个非空单元格的行数

Range（"b65536"）.End（xlUp）.Row=B列最后一个非空单元格的行数

Range（"d65536"）.End（xlUp）.Row=D列最后一个非空单元格的行数

Range（"k65536"）.End（xlUp）.Row=K列最后一个非空单元格的行数

请教.end（xlUp）.row的含义?

经常在VBA中常见如下代码：

DimRngAsInteger

Rng=sheets1.Range

代码详解

定义RNG作为一个整型变量

'第二行的"sheets1"这种写法是错误的.正确的表述方法应为"sheet1"或"sheets

（1）"

sheet1,对工作表对象的称呼;

range（"a65536"）指一个range对象,具体指第一列最后一行;

END,range对象的END属性,也返回一个对象,我理解为基于上述对象的延伸至最后一个RANGE对象.具体延伸的方向要看它给出的参数.比如xlToLeft、xlToRight、xlUp或xlDown。

ROW返回区域中第一块的第一行的行号。

Long类型，只读

多谢两位高手指点，看完以后我是这样理解的，不知对否？

    Range（"A65536"）.End（xlUp）.Row是指第一列最后一个有数据单元格的行数，它的下一行即加1就是3楼所说的要添加数据的空白单元格

汇总工作表数据

2010-06-0300:

44

方法一：

SubGetdata（）

DimErowAsInteger,cAsVariant,SerowAsInteger

Erow=Sheets（"Total"）.[a65536].End（xlUp）.Row+1

IfErow>4ThenSheets（"Total"）.Rows（"4:

"&Erow）.ClearContents

ForEachcInThisWorkbook.Sheets

Ifc.Name<>"Total"Then

Serow=c.[a65536].End（xlUp）.Row

Erow=Sheets（"Total"）.[a65536].End（xlUp）.Row+1

c.Range（"a4:

l"&Serow）.CopyDestination:

=Sheets（"Total"）.Range（"a"&Erow）

EndIf

Nextc

EndSub

方法二

SubSQLConbime（）

Range（"A4:

L65536"）.ClearContents

DimiRowAsInteger,sqlTempAsString,sqlAsString

Setcnn=CreateObject（"adodb.connection"）

cnn.Open"provider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource="&ThisWorkbook.FullName

ForEachshInSheets

Ifsh.Name<>"理数"Then

iRow=sh.Range（"a3"）.End（xlDown）.Row

sqlTemp="select*from["&sh.Name&"$A3:

L"&iRow&"]orderby班别,座号"

sql=sqlTemp&"union"&sql

EndIf

Next

sql=Left（sql,Len（sql）-6）

Sheets（"理数"）.Range（"a4"）.CopyFromRecordsetcnn.Execute（sql）

cnn.Close:

Setcnn=Nothing

EndSub

方法三

SubConbimeAll（）

DimyyAsLong

yy=[a65536].End（xlUp）.Row+1

Range（"a2:

l"&yy）.ClearContents

Setconn=CreateObject（"adodb.connection"）

Fori=1To4

conn.Open"provider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource="&ThisWorkbook.FullName

Sq2="select*from["&Range（"p"&i）&"$a3:

l1000]"

[a65536].End（xlUp）.Offset（1,0）.CopyFromRecordsetconn.Execute（Sq2）

conn.Close

Nexti

Setconn=Nothing

EndSub

把工作簿中的200多张表复制到一张工作表里？

（首选）

我是做收款工作的，

每天收款情况做一张表格，

现在快一年了，今天老板突然要我把这9个月积累的每日数据全部反映到一张工作表上，

其实点复制粘贴200多张表不是大问题，主要是怕中途看花了眼，漏了或者重复了就很麻烦，

请教高手帮忙，用公式或者VBA，小弟感激不尽。

右键点击新工作表标签，选择“查看代码”，将下面的代码粘贴到打开的VBE窗口中：

SubShcopy（）

DimnRow&,R1%,Rs%,Ls%’&:

长整数型;%:

整数型。

R1=2'每页从第2行开始复制（可修改）

Application.EnableEvents=False’如果对指定对象启用事件，则该值为True。

Boolean类型，可读写

Application.ScreenUpdating=False

Fori=2ToSheets.Count’sheets.count最后一张工作表。

WithSheets（i）

Rs=.UsedRange.Rows.Count+1-R1’对于Worksheet对象，Rows属性返回代表指定工作表所有行的Range对象。

Range对象，只读。

工作表上已使用区域的行数。

Ls=.UsedRange.Columns.Count’Count属性。

描述：

返回集合或Dictionary对象中的条目数。

只读。

语法：

object.Count

object总是“应用于”列表中某一项的名称。

工作表上已使用区域的列数。

arr=.Range（"a"&R1）.Resize（Rs,Ls）‘arr为变量，在VBA中，变量无需声明就可以直接使用，此时该变量为变体变量。

Resize属性。

在Excel中，Resize属性的功能为调整指定区域的大小。

返回Range对象，该对象代表调整后的区域。

expression.Resize（RowSize,ColumnSize）

expression必需。

该表达式返回要调整大小的Range对象。

RowSizeVariant类型，可选。

新区域中的行数。

如果省略该参数，则该区域中的行数保持不变。

ColumnSizeVariant类型，可选。

新区域中的列数。

如果省略该参数。

则该区域中的列数保持不变。

Range（"a65536"）.End（xlUp）.Offset

（1）.Resize（Rs,Ls）=arr

EndWith

Next

Application.EnableEvents=True

Application.ScreenUpdating=True

EndSub

修改“开始行号”。

将光标定位到代码中间任意位置，按F5运行它。

关闭VBE窗口。

注意单词之间要用空格隔开。

Excel多个工作簿中的工作表合并到一个工作簿中

有时，需要将多个Excel工作簿中的工作表合并到一个工作簿中。

有多种合并工作簿的情形，下面先给出一种合并多个工作簿的VBA范例，供参考。

（此方法将一个工作簿中所有的工作表复制到一张工作表上）

方法1

SubCombineWorkbooks（）

DimwkAsWorkbook

DimshAsWorksheet

DimstrFileNameAsString

DimstrFileDirAsString

DimnmAsString

nm=ThisWorkbook.Name

strFileDir=ThisWorkbook.path&"\"

Application.ScreenUpdating=False

strFileName=Dir（strFileDir&"*.xls"）

DoWhilestrFileName<>vbNullString

IfstrFileName<>nmThen

MsgBoxstrFileName

Setwk=Workbooks.Open（Filename:

=strFileDir&strFileName,ReadOnly:

=True）

strFileName=Left（Left（strFileName,Len（strFileName）-4）,29）'取主文件名,除掉.XLS

ForEachshInwk.Sheets

sh.CopyAfter:

=ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count）

'工作表命名,以工作表所在文件名为类

Ifwk.Sheets.Count>1Then

ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count）.Name=strFileName&sh.Name

Else

ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count）.Name=strFileName

EndIf

Next

wk.CloseSaveChanges:

=False

EndIf

strFileName=Dir

Loop

Application.ScreenUpdating=True

EndSub

方法2

SubUnWorksheets（）

Application.ScreenUpdating=False

DimljAsString

DimdirnameAsString

DimnmAsString

DimsnameAsString

DimiAsInteger,iiAsInteger

lj=ActiveWorkbook.path

nm=ActiveWorkbook.Name

dirname=Dir（lj&"\*.xls"）'查找文件

DoWhiledirname<>""

Ifdirname<>nmThen

Workbooks.OpenFilename:

=lj&"\"&dirname'打开文件

ii=ActiveWorkbook.Sheets.Count'统计工作表个数

'复制新打开工作簿的每一个工作表到当前工作表（After:

=ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count））最后一个后面

Fori=1Toii

Workbooks（dirname）.Sheets（i）.CopyAfter:

=ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count）

Next

Workbooks（dirname）.CloseFalse

EndIf

dirname=Dir

Loop

EndSub

在同一文件夹下有多个工作簿，其中有一个用于汇总的工作簿，将除该汇总工作簿外的其它工作簿中的每一张工作表的数据汇总到该汇总工作簿的一张工作表中。

好用！

SubUnionWorksheets（）

Application.ScreenUpdating=False’关闭屏幕更新

DimljAsString

DimdirnameAsString

DimnmAsString

DimiAsInteger,iiAsInteger

lj=ActiveWorkbook.path’Path属性。

返回指定文件、文件夹、或驱动器的路径。

nm=ActiveWorkbook.Name’Name属性。

指定一个控件或对象的名称或与Font对象相关的字体的名称。

变量赋值使用=“等号”，“=”后的值可以是单纯的数值、字符串或表达式。

dirname=Dir（lj&"\*.xls"）’Dir函数。

返回一个String，用以表示一个文件名、目录名或文件夹名称，它必须与指定的模式或文件属性、或磁盘卷标相匹配。

Cells.Clear’Clear方法。

清除Err对象的所有属性设置。

DoWhiledirname<>""’前置式DO…LOOP循环。

Ifdirname<>nmThen

Workbooks.OpenFilename:

=lj&"\"&dirname’Open方法。

“<>”为比较运算符“不等于”。

ii=ActiveWorkbook.Sheets.Count’Sheets属性

Workbooks（nm）.Activate’Activate方法。

Workbooks（nm）属使用工作簿名称引用workbook，语法格式为：

workbook（工作簿名称）。

'复制新打开工作簿的每一个工作表的已用区域到当前工作表

Fori=1Toii

Workbooks（dirname）.Sheets（i）.UsedRange.Copy_

Range（"a65536"）.End（xlUp