Creating Pivot Table using VBA

[anoi90]

Hi everyone,

I am new to VBA and now my main task is to writing VBA. I have to create a pivot table that i have to updated it every week (Attached). I could make it with Insert Pivot Table options but I want it work with VBA (since I have to add new data into the Map Sheet weekly, and later if this works, i would have to connect it with a link from my company website so the data can update automatically). But at this point, I only need your help on creating pivot Table and group by month.

Here is my code for the attached.

Sub MakeAPivotTable()

Dim pt As PivotTable
Dim cacheOfpt As PivotCache
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
Sheets("Report").Select
ActiveSheet.PivotTables("PivotTable1").TableRange2.Clear

Sheets("Map").Select
Set cacheOfpt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Table1)

Sheets("Report").Select
Set pt = ActiveSheet.PivotTables.Add(cacheOfpt, Range("a1"), "PivotTable1"

With pt
.PivotFields("Date Mapped").Orientation = xlRowField
.PivotFields("Specialist").Orientation = xlColumnField
.PivotFields("PCMCAT").Orientation = xlDataField
End With

End Sub

Book10.xlsm

Somehow when I tried to run macros, it didn't popup the Pivot Table. I am not sure what went wrong. Any help would be greatly appreciated.

Thank you