VBA Pivot Table

[chaddres]

I need to make a pivot table with a macro. I cannot use XL2BB but I am attaching a screenshot of my data. When I run the macro, I get an error on the "With ActiveSheet.PivotTables("pTable1")" line. Any help is appreciated. If there is another way to provide sample data, I would gladly do so.

Sub PivotTable()
'
' Macro1 Macro
'

'
    Sheets("Payroll Data").Select
    Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = ActiveWorkbook.Worksheets.Add
    sht1.Name = "Pivot Table"
    Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
        sht1.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
    Sheets("Pivot Table").Select
    Cells(2, 2).Select
    With ActiveSheet.PivotTables("pTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("pTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("pTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee_Number")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("pTable1").PivotFields("CC1")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Regular_Hours"), "Sum of Regular_Hours", xlSum
End Sub

 

[chaddres]

Update: I have everything working now except my "Sum" (last two lines of code). Any ideas?

Sub PivotTable()
'
' Macro1 Macro
'

'
    Sheets("Payroll Data").Select
    Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = ActiveWorkbook.Worksheets.Add
    sht1.Name = "Pivot Table"
    Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
        sht1.Cells(1, 1), TableName:="pTable1" & Format(Time, "hhmmss"))
    Sheets("Pivot Table").Select
    Cells(2, 2).Select
    With pTable1
        With .PivotFields("Employee_Number")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("CC1")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Regular_Hours"), "Sum of Regular_Hours", xlSum
    End With
End Sub

 

[chaddres]

Solved!

Sub PivotTable()
'
' Macro1 Macro
'

'
    Sheets("Payroll Data").Select
    Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = ActiveWorkbook.Worksheets.Add
    sht1.Name = "Pivot Table"
    Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
        sht1.Cells(1, 1), TableName:="pTable1" & Format(Time, "hhmmss"))
    Sheets("Pivot Table").Select
    Cells(2, 2).Select
    With pTable1
        With .PivotFields("Employee_Number")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("CC1")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Regular_Hours")
            .Orientation = xlDataField
            .Function = xlSum
        End With
    End With
End Sub