Pivot Table Macro

[chaddres]

I am having some trouble with my pivot table VBA. On this line, I get an error

"ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
"pTable1").PivotFields("REGULAR EARNINGS"), "Sum of REGULAR EARNINGS", _
xlSum"

I figure I need a With statement, but am unsure of what that should be. Any ideas?

Chad

    Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = Sheets("FLS 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("FLS Pivot Table").Select
    Cells(2, 2).Select
    With pTable1
        With .PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With
        With .PivotFields("COMPANY CODE")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("NAME")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("QTR")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
            "pTable1").PivotFields("REGULAR EARNINGS"), "Sum of REGULAR EARNINGS", _
            xlSum
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
            "pTable1").PivotFields("OVERTIME EARNINGS"), "Sum of OVERTIME EARNINGS", _
            xlSum
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
            "pTable1").PivotFields("Regular"), "Sum of Regular", xlSum
        ActiveSheet.PivotTables("pTable1").PivotFields("Sum of REGULAR EARNINGS"). _
            Orientation = xlHidden
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
            "pTable1").PivotFields("Overtime"), "Sum of Overtime", xlSum
        ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
            "pTable1").PivotFields("Gross Earnings"), "Sum of Gross Earnings", xlSum
        ActiveSheet.PivotTables("pTable1").PivotFields("Sum of OVERTIME EARNINGS"). _
            Orientation = xlHidden
        ActiveSheet.PivotTables("pTable1").PivotFields("COMPANY CODE"). _
            ClearAllFilters
        ActiveSheet.PivotTables("pTable1").PivotFields("COMPANY CODE").CurrentPage _
            = "FLS"
    End With

 

[chaddres]

I solved most of it, but am now stuck on the very last line of code.

Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = Sheets("FLS 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("FLS Pivot Table").Select
    Cells(2, 2).Select
    With pTable1
        With .PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With
        With .PivotFields("COMPANY CODE")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("NAME")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("QTR")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Regular")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("Overtime")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("Gross Earnings")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        ActiveSheet.PivotTables("pTable1").PivotFields("COMPANY CODE").CurrentPage _
            = "FLS"

 

[chaddres]

Got it. Finally.

    Dim rng1 As Range
    Dim sht1 As Worksheet
    Dim pTable1 As PivotTable
    Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht1 = Sheets("FLS 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("FLS Pivot Table").Select
    Cells(2, 2).Select
    With pTable1
        With .PivotCache
            .RefreshOnFileOpen = False
            .MissingItemsLimit = xlMissingItemsDefault
        End With
        With .PivotFields("COMPANY CODE")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("NAME")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("QTR")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Regular")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("Overtime")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("Gross Earnings")
            .Orientation = xlDataField
            .Function = xlSum
        End With
        With .PivotFields("COMPANY CODE")
            .CurrentPage = "FLS"
        End With
    End With