Sub CreatePivotTable() Dim Pvc As PivotCache Dim Pvt As PivotTable Dim strSourceData As String Dim strTableDestination As String Dim strTableName As String Sheet2.Cells.Clear strSourceData = Sheet1.UsedRange.Address strTableDestination = "Sheet2!R3C1" strTableName = "資料透視表1" Set Pvc = ActiveWorkbook.PivotCaches.Create(xlDatabase, strSourceData, xlPivotTableVersion10) Set Pvt = Pvc.CreatePivotTable(strTableDestination, strTableName) Pvt.AddFields RowFields:=Array("姓名", "淨營業額"), ColumnFields:=Array("購貨日期", "優惠顧客編號") With ActiveSheet.PivotTables("資料透視表1").PivotFields("購貨金額") .Orientation = xlDataField .Caption = "加總的購貨金額" .Position = 1 .Function = xlSum End With End Sub