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

1、excel如何把多张工作表内容快速复制到一张表给工作表的每行都附上工作表名作为新列Sub test()Dim i As IntegerDim j As IntegerDim o As Integeri = Worksheets.Count For j = 1 To io = Worksheets(j).UsedRange.Rows.CountWorksheets(j).Range(k3:k30) = Worksheets(j).NameNextEnd Sub如何把多张工作表内容快速复制到一张表我有一份教师工资表,一个工作簿里有好多张工作表的,现在我想把各工作表里的内容全部合到一张表中,除了用复

2、制粘贴的方法外,请问还能有什么好的方法快速合到一起吗?（首选）Sub yy() 声明过程名称为“yy”。当要声明一个过程时，通常是以关键字“SUB”开头，并且以关键字“END”结束，在SUB关键字之前是用来指定此过程的作用域，例如public,static, 或protected 等。在SUB关键字之后为过程最重要的三大部分：过程名称、参数以及在SUB和END SUB之间的主体程序代码内容。过程是可以执行的语句序列单元，所有可执行的代码必须包含在某个过程中，任何过程都不能嵌套在其它过程中，过程的名称必须在模块级别进行定义。Sheets.Add after:=Sheets(Sheets.Coun

3、t)新建一个工作表放在最后。参数值和参数名之间应该使用“：=”符号，而不是等号。在代码中，属性和方法都是通过连接符“.”来和对象连接的。Add是方法名，after是自变量，方法名与自变量之间用半角空格隔开。For i = 1 To Sheets.Count - 1从第一个工作表到倒数第二个工作表With Sheets(i)with语句可以在一个单一对象或一个用户定义类型上执行一系列的语句。使用with语句不仅可以简化程序代码，而且可以提高代码的运行效率。 With/end with语句结构中以“.”开头的语句相当于引用了with语句中指定的对象。当程序一旦进入with/end with结构，w

4、ith语句指定的对象就不能改变。因此不能用with语句来设置多个不同的对象。n = .c65536.End(xlUp).Row求出c列最大行号。求出某列最大行号的目的是确定有数据区域的最下边界，为此，选择求最大行号列时，选择的列数据最下边界要最大限度包含所有的数据。End和Row都是属性，此语句取得属性值后赋值给变量n。此程序事先没有对变量进行声明。在程序中取得对象的属性的代码结构：变量=对象属性。.Range(a2:V&n).Copy ActiveSheet.c65536.End(xlUp).Offset(1, -2)取a2开始到最后的数据copy到新表数据的下一行。n =.c65536.E

5、nd(xlUp) .Row得到C列最后一个非空单元格所在的行号，“.Offset(1, -2)”偏移“c65536.End(xlUp)”得到单元格的位置开始复制后数据的粘贴位置。由C列移到A列。c65536.End(xlUp)：求出C列最下边有数据的单元格，粘贴复制的数据时为了前面的数据不被后面粘贴上的数据覆盖，开始粘贴数据的位置在最下边有数据的单元格位置的基础上要用offset偏移。End WithNextEnd Sub 注：在VISUAL BASIC中要加入注释，直接在注释文字前加上符号即可，在符号之后的文字会被编译器忽略，也可以使用REM来进行注释，但使用符号会更快捷一些，还可以节省内存

6、及加快编译的效率。以上代码粘贴位置、复制范围可以根据实际需要可变。2010年5月用于林业旱灾调查一个工作簿上的所有工作表汇总 到一张工作表的代码：Sub yy() 声明过程名称为yy。当要声明一个过程时，通常是以关键字SUB开头，并且以关键字END结束，在SUB关键字之前是用来指定此过程的作用域，例如public,static, 或protected 等。在SUB关键字之后为过程最重要的三大部分：过程名称、参数以及在SUB和END SUB之间的主体程序代码内容。过程是可以执行的语句序列单元，所有可执行的代码必须包含在某个过程中，任何过程都不能嵌套在其它过程中，过程的名称必须在模块级别进行定义。

