按目錄批次新建指定工作表

所用知識點：

迴圈，陣列，生成、重新命名及隱藏工作表，生成超連結等。

這兩天在學透視表，沒想到透視表居然能批次生成工作簿，真厲害呀。不過VBA也是可以寫的，所以本篇就來寫一寫怎麼按目錄批次生成工作簿。

只實現一個功能太單調了，既然是目錄，那就批次加上超連結吧，方便跳轉~

Let‘s go！

一、需求：

根據目錄生成對應月份的資產負債表（複製模板表），以目錄上的名稱（年月）命名工作表，並修改表內的年月。

2。 在目錄建立對應工作表的超連結，然後在對應工作表I1單元格建立返回目錄的超連結。

3。 隱藏“模板”工作表。

目錄

模板

可以下載檔案，先思考做一下，然後再看我的做法~

A002按目錄批次生成工作表。xlsx

18。7K

·

百度網盤

二、分步解題：

根據目錄生成對應月份的資產負債表（複製模板表），以目錄上的名稱（年月）命名工作表，並修改表內的年月。

① 獲取目錄內容，放入陣列

dim arr

arr = Sheets（“目錄”）。Range（“B3：B” & Cells（“B65535”）。End（xlUp）。Row）

Cells（“B65535”）。End（xlUp）。Row 獲取目錄的最後一個單元格

② 複製“模板”工作表（不記得可以用錄製宏獲取程式碼）

Sheets（“模板”）。Copy After：=Sheets（ThisWorkbook。Sheets。Count）

ThisWorkbook。Sheets。Count是計算當前工作表數，把複製的模板工作表放在最後。

③ 套入迴圈，迴圈上一步、重新命名生成的工作表、修改表內的年月（E3單元格）

For i = 1 To UBound（arr）

’複製“模板”工作表到最後

Sheets（“模板”）。Copy After：=Sheets（ThisWorkbook。Sheets。Count）

‘按目錄年月重新命名

Sheets（i + 2）。Name = arr（i， 1）

’修改表內的年月

Sheets（i + 2）。Range（“E3”） = arr（i， 1）

Next i

2. 在目錄建立對應工作表的超連結，然後在對應工作表I1單元格建立返回目錄的工作表。

建立超連結Hyperlinks。Add，語法如下：Expression一般是工作表。

Expression。Hyperlinks。Add（Anchor， Address， SubAddress， ScreenTip， TextToDisplay）

① 目錄建立對應工作表的超連結（以B3單元格“2020年01月“為例）

Sheets（“目錄”）。Hyperlinks。Add anchor：=Sheets（“目錄”）。Range（“B3”）， Address：=“”， SubAddress：=“2020年01月！A1”， ScreenTip：=“進入”

② 在對應工作表的I1單元格建立返回目錄的超連結（以“2020年01月“工作表為例）

Sheets（“2020年01月”）。Hyperlinks。Add anchor：=Range（“I1”）， Address：=“”， SubAddress：=“目錄！A1”， ScreenTip：=“跳轉到目錄工作表”， TextToDisplay：=“返回目錄”

③ 套上迴圈

For i = 1 To UBound（arr）

Sheets（“目錄”）。Hyperlinks。Add anchor：=Sheets（“目錄”）。Range（“B” & i + 2）， Address：=“”， SubAddress：=arr（i， 1） & “！A1”， ScreenTip：=“進入”

‘在目錄建立超連結

Sheets（i + 2）。Hyperlinks。Add anchor：=Range（“I1”）， Address：=“”， SubAddress：=“目錄！A1”， ScreenTip：=“跳轉到目錄工作表”， TextToDisplay：=“返回目錄”

’在對應工作表建立返回的超連結

Next i

3. 隱藏“模板”工作表

Sheets（“模板”）。Visible = False

三、完整程式碼及演示

需求1和2的迴圈其實是一樣的，可以把它們合在一個迴圈內。

Sub Addworksheets（）

Dim arr

Dim i As Integer， k As Integer

Application。ScreenUpdating = False

‘關閉螢幕重新整理

arr = Sheets（“目錄”）。Range（“B3：B” & Range（“B65535”）。End（xlUp）。Row）

’獲取目錄內容，放入陣列

For i = 1 To UBound（arr）

Sheets（“模板”）。Copy After：=Sheets（ThisWorkbook。Sheets。Count）

‘複製“模板”工作表到最後，ThisWorkbook。Sheets。Count獲取當前工作表數量

Sheets（i + 2）。Name = arr（i， 1）

’按目錄年月重新命名

Sheets（i + 2）。Range（“E3”） = arr（i， 1）

‘修改表內的年月

Sheets（“目錄”）。Hyperlinks。Add anchor：=Sheets（“目錄”）。Range（“B” & i + 2）， Address：=“”， SubAddress：=arr（i， 1） & “！A1”， ScreenTip：=“進入”

’在目錄建立超連結

Sheets（i + 2）。Hyperlinks。Add anchor：=Range（“I1”）， Address：=“”， SubAddress：=“目錄！A1”， ScreenTip：=“跳轉到目錄工作表”， TextToDisplay：=“返回目錄”

‘在對應工作表建立返回的超連結

Next i

Sheets（“模板”）。Visible = False

’隱藏“模板”工作表

Sheets（“目錄”）。Select

MsgBox “已生成目錄中的工作表”

‘最後跳轉回目錄，並提示已完成生成

Application。ScreenUpdating = True

’恢復螢幕重新整理

End Sub

執行時一閃一閃，其實是因為我忘記寫關閉螢幕重新整理了……

另外，有一個小地方需要注意，就是目錄裡的內容。我輸入的其實是“2020年01月”，顯示也是“2020年01月”，但實際單元格內容卻是“2020/01/01”，Excel自動把它轉換為日期格式，這樣會導致工作表命名出錯，因為工作表名不可以含“/”，所以需要把單元格格式修改為文字值。

※用透視表也可以批次生成工作表哦！

選中目錄內容，插入透視表，在資料透視表字段把目錄拉到篩選器區間裡。

2。 在選單欄-分析-選項，點選“顯示報表篩選頁”，就會按目錄生成工作表啦~

3。 選中生成的12個工作表操作清除透視表（選中的工作表會被共同操作），再複製模板，然後選中那12個工作表，操作複製。這樣，所有的工作表都和模板一致了。

想把專欄做得更專業些，寫很實用的程式碼，可以直接套用的。想選題就想了很久，也看了很多Excel大神寫的文章，但發現程式碼再實用也很難做到所有場景都通用，因為它是透過規則制定的，所以肯定會有限制的地方。

我想，我更想做到的應該是，分享我的所學，儘可能寫出貼合實際需求的程式碼，也希望給讀者這樣的啟發，可能這樣的分享會更有意義？當然我現在能力也不足，寫一篇文要寫一個下午呢，得多練多寫呀，如果有哪裡寫得不對，也請大家多多指教~

本次的程式碼檔案：

A002按目錄批次生成工作表。xlsm

130K

·

百度網盤