VBA + Pivot Tables = Pain

snuffnchess

Board Regular
Joined
May 15, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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?
VBA Code:
pt8.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)

VBA Code:
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
 

Attachments

  • Before.png
    Before.png
    70.1 KB · Views: 23
  • After.png
    After.png
    80.2 KB · Views: 32

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Well looks like i solved the issue.

Posting here in case anybody else has the same issue

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top