7、Sheets.Add after:=Sheets(Sheets.Count) 新建一个工作表放在最后。参数值和参数名之间应该使用：=符号，而不是等号。在代码中，属性和方法都是通过连接符.来和对象连接的。Add是方法名，after是自变量，方法名与自变量之间用半角空格隔开。For i = 1 To Sheets.Count - 1 从第一个工作表到倒数第二个工作表With Sheets(i) with语句可以在一个单一对象或一个用户定义类型上执行一系列的语句。使用with语句不仅可以简化程序代码，而且可以提高代码的运行效率。 With/end with语句结构中以.开头的语句相当于引用了with

8、语句中指定的对象。当程序一旦进入with/end with结构，with语句指定的对象就不能改变。因此不能用with语句来设置多个不同的对象。n = .a65536.End(xlUp).Row 分别求出最大行号。 End和Row都是属性，此语句取得属性值后赋值给变量n。此程序事先没有对变量进行声明。在程序中取得对象的属性的代码结构：变量=对象属性。.Range(a3:v & n).Copy ActiveSheet.a65536.End(xlUp).Offset(1, 0) 取a2开始到最后的数据copy到新表数据的下一行。n =.c65536.End(xlUp) .Row得到C列最后一个非空单

9、元格所在的行号，.Offset(1, -2)偏移c65536.End(xlUp)得到单元格的位置开始复制后数据的粘贴位置。由C列移到A列。End WithNextEnd Sub注：在VISUAL BASIC中要加入注释，直接在注释文字前加上符号即可，在符号之后的文字会被编译器忽略，也可以使用REM来进行注释，但使用符号会更快捷一些，还可以节省内存及加快编译的效率。如果当前活动工作表的A列填写的是学生的姓名，现在我们想在表中A列最后一个学生的后面增加一个叫“张青”的学生，则代码为：代码:ActiveSheet.Cells(A65536.End(xlUp).Row + 1, 1).Value =

10、张青“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).SelectRange(b65536).End(xlUp).Row 问题高手赐教其含义与区别Range(a65536

11、).End(xlUp).RowRange(b65536).End(xlUp).RowRange(d65536).End(xlUp).RowRange(k65536).End(xlUp).Row看一下vba中END的帮助就知道了。End 属性参阅应用于示例特性返回一个 Range 对象，该对象代表包含源区域的区域尾端的单元格。等同于按键 End+ 向上键、End+ 向下键、End+ 向左键或 End+ 向右键。Range 对象，只读。expression.End(Direction)expression 必需。 该表达式返回“应用于”列表中的对象之一。Direction XlDirection

