在Word中可以用「郵件合并」功能批量制作文檔，那Excel怎么辦呢？如果現(xiàn)在需要把一個(gè)有200行數(shù)據(jù)的工作表中的每一行數(shù)據(jù)都按照某一種模板生成一個(gè)新的工作表。比如要在Excel中為下面這個(gè)學(xué)生成績(jī)表中的每個(gè)學(xué)生按統(tǒng)一的模板制作考試成績(jī)表。

數(shù)據(jù)和模板放在同一個(gè)工作簿中。模板的格式?jīng)Q定最終生成的工作表的格式。

案例使用Excel2010進(jìn)行演示，主要思路是利用VBA宏自動(dòng)復(fù)制粘貼數(shù)據(jù)，主要思路如下：

• 計(jì)算「數(shù)據(jù)工作表」所包含的數(shù)據(jù)量 N，即本案例中學(xué)生的人數(shù)；
• 循環(huán)復(fù)制「模板工作表」，并以「數(shù)據(jù)工作表」中學(xué)生的名字命名新復(fù)制的「模板工作表」；
• 循環(huán)復(fù)制「數(shù)據(jù)工作表」中的內(nèi)容到新復(fù)制的「模板工作表」；
• 把每個(gè)工作表另存為工作簿。

工作簿和工作表的區(qū)別

一個(gè)「工作簿」是由多個(gè)「工作表」組成的。我們?cè)诠ぷ髦薪?jīng)常說的的Excel表格實(shí)際是「工作簿」。

計(jì)算「數(shù)據(jù)工作表」中數(shù)據(jù)的行數(shù)N

計(jì)算「數(shù)據(jù)工作表」中數(shù)據(jù)的行數(shù)N，即獲取本案例中學(xué)生的人數(shù)。

首先打開「工作簿」學(xué)生考試成績(jī)表.xlsx，鼠標(biāo)右鍵單擊任意工作表標(biāo)簽，再點(diǎn)擊「查看代碼」打開VBA窗口，然后點(diǎn)擊「ThisWorkbook」打開代碼編輯窗口。點(diǎn)擊菜單「插入」-「過程」，類型選擇「函數(shù)」，在名稱文本框內(nèi)輸入自定義函數(shù)名稱「CopyTheData」，在編輯窗口內(nèi)添加一個(gè)「函數(shù)」。代碼如下：

復(fù)制并重命名模板工作表

通過For循環(huán)復(fù)制「模板工作表」，并以「數(shù)據(jù)工作表」中學(xué)生的名字命名新復(fù)制添加的工作表。代碼如下：

代碼中有這樣一句「Sheets(i + 2).Name = Sheets('數(shù)據(jù)').Cells(i + 1, 2)」需要重點(diǎn)理解。其中「Cells屬性」非常重要，是什么意思呢？一起來看一下Excel幫助文檔關(guān)于「Cells屬性」的解釋吧，就是代表工作表中的一個(gè)單元格。

在「Cells屬性」后面緊接著指定的行索引和列索引：Cells(行索引,列索引)。

因此「Sheets(i + 2).Name = Sheets('數(shù)據(jù)').Cells(i + 1, 2)」這句代碼的意思就是：把名稱為「數(shù)據(jù)」的工作表中行索引為i+1，列索引為2的單元格的內(nèi)容賦值給第i+2個(gè)工作表的標(biāo)簽名，就是重命名工作表標(biāo)簽。

VBA復(fù)制粘貼數(shù)據(jù)

為便于調(diào)試，我們?cè)黾恿艘粋€(gè)函數(shù)「DeleteSheets」用于批量刪除除「數(shù)據(jù)工作表」和「模板工作表」以外的工作表，并在「CopyTheData」中進(jìn)行調(diào)用。

完整的「DeleteSheets」代碼：

現(xiàn)在需要依次把「數(shù)據(jù)工作表」中的每一行數(shù)據(jù)復(fù)制到新添加的「數(shù)據(jù)工作表」中。其中一行最關(guān)鍵代碼為：「Sheets(i).Cells(x,y) = Sheets('數(shù)據(jù)').Cells(j, z)」

意思就是把名稱為「數(shù)據(jù)」的工作表中行索引為j，列索引為z的單元格的內(nèi)容賦值給第i個(gè)「工作表」中行索引為x，列索引為y的單元格。在本案例中就是把「數(shù)據(jù)工作表」中每個(gè)學(xué)生的每一項(xiàng)信息復(fù)制到新添加的「模板工作表」中。

復(fù)制處理數(shù)據(jù)函數(shù)「CopyTheData」的全部代碼為：

運(yùn)行「CopyTheData」：

發(fā)現(xiàn)6個(gè)工作表自動(dòng)復(fù)制粘貼完成，是不是速度很快??？

把每一個(gè)工作表另存為工作簿Excel文件

完成上一步后，每個(gè)學(xué)生考試成績(jī)表都是一個(gè)工作表，如果需要把這些工作表都另存為工作簿，即保存為單獨(dú)的xlsx或xls文件，該怎么辦？

這里可以再添加一個(gè)函數(shù)「SplitAndSaveFiles」，并調(diào)用「CopyTheData」。

運(yùn)行「SplitAndSaveFiles」，全部自動(dòng)完成，速度快而且容錯(cuò)率高。

好了，關(guān)于利用VBA批量生成工作表，批量保存工作表為工作簿的技巧就分享到這里，如果你還有其他技巧，可以在文章下進(jìn)行留言哦~

文中代碼及文件獲取方式：私信回復(fù)「0425」即可下載。