VBA + Pivot Tables = Pain

[snuffnchess]

The point of the VBA project I am working on is for the set of data to be updated each week with the previous weeks numbers, and to then update the pivot tables that are linked to said dataset. The only change to the Datasource is the last row where data will be present - because well, the new data just gets pasted to the bottom of the dataset. So macro is essentially pasting that data, and updating the pivot.

The problem I am having, is that for some reason when the pivot is updating, it is updating the fields of said pivot table, and then fields that WERE present before the update, now are not.

The issue is coming from this line - but I do not know / understand why. Help?

pt8.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)

Sub aragingstats()

Dim ob As Workbook
Dim colcnt As Worksheet
Dim revbrk As Worksheet
Dim agdata As Worksheet
Dim agdatalr As Long

Dim pt8 As PivotTable
Dim pt9 As PivotTable
Dim rngData As Range


Set ob = ThisWorkbook
Set colcnt = ob.Sheets("Collector Counts")
Set revbrk = ob.Sheets("Revenue Breakdown")
Set agdata = ob.Sheets("Aging Data")




        
        agdata.Activate
            agdatalr = Cells(Rows.Count, "A").End(xlUp).Row
            Set rngData = agdata.Range("A1:N" & agdatalr)
            
        revbrk.Activate
        
        Set pt8 = revbrk.PivotTables("PivotTable8")
        Set pt9 = revbrk.PivotTables("PivotTable9")
        
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.RemovePivotTable (ActiveSheet.PivotTables("PivotTable8"))
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.RemovePivotTable (ActiveSheet.PivotTables("PivotTable9"))
        
 [B]           pt8.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)[/B]
            
            ActiveSheet.PivotTables("PivotTable9").CacheIndex = ActiveSheet.PivotTables("PivotTable8").CacheIndex
            
            
            ActiveSheet.PivotTables("PivotTable8").PivotFields("Date").ShowDetail = False
            ActiveSheet.PivotTables("PivotTable9").PivotFields("Date").ShowDetail = False
    
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.AddPivotTable (ActiveSheet.PivotTables("PivotTable8"))
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.AddPivotTable (ActiveSheet.PivotTables("PivotTable9"))

End Sub

 

[snuffnchess]

Well looks like i solved the issue.

Posting here in case anybody else has the same issue

Sub aragingstats()

Dim ob As Workbook
Dim colcnt As Worksheet
Dim revbrk As Worksheet
Dim agdata As Worksheet
Dim franrg As Range
Dim agdatalr As Long


Dim pt8 As PivotTable
Dim pt9 As PivotTable

Dim SRCDATA As String

Set ob = ThisWorkbook
Set colcnt = ob.Sheets("Collector Counts")
Set revbrk = ob.Sheets("Revenue Breakdown")
Set agdata = ob.Sheets("Aging Data")




        
        agdata.Activate
            agdatalr = Cells(Rows.Count, "A").End(xlUp).Row
            
        revbrk.Activate
        
        Set pt8 = revbrk.PivotTables("PivotTable8")
        Set pt9 = revbrk.PivotTables("PivotTable9")
        
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.RemovePivotTable (pt8)
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.RemovePivotTable (pt9)
        
        With pt8
            SRCDATA = .PivotCache.SourceData
            SRCDATA = "Aging Data!R1C1:R" & agdatalr & "C22"
            .ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, SRCDATA)
        End With
            
            pt9.CacheIndex = pt8.CacheIndex


    
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.AddPivotTable pt8
        ob.SlicerCaches("Slicer_Collector_Name").PivotTables.AddPivotTable pt9
    
   
    
End Sub