Using Access to automate a Pivot Table in Excel (1 Viewer)

'Insert Data Field
With ActiveSheet.PivotTables("PaidPivotTable").PivotFie lds("Sum of Premium Credit")
.Orientation = xlDataField
.Function = xlCount
.Name = "Sum of Premium Credit"
End With
With ActiveSheet.PivotTables("PaidPivotTable").PivotFie lds("Sum of Policy Count")
.Orientation = xlDataField
.Function = xlCount
.Name = "Sum of Policy Count"
End With

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com
    Dim xls As Excel.Application
    Dim wkb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim pvtCache As Excel.PivotCache
    Dim pvt As Excel.PivotTable
    Dim StartPvt As String
    Dim SrcData As String

    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("C:\Temp\PivotTableVBA.xlsm")

    'Determine the data range you want to pivot
    SrcData = "Sheet1!" & Range("A1:T4").Address(ReferenceStyle:=xlR1C1)

    'Create a new worksheet
    Set sht = wkb.Sheets.Add

    'Where do you want Pivot Table to start?
    StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                   SourceType:=xlDatabase, _
                   SourceData:=SrcData)

    'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable( _
              TableDestination:=StartPvt, _
              TableName:="PaidPivotTable")

    'Insert Row Fields
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Number")
        .Orientation = xlRowField
        .Position = 1
    End With

    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Number")
        .Orientation = xlRowField
        .Position = 1
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Agent State")
        .Orientation = xlRowField
        .Position = 3
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Home Agency Number")
        .Orientation = xlRowField
        .Position = 4
    End With
    With sht.PivotTables("PaidPivotTable").PivotFields("Home Agency Name")
        .Orientation = xlRowField
        .Position = 5
    End With

    'Insert Column Fields
    With sht.PivotTables("PaidPivotTable").PivotFields("Year")
        .Orientation = xlColumnField
        .Position = 1
    End With

    'Insert Data Field
    'With sht.PivotTables("PaidPivotTable").PivotFields("Sum of Premium Credit")
    '.Orientation = xlDataField
    '.Function = xlCount
    '.Name = "Sum of Premium Credit"
    'End With
    'With sht.PivotTables("PaidPivotTable").PivotFields("Sum of Policy Count")
    '.Orientation = xlDataField
    '.Function = xlCount
    '.Name = "Sum of Policy Count"
    'End With

    sht.PivotTables("PaidPivotTable").AddDataField sht.PivotTables( _
                "PaidPivotTable").PivotFields("Premium Credit"), "Sum of Premium Credit", xlSum

    sht.PivotTables("PaidPivotTable").AddDataField sht.PivotTables( _
                "PaidPivotTable").PivotFields("Policy Count"), "Sum of Policy Count", xlSum

    'Format Pivot Table
    sht.PivotTables("PaidPivotTable").ShowTableStyleRowStripes = True
    sht.PivotTables("PaidPivotTable").TableStyle2 = "PivotStyleMedium9"
    sht.PivotTables("PaidPivotTable").RowAxisLayout xlTabularRow
    sht.PivotTables("PaidPivotTable").NullString = "0"

    'Hide subtotals
    sht.PivotTables("PaidPivotTable").PivotFields("Agent Number").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    sht.PivotTables("PaidPivotTable").PivotFields("Agent Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    sht.Columns("B:N").AutoFit

    Set pvt = Nothing
    Set pvtCache = Nothing
    Set sht = Nothing
    Set wkb = Nothing
    xls.Visible = True

End Sub