【原】Excel VBA工作表 7.8 单个工作薄所有工作表数据合并数据规则100+都不怕

Excel和VBA 2021-03-31

展开全文

前景提要

通过之前的几节的分享，小伙伴们应该能够熟练的掌握工作表的新建和重命名等一些简单的操作了，既然已经有了一些基本的基础之后，我们现在开始增加难度，学习一些更加实用性的操作吧，今天我们来分享下批量合并单个工作薄下所有工作表的数据

工作薄？工作表？有什么不同，还比较懵的小伙伴们建议看看这一系列的第一节分享，这里就不再重复说明了，批量合并工作表在大家的日常工作中是非常常见的，因为很小伙伴在我一开始说工作表的时候，就说到这个问题了，接下来我们进入实际的场景模拟，综合实例来学习下批量工作表的合并操作

场景模拟

还是用我们之前的例子吧，假设当前工作薄内有3个班次的所有学生的VBA和python的考试成绩

现在我们想要知道本年级所有的学生的考试成功，就需要将这几个表汇总在一起，不然的话，没有办法针对整体进行分析

一个个的复制粘贴明显很明显，并不是我们想要的效果，虽然眼下只有3个工作表，但是如果有10+,20+呢？

我们还是学习系VBA的做法，这样能够更好的提升工作效率

代码区

首先我们来查看下数据，每个报表的数据结构都是相同的，这是一个标准结构的数据，那就非常简单了，上代码

Sub testadd()Dim sth As Worksheet, new_sth As WorksheetWorksheets.Add after:=Worksheets(Worksheets.Count)ActiveSheet.Name = "汇总表"Set new_sth = ActiveSheetk = 0For Each sth In Worksheets If sth.Name <> "汇总表" Then If Not sth.UsedRange Is Nothing Then k = k + 1 If k = 1 Then sth.UsedRange.Copy new_sth.Cells(1, 1) Else l = new_sth.Cells(Rows.Count, 1).End(xlUp).Row sth.UsedRange.Offset(1, 0).Copy new_sth.Cells(l + 1, 1) End If End If End IfNext sthEnd Sub

来看看代码执行的效果

程序新建了一个工作表，命名为汇总表，将所有汇总的数据都合并在这个工作表中，方便区分

并且汇总表中也是涵盖了所以的数据，一个都没有少。每个表的10个数据都汇总过来了，没有损失数据。

代码分析

首先我们为了方便进行区分，新建一个工作表，将所有的数据都汇总在这个新建的工作表中，工作表的新建和重命名，之前已经分享过了，想必大家都非常的熟悉了。

Worksheets.Add after:=Worksheets(Worksheets.Count)ActiveSheet.Name = "汇总表"

大家会留意到这里有一点不同，Worksheets.Count这个代码的意思是代表当前所有的工作表的总和，比方说现在有4个工作表，一个新建+原来的3个班班级的工作表

后面的代码就非常的简单了

For Each sth In Worksheets If sth.Name <> "汇总表" Then If Not sth.UsedRange Is Nothing Then k = k + 1 If k = 1 Then sth.UsedRange.Copy new_sth.Cells(1, 1) Else l = new_sth.Cells(Rows.Count, 1).End(xlUp).Row sth.UsedRange.Offset(1, 0).Copy new_sth.Cells(l + 1, 1) End If End If End IfNext sth

这一段代码就是循环遍历所有的工作表，只要工作表的名称不等于我们的汇总表，那么就执行汇总

这里讲一个新的知识点sth.UsedRange，代表的就是当前工作表的所有被使用区域，没有被使用的区域不算在内的，这样就可以将所有的有数据的区域全部汇总起来，不需要再通过Cells(Rows.Count, 1).End(xlUp).Row来进行判断最后一个非空单元格了，我们来具体看看sth.UsedRange的效果

我们这里执行sth.UsedRange.select方便大家识别下