12、类型，必需。所要移动的方向。XlDirection 可为 XlDirection 常量之一。 xlDown xlToRight xlToLeft xlUp 示例本示例选定包含单元格 B4 的区域中 B 列顶端的单元格。Range(B4).End(xlUp).Select 本示例选定包含单元格 B4 的区域中第 4 行尾端的单元格。Range(B4).End(xlToRight).Select 本示例将选定区域从单元格 B4 延伸至第四行最后一个包含数据的单元格。Worksheets(Sheet1).ActivateRange(B4, Range(B4).End(xlToRight).Selec

13、tRange(a65536).End(xlUp).Row A列数据区域最后一行的行号Range(b65536).End(xlUp).Row B列数据区域最后一行的行号Range(d65536).End(xlUp).Row D列数据区域最后一行的行号Range(k65536).End(xlUp).Row K列数据区域最后一行的行号Range(a65536).End(xlUp).RowA列第65536行之上的最后有数据的单元格所在行数回复Range(b65536).End(xlUp).Row = B列最后一个非空单元格的行数Range(a65536).End(xlUp).Row=A列最后一个非空单

14、元格的行数Range(b65536).End(xlUp).Row = B列最后一个非空单元格的行数Range(d65536).End(xlUp).Row = D列最后一个非空单元格的行数Range(k65536).End(xlUp).Row= K列最后一个非空单元格的行数请教.end(xlUp).row的含义?经常在VBA中常见如下代码：Dim Rng As IntegerRng = sheets1.Range代码详解定义RNG作为一个整型变量第二行的sheets1这种写法是错误的.正确的表述方法应为sheet1或sheets(1)sheet1,对工作表对象的称呼;range(a65536)指

15、一个range对象,具体指第一列最后一行;END,range对象的END属性,也返回一个对象,我理解为基于上述对象的延伸至最后一个RANGE对象.具体延伸的方向要看它给出的参数.比如xlToLeft、xlToRight、xlUp 或 xlDown。ROW返回区域中第一块的第一行的行号。Long 类型，只读多谢两位高手指点，看完以后我是这样理解的，不知对否？ Range(A65536).End(xlUp).Row 是指第一列最后一个有数据单元格的行数，它的下一行即加 1 就是3楼所说的要添加数据的空白单元格汇总工作表数据2010-06-03 00:44方法一：Sub Getdata()Dim E

16、row As Integer, c As Variant, Serow As IntegerErow = Sheets(Total).a65536.End(xlUp).Row + 1If Erow 4 Then Sheets(Total).Rows(4: & Erow).ClearContentsFor Each c In ThisWorkbook.Sheets If c.Name Total Then Serow = c.a65536.End(xlUp).Row Erow = Sheets(Total).a65536.End(xlUp).Row + 1 c.Range(a4:l & Sero

17、w).Copy Destination:=Sheets(Total).Range(a & Erow) End IfNext cEnd Sub方法二Sub SQLConbime()Range(A4:L65536).ClearContentsDim iRow As Integer, sqlTemp As String, sql As String Set cnn = CreateObject(adodb.connection) cnn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= &

18、 ThisWorkbook.FullName For Each sh In Sheets If sh.Name 理数 Then iRow = sh.Range(a3).End(xlDown).Row sqlTemp = select * from & sh.Name & $A3:L & iRow & order by 班别,座号 sql = sqlTemp & union & sql End If Next sql = Left(sql, Len(sql) - 6) Sheets(理数).Range(a4).CopyFromRecordset cnn.Execute(sql) cnn.Clos

19、e: Set cnn = NothingEnd Sub方法三Sub ConbimeAll() Dim yy As Long yy = a65536.End(xlUp).Row + 1 Range(a2:l & yy).ClearContents Set conn = CreateObject(adodb.connection) For i = 1 To 4 conn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= & ThisWorkbook.FullName Sq2 = sele

20、ct * from & Range(p & i) & $a3:l1000 a65536.End(xlUp).Offset(1, 0).CopyFromRecordset conn.Execute(Sq2) conn.Close Next i Set conn = NothingEnd Sub把工作簿中的200多张表复制到一张工作表里？（首选）我是做收款工作的，每天收款情况做一张表格，现在快一年了，今天老板突然要我把这9个月积累的每日数据全部反映到一张工作表上，其实点复制粘贴200多张表不是大问题，主要是怕中途看花了眼，漏了或者重复了就很麻烦，请教高手帮忙，用公式或者VBA，小弟感激不尽。右键点

21、击新工作表标签，选择“查看代码”，将下面的代码粘贴到打开的VBE窗口中：Sub Shcopy()Dim nRow&, R1%, Rs%, Ls%&:长整数型;%:整数型。R1 = 2 每页从第2行开始复制(可修改)Application.EnableEvents = False 如果对指定对象启用事件，则该值为 True。Boolean 类型，可读写Application.ScreenUpdating = FalseFor i = 2 To Sheets.Countsheets.count最后一张工作表。With Sheets(i)Rs = .UsedRange.Rows.Count + 1

22、- 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 属性的

23、功能为调整指定区域的大小。返回 Range 对象，该对象代表调整后的区域。 expression.Resize(RowSize, ColumnSize) expression 必需。该表达式返回要调整大小的 Range 对象。 RowSize Variant 类型，可选。新区域中的行数。如果省略该参数，则该区域中的行数保持不变。 ColumnSize Variant类型，可选。新区域中的列数。如果省略该参数。则该区域中的列数保持不变。Range(a65536).End(xlUp).Offset(1).Resize(Rs, Ls) = arrEnd WithNextApplication.Ena

24、bleEvents = TrueApplication.ScreenUpdating = TrueEnd Sub修改“开始行号”。将光标定位到代码中间任意位置，按F5运行它。关闭VBE窗口。注意单词之间要用空格隔开。Excel多个工作簿中的工作表合并到一个工作簿中 有时，需要将多个Excel工作簿中的工作表合并到一个工作簿中。有多种合并工作簿的情形，下面先给出一种合并多个工作簿的VBA范例，供参考。（此方法将一个工作簿中所有的工作表复制到一张工作表上）方法1Sub CombineWorkbooks()Dim wk As WorkbookDim sh As WorksheetDim strFil

25、eName As StringDim strFileDir As StringDim nm As Stringnm = ThisWorkbook.NamestrFileDir = ThisWorkbook.path & Application.ScreenUpdating = FalsestrFileName = Dir(strFileDir & *.xls)Do While strFileName vbNullStringIf strFileName nm ThenMsgBox strFileNameSet wk = Workbooks.Open(Filename:=strFileDir &

26、 strFileName, ReadOnly:=True)strFileName = Left(Left(strFileName, Len(strFileName) - 4), 29) 取主文件名,除掉.XLSFor Each sh In wk.Sheetssh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)工作表命名,以工作表所在文件名为类If wk.Sheets.Count 1 ThenThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileName &

