VBA拆分工作表

VBA拆分工作表

Excel界面操作为我们提供了各种强大的功能，我们首先应该充分利用Excel中的内置功能，在利用VBA编程时也是如此，这是我们学习Excel VBA的原则之一。

本节实例：

在工作中，我们可能会遇到一张很复杂、繁冗的表格，表格中的数据时公司各个部门的详细数据清单，我们的任务是：把这个总数据表分解成一张张易于分析的数据表。当表格内容很多时，利用VBA是一种不错的方法。

假设有如图所示的数据，我们的任务是按照“地区”把原始数据拆分成一个工作表。我们该如何做呢？

常规思路

首先用VBA按照“地区”列对原始数据排序；然后用VBA的循环处理结构查看每一条数据，如果“地区”列中当前单元格和上一个单元格中的地区有所不同，就新建一个工作表来保存该地区的数据。

另一种思路

利用数据工作表的功能实现数据拆分。在数据透视表中，如果双击数据区域中的某一个单元格，在默认情况下，Excel会自动生成一个单独的该单元格背后所用到的所有原始数据的工作表。下面我们通过实际操作来了解这个功能，并录制一个宏作为VBA解决方案的起点。

具体操作

单击“开发工具”——“代码”功能组中的“录制宏”按钮，按照下述步骤开始录制宏。注意，为了让Excel宏尽量少的录制无关代码，我们应该尽量减少不必要的动作。

首先将光标置于数据区域中的任意单元格，按快捷键“Ctrl + A”选择整个数据区域，然后单击“插入”——“数据透视表”——“数据透视表”，在“创建数据透视表”对话框中进行如下设置。

单击“确定”按钮，在工作表右侧弹出“数据透视表字段列表”窗格。进行数据透视表布局设置，此时我们得到的数据透视表如下图所示：

下面我们开始演示一个常用的操作技巧

操作技巧

双击数据透视表“数据区域”中的任意一个单元格，比如B5单元格。B5单元格对应的是“东部”区域的所有原始数据。如果双击“B6”单元格，我们又得到了“南部”区域的所有原始数据工作表。我们将用VBA调用Excel数据透视表的这个功能，将原始数据拆分成多个工作表。

现在，我们单击“开发工具”——“代码”功能组中的“停止录制”按钮，停止宏的录制，然后按快捷键“ALT + F11”进入Excel VBA的开发环境，在“模块一”中，我们看到刚才录制的代码如下：

在上述Excel宏所录制的代码中，我们第一个要关心的是加粗部分的代码，这段代码的作用是在计算机内存中创建一个“数据透视表数据加工区（Pivot Cache）”

这里的ActiveWorkbook.PivotCaches的Create方法有多个参数，其中的SourceData:="表1"代表数据透视表的原始数据范围，是一个表示数据范围地址的字符串。为了增加程序的灵活性，我们在编写VBA代码时，根据原始数据范围的大小自动生成这个字符串参数，如果原始数据所在的工作表叫做“Sheet1”，那么表示原属数据范围的地址字符串可以由如下代码得到：

strDataAddr = "sheet1!" & Worksheets("sheet1").UsedRange.Address

我们再定义一个Excel工作表对象pivotSht,用来代表数据透视表所在的工作表，那么创建Pivot Cache代码的另一个表示数据透视表在工作表位置的参数TableDestination:="Sheet4!R3C1"中的"Sheet4!R3C1"就可以修改为pivotSht.Name & “!R3C1” ,这里的R3C1是Excel单元格地址的另一种表示方法，代表第3行(R代表Row）

第1列（C代表Column）处的单元格。

宏中最后4行代码是双击数据透视表“数据区域”中的单元格生成拆分工作表时所录制的VBA代码，这是我们手动操作生成的，如果用VBA自动拆分工作表的话，那么显然需要我们使用VBA中的循环处理语句自动完成这个工作。

Range("B4").Select

Selection.ShowDetail = True

由上图可以看出，数据区域从B4单元格开始，我们只要从B4单元格开始，对下面的每一个单元格调用Selection.ShowDetail = True即可，直到遇到空白单元格为止。根据这个思路，这部分代码修改如下：

Dim myCell As Range

Set myCell = pivotSht.Range("B4")

Do Until myCell.Value = ""

myCell.ShowDetail = True

ActiveSheet.Name = myCell.Offset(0, -1).Value

Set myCell = myCell.Offset(1, 0)

Loop

下面是录制宏修改后的完整代码。

下面是录制宏修改后的完整代码。

效果图

本节我们介绍了用VBA把工作表按照某种类别拆分成独立的工作表，以录制宏为解决方案的起点，充分调用Excel的内置功能，并对所录制的宏进行简单修改以实现我们想要的目标。

注释：

在代码段的后面+ “空格” +“_” + “Enter”为换行符

换行符_

以上为换行符，在本节代码的多处频繁使用，请予以注意。