In our below example we have a data of employee data dashboard across locations.

Here instead of using the conventional method for creating a Pivot Table, we use Excel VBA macro instead to represent our required dashboard.

This code can be used in combination with our previous excel macro series example.

Eg: Extract active employee data onto a separate sheet and then create a pivot table based on the results.

Also we have created a code which will delete the Pivot charts in the workbook at the click of a command button.

Download Code Macro_Pivot Table

Download-Excel-Macro-pivot

Sub Create_PivotTable()

    Dim pc As PivotCache
    Dim PT As PivotTable
    Dim pf As PivotField
        
        'wsdata is the name of the worksheet
    If ThisWorkbook.PivotCaches.Count = 0 Then
        Set pc = ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=wsdata.Name & "!" & wsdata.Range("A1").CurrentRegion.Address, _
            Version:=xlPivotTableVersion15)
    Else
        Set pc = ThisWorkbook.PivotCaches(1)
    End If
    
    Worksheets.Add
    Range("A3").Select
    
    Set PT = pc.CreatePivotTable( _
        Tabledestination:=ActiveCell, _
        TableName:="dataPivot")
        
    ' Row parameter for pivot table
    Set pf = PT.PivotFields("Location")
    pf.Orientation = xlRowField
    
    ' Column parameter for pivot table

    Set pf = PT.PivotFields("Department")
    pf.Orientation = xlColumnField

    ' Sum /Average /COunt etc parameter for pivot table
    Set pf = PT.PivotFields("Name")
    pf.Orientation = xlDataField
    ' you can use functions like xlsum, xlaverage
    pf.Function = xlCount

End Sub

Sub DeleteAllPivotTablesInWorkbook()
Dim WS as Worksheet
        On Error Resume Next
    For Each WS In ActiveWorkbook.Worksheets
        For Each PT In WS.PivotTables
            WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp
        Next PT
    Next WS
End Sub