20170517xlVBA Add pivot Table

Sub addpovittable () ' Constance const data_sheet As String = "Advanced Filter" Const data_address As String = "r7c  1:r107c11 "Const Pivot_sheet As String =" Pivotsheet "' SHEET Name with No space Const pivot_address As String = "R3c1" Const pivot_table as String = "PIVOT TABLE" ' Object Dim Wb As Workbook Dim Sht As Worksheet Dim PvtT Able as PivotTable Set Wb = ThisWorkbook ' do not display Alert when delete a sheet Application.displa Yalerts = False for each Sht in wb.worksheets If sht.name = Pivot_sheet then sht.delete Next Sht Applicati Mnl DisplayAlerts = True ' Add A new sheet for pivot table Set Sht = Wb.Worksheets.Add (After:=wb.worksheets (wb.worksheets .                                         Count)) Sht.name = Pivot_sheet ' Create a new PIVOT table Set pvttable = Wb.PivotCaches.Create (_  Sourcetype:=xldatabase, _ Sourcedata:=data_sheet & "!" & Data_address, _ Version:=xlpivottableversion15). _ Createpivottable (_ Tabledestination:=pivot_sheet & "!" & P Ivot_address, _ tablename:=pivot_table, _ Defaultver SION:=XLPIVOTTABLEVERSION15) ' Set fields with pvttable. PivotFields ("state"). Orientation = Xlrowfield. PivotFields ("state"). Position = 1. PivotFields ("City"). Orientation = Xlrowfield. PivotFields ("City"). Position = 2. PivotFields ("Salesperson"). Orientation = Xlrowfield. PivotFields ("Salesperson"). Position = 3. PivotFields ("Payment"). Orientation = Xlrowfield. PivotFields ("Payment"). Position = 4. PivotFields ("Transport"). Orientation = Xlrowfield. PivotFields ("Transport"). Position = 5. PivotFields ("Month"). Orientation = Xlrowfield. PivotFields ("Month"). Position = 6. Adddatafield.PivotFields ("Product a"), "sum:product a", xlsum. Adddatafield. PivotFields ("Product B"), "Sum:product B", Xlsum. Adddatafield. PivotFields ("Product C"), "Sum:product C", Xlsum End with ' Release Objects set Wb = Nothing Set Sht = No Thing Set pvttable = NothingEnd Sub

　　

20170517xlVBA Add pivot Table