一文教你在Excel中利用VBA实现类似「邮件合并」的功能！

在Word中可以用「邮件合并」功能批量制作文档，那Excel怎么办呢？如果现在需要把一个有200行数据的工作表中的每一行数据都按照某一种模板生成一个新的工作表。比如要在Excel中为下面这个学生成绩表中的每个学生按统一的模板制作考试成绩表。

数据和模板放在同一个工作簿中。模板的格式决定最终生成的工作表的格式。

案例使用Excel2010进行演示，主要思路是利用VBA宏自动复制粘贴数据，主要思路如下：

• 计算「数据工作表」所包含的数据量 N，即本案例中学生的人数；
• 循环复制「模板工作表」，并以「数据工作表」中学生的名字命名新复制的「模板工作表」；
• 循环复制「数据工作表」中的内容到新复制的「模板工作表」；
• 把每个工作表另存为工作簿。

工作簿和工作表的区别

一个「工作簿」是由多个「工作表」组成的。我们在工作中经常说的的Excel表格实际是「工作簿」。

计算「数据工作表」中数据的行数N

计算「数据工作表」中数据的行数N，即获取本案例中学生的人数。

首先打开「工作簿」学生考试成绩表.xlsx，鼠标右键单击任意工作表标签，再点击「查看代码」打开VBA窗口，然后点击「ThisWorkbook」打开代码编辑窗口。点击菜单「插入」-「过程」，类型选择「函数」，在名称文本框内输入自定义函数名称「CopyTheData」，在编辑窗口内添加一个「函数」。代码如下：

复制并重命名模板工作表

通过For循环复制「模板工作表」，并以「数据工作表」中学生的名字命名新复制添加的工作表。代码如下：

代码中有这样一句「Sheets(i + 2).Name = Sheets('数据').Cells(i + 1, 2)」需要重点理解。其中「Cells属性」非常重要，是什么意思呢？一起来看一下Excel帮助文档关于「Cells属性」的解释吧，就是代表工作表中的一个单元格。

在「Cells属性」后面紧接着指定的行索引和列索引：Cells(行索引,列索引)。

因此「Sheets(i + 2).Name = Sheets('数据').Cells(i + 1, 2)」这句代码的意思就是：把名称为「数据」的工作表中行索引为i+1，列索引为2的单元格的内容赋值给第i+2个工作表的标签名，就是重命名工作表标签。

VBA复制粘贴数据

为便于调试，我们增加了一个函数「DeleteSheets」用于批量删除除「数据工作表」和「模板工作表」以外的工作表，并在「CopyTheData」中进行调用。

完整的「DeleteSheets」代码：

现在需要依次把「数据工作表」中的每一行数据复制到新添加的「数据工作表」中。其中一行最关键代码为：「Sheets(i).Cells(x,y) = Sheets('数据').Cells(j, z)」

意思就是把名称为「数据」的工作表中行索引为j，列索引为z的单元格的内容赋值给第i个「工作表」中行索引为x，列索引为y的单元格。在本案例中就是把「数据工作表」中每个学生的每一项信息复制到新添加的「模板工作表」中。

复制处理数据函数「CopyTheData」的全部代码为：

运行「CopyTheData」：

发现6个工作表自动复制粘贴完成，是不是速度很快！？

把每一个工作表另存为工作簿Excel文件

完成上一步后，每个学生考试成绩表都是一个工作表，如果需要把这些工作表都另存为工作簿，即保存为单独的xlsx或xls文件，该怎么办？

这里可以再添加一个函数「SplitAndSaveFiles」，并调用「CopyTheData」。

运行「SplitAndSaveFiles」，全部自动完成，速度快而且容错率高。

好了，关于利用VBA批量生成工作表，批量保存工作表为工作簿的技巧就分享到这里，如果你还有其他技巧，可以在文章下进行留言哦~

文中代码及文件获取方式：私信回复「0425」即可下载。