27、sh.NameElseThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = strFileNameEnd IfNextwk.Close SaveChanges:=FalseEnd IfstrFileName = DirLoopApplication.ScreenUpdating = TrueEnd Sub方法2Sub UnWorksheets()Application.ScreenUpdating = FalseDim lj As StringDim dirname As StringDim nm As StringDim sname As

28、 StringDim i As Integer, ii As Integerlj = ActiveWorkbook.pathnm = ActiveWorkbook.Namedirname = Dir(lj & *.xls) 查找文件Do While dirname If dirname nm ThenWorkbooks.Open Filename:=lj & & dirname 打开文件ii = ActiveWorkbook.Sheets.Count 统计工作表个数复制新打开工作簿的每一个工作表到当前工作表(After:=ThisWorkbook.Sheets(ThisWorkbook.She

29、ets.Count)最后一个后面For i = 1 To iiWorkbooks(dirname).Sheets(i).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)NextWorkbooks(dirname).Close FalseEnd Ifdirname = DirLoopEnd Sub在同一文件夹下有多个工作簿，其中有一个用于汇总的工作簿，将除该汇总工作簿外的其它工作簿中的每一张工作表的数据汇总到该汇总工作簿的一张工作表中。好用！Sub UnionWorksheets()Application.ScreenUpdat

30、ing = False关闭屏幕更新Dim lj As String Dim dirname As StringDim nm As StringDim i As Integer, ii As Integerlj = ActiveWorkbook.path Path 属性。返回指定文件、文件夹、或驱动器的路径。nm = ActiveWorkbook.Name Name属性。指定一个控件或对象的名称或与 Font 对象相关的字体的名称。变量赋值使用=“等号”，“=”后的值可以是单纯的数值、字符串或表达式。dirname = Dir(lj & *.xls) Dir 函数。返回一个String，用以表示一个文件名、目录名或文件夹名称，它必须与指定的模式或文件属性、或磁盘卷标相匹配。Cells.Clear Clear 方法。清除 Err 对象的所有属性设置。Do While dirname 前置式DOLOOP循环。If dirname nm ThenWorkbooks.Open Filename:=lj & & dirname Open 方法。“”为比较运算符“不等于”。ii = ActiveWorkbook.Sh