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」為換行符

換行符_

以上為換行符，在本節代碼的多處頻繁使用，請予以注意。