Script causing data source to be corrupt(?) after running the script.

[Xish_Teo]

Hi, I've a code which runs to refresh all pivots I've listed in an excel file with a specific data source, also listed in the cells of the excel file.

The code is able to execute, and refreshes the data, however, the data source range becomes corrupt after? It throws me error: "The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report." and excel would need to restart after this error is being thrown.

I'm assuming that the data source is the one which is corrupted and not the pivot table itself because of below incidents:

1. Refreshing the same pivot table against other data sources would not throw the same error.
2. Using original data source listed in my excel file to create a new pivot would throw the error.

Is there something I need to change in my code? Below is the code:

Option Explicit


Public Data_Source_1400 As Range
Public Data_Source_1001 As Range
Public Data_Source As Range


Public Pivot_Workbook As String
Public Pivot_Sheet As String
Public Pivot_Table_Name As String




Public Ref_Sheet As Worksheet


Sub Refresh_Pivots()


Set Ref_Sheet = Workbooks("MACRO BUTTONS.xlsm").Worksheets("Refresh Pivots")
Set Data_Source_1400 = Ref_Sheet.Range("RDD1400_Range")
Set Data_Source_1001 = Ref_Sheet.Range("RDD1001_Range")




'Loops through first column in sheet.
With Ref_Sheet
    Dim i As Integer
        
    For i = 2 To 20
        If .Cells(i, 1).Value <> "" Then
            Pivot_Workbook = .Cells(i, 1).Value
            Pivot_Sheet = .Cells(i, 2).Value
            Pivot_Table_Name = .Cells(i, 3).Value
            
            'Chooses different range based on whether it's for 1400_N or 1001
            If .Cells(i, 4).Value = "1400_N" Then
                Set Data_Source = Data_Source_1400
            Else
                Set Data_Source = Data_Source_1001
            End If
                            
            Call Refresh_Pivot(Pivot_Workbook, Pivot_Sheet, Pivot_Table_Name, Data_Source)
        End If
    Next
End With
       
      
End Sub




Function Refresh_Pivot(Pivot_Workbook As String, Pivot_Sheet As String, Pivot_Table_Name As String, Data_Source As Range)


'Change data source of pivot.
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).ChangePivotCache _
Workbooks(Pivot_Workbook).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Data_Source)


'Ensure pivot table is refreshed.
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).RefreshTable




End Function

Thanks in advance!

 

[mole999]

on the occasions this occurs is your date truncated by a missing value say you expect so many days, and suddenly on is missing, which might change the layout of the data?

 

[Xish_Teo]

Hmmm my data is usually a year to date, so the information would usually be more and not less.

 

[Xish_Teo]

Found the solution to the problem from my colleague!

Add in "Version:=xlPivotVersion14" after the change pivot cache code, this would prevent the file from becoming unstable, this may vary depending on the version of MS excel you're using. I'm on excel 2010 for now.

Instead of ".RefreshTable", refresh the pivot cache instead. So the function Refresh_Pivot should look like below instead

'Change data source of pivot.
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).ChangePivotCache _
Workbooks(Pivot_Workbook).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Data_Source, Version:=xlPivotVersion14)

'Refreshes the cache of the pivot
Workbooks(Pivot_Workbook).Worksheets(Pivot_sheet).PivotTables(Pivot_Table_Name).PivotCache.Refresh
Workbooks(Pivot_Workbook).Worksheets(Pivot_sheet).Calculate

[\code]

 

[Xish_Teo]

Update: Another line of code needed to ensure it doesn't corrupt the data; as per below.

'Change data source of pivot.
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).ChangePivotCache _
Workbooks(Pivot_Workbook).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Data_Source, Version:=xlPivotVersion14)

'Refreshes the cache of the pivot
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).PivotCache.Refresh
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).SaveData = True
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).Calculate

 

[SOnicReducer]

Update: Another line of code needed to ensure it doesn't corrupt the data; as per below.

'Change data source of pivot.
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).ChangePivotCache _
Workbooks(Pivot_Workbook).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Data_Source, Version:=xlPivotVersion14)

'Refreshes the cache of the pivot
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).PivotCache.Refresh
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).PivotTables(Pivot_Table_Name).SaveData = True
Workbooks(Pivot_Workbook).Worksheets(Pivot_Sheet).Calculate

Digging up this post.
i'm new to VBA and this helped me because my pivots were getting corrupt and now seems ok.
unfortunately there is strange behavior , after i run my macro and every pivot table is update i do a slicer but i'm unable to choose any of the other pivottable to link to that slicer unless i manually do a change data source
Wondering if i'm missing anything

For Each pvtbl In DashboardSheet.PivotTables    'change the Ranges
    DashboardSheet.PivotTables(pvtbl.Name).ChangePivotCache ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=NewRangeName, Version:=xlPivotTableVersion15)
    'Updating the Pivot Table deciding if it stays here or pull out
    DashboardSheet.PivotTables(pvtbl.Name).RefreshTable


Next