一.用數(shù)據(jù)透視表向?qū)?chuàng)建透視表的方法：
Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase,SourceData:=Range(“Sheet1!A4:E250”)
用PivotTableWizard方法，創(chuàng)建一個透視表，2個參數(shù)，一個SourceType數(shù)據(jù)源類型，采用excel數(shù)據(jù)庫；另外一個SourceData數(shù)據(jù)源，采用工作表Sheet1!A4:E250單元格區(qū)域，透視表的放置位置在當前活動的單元格。
當然，你也可以給數(shù)據(jù)透視表指定放置的詳細位置，例如，把它放在D4單元格，透視表命名為:=”My Pivot Table”，代碼變?yōu)椋?/p>
Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, SourceData:=Range(“Sheet1!A4:E250”), _
TableDestination:=Range(“D4”), TableName:=”My Pivot Table”
最后，你還可以指定“行合計”和“列合計”，則代碼變?yōu)?
Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, SourceData:=Range(“Sheet1!A4:E250”), _
TableDestination:=Range(“D4”),TableName:=”My Pivot Table”, _
RowGrand:=True, ColumnGrand:=True
我們一般使用3個參數(shù)即可，其它的省略：
Worksheets(“Sheet2”).PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Range(“Sheet1!A4:C28”),TableDestination:=Range(“B2”)
二.用PivotCache透視表內(nèi)存緩存創(chuàng)建一個數(shù)據(jù)透視表：
Public Sub CreatePivotTable()
Dim wb As Workbook ’聲明3個變量！
Dim pt As PivotTable
Dim pc As PivotCache
On Error GoTo ErrorHandler
‘ Open the workbook.即設置打開一個工作簿的變量。
Set wb = Workbooks.Open(“c:\PivotData\VideoStoreRawData.xls”)
‘ Create the PivotCache.創(chuàng)建一個透視表內(nèi)存緩存！
Set pc = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=”[VideoStoreRawData.xls]Sheet1!A4:C28”)
‘ 采用CreatePivotTable方法，利用透視表緩存創(chuàng)建一個數(shù)據(jù)透視表！
Set pt = pc.CreatePivotTable TableDestination:=”[VideoStoreRawData.xls]Sheet2! “,TableName:=”Video Data”
‘下面是錯誤處理程序，可以借鑒一下，很常用的方法！
wb.Worksheets(“Sheet2”).Activate
EndOfSub:
Exit Sub
ErrorHandler:
If Err.Number = 5 Or Err.Number = 9 Then
MsgBox “The file could not be found”
ElseIf Err.Number = 1004 Then
MsgBox “There is already a PivotTable at that location”
Else
MsgBox “Error “ & Err & “ - “ & Err.Description
End If
Resume EndOfSub
End Sub
以上2種方法，創(chuàng)建出一個奇怪的4個單元格形狀的數(shù)據(jù)透視表，透視表中沒有任何字段！
下面是添加行字段，列字段和頁字段的方法：
用Addfields方法，來添加，它的語法是：
AddFields(RowFields, ColumnFields, PageFields, _ AddToTable)
如果你只添加一個字段，例如：
Worksheets(“Sheet1”).PivotTables(1).AddFields _
(ColumnFields:=”Region”, AddToTable:=True)
如果添加多個字段，則用Array來添加：
myPivotTable.AddFields(RowFields:=Array(“Status”, “DueDate”))
下面的代碼是一個完整的例子，包括行，列和頁字段的添加：
myPivotTable.AddFields(RowFields:=”Region”, _
ColumnFields:=”Quarter”, _
PageFields:=Array(“Status”, “DueDate”))
下面繼續(xù)介紹向數(shù)據(jù)區(qū)域添加數(shù)據(jù)字段的方法：
如果你只添加一個數(shù)據(jù)字段，則用AddDataField方法，它的語法是：
AddDataField(Field,Caption,Function)
Field參數(shù)的表示方法：PivotFields(Name)
例子如下：
Dim pt As PivotTable
Set pt = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
pt.AddDataField pt.PivotFields(“Sales”), “Total Sales”
下面是一個創(chuàng)建數(shù)據(jù)透視表的完整例子：
Public Sub CreateCompletePivotTable()
Dim wb As Workbook
Dim pt As PivotTable
On Error GoTo ErrorHandler
‘ Open the workbook.
Set wb = Workbooks.Open(“c:\PivotData\VideoStoreRawData.xls”)
‘ Create the PivotTable and get a reference to it.
Set pt = Worksheets(“Sheet2”).PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=Range(“Sheet1!A4:C28”), _
TableDestination:=Range(“Sheet2!B2”))
‘ Add row and column fields.
pt.AddFields RowFields:=”Store”, ColumnFields:=”Category”
‘ Add data field.
pt.AddDataField pt.PivotFields(“Titles”), “Total Titles”
EndOfSub:
Exit Sub
ErrorHandler:
If Err.Number = 5 Or Err.Number = 9 Then
MsgBox “The file could not be found”
ElseIf Err.Number = 1004 Then
MsgBox “There is already a PivotTable at that location”
Else
MsgBox “Error “ & Err & “ - “ & Err.Description
End If
Resume EndOfSub
End Sub
