Pivot table refresh....or not? (1 Viewer)

Boromyr

New member
Local time
Today, 14:29
Joined
Jul 20, 2020
Messages
15
Hello!
I have a problem with an autorefresh of a pivot table. With this code, I refresh the origin of a pivot table to eliminate the "empty" field from filters. It works partially: the origin changes, filters are clean, pivot table is refreshed...but...when I go to work on filter, excel says that "The pivot table report was saved without the underlying data. use the update data command to update the report". If I click on "Update", the pivot doesn't update (it is already updated!!!) but filters starts to work.
Where's the error???
Thanks, sorry for bad english & I hope my translation is understandable :)


Code:
Set NuovaCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=percorso & "\Output\[Nuovo_QC_DT-V1.2 - Fatture Scadute.xlsx]Base Dati!R1C1:R" & lUltRiga & "C19")
  
        With PT
               .ChangePivotCache (NuovaCache)
            .PivotCache.Refresh
               .PivotFields("Dir. Territoriale").ClearAllFilters
               .PivotFields("P.to oper.").ClearAllFilters
            .PivotFields("Desc. P.to op.").ClearAllFilters
        End With
        Set NuovaCache = Nothing
        'Aggiorno la base dati per la Pivot e pulisco tutti i filtri
 

Attachments

  • Errore.jpg
    Errore.jpg
    20 KB · Views: 531

isladogs

MVP / VIP
Local time
Today, 13:29
Joined
Jan 14, 2017
Messages
18,186
I can't answer your question but it looks like it should be in the Excel part of the forum. Shall I move it for you?
 

Isaac

Lifelong Learner
Local time
Today, 06:29
Joined
Mar 14, 2017
Messages
8,738
What if you set the save data option?
PivotTable Options > Data tab > check "Save source data with file"
 

Boromyr

New member
Local time
Today, 14:29
Joined
Jul 20, 2020
Messages
15
What if you set the save data option?
PivotTable Options > Data tab > check "Save source data with file"

Making this in the excel files, doesn't solved the problem. But, recording a macro, I put the same thing in the VBA code after the refresh, and it works!!!

Thank you very much (y)
 

Isaac

Lifelong Learner
Local time
Today, 06:29
Joined
Mar 14, 2017
Messages
8,738
Making this in the excel files, doesn't solved the problem. But, recording a macro, I put the same thing in the VBA code after the refresh, and it works!!!

Thank you very much (y)
Glad you got it working!
 

Users who are viewing this thread

Top Bottom