Updating a pivot table using vba Chatmobileno
So we could have refreshed all the Pivot Caches instead of the Pivot Tables. When you create a new Pivot Table you are asked if you want it based on a previous table.
If you say no, this Pivot Table gets its own cache and doubles the size of the source data.
If you change any of the text or numbers in your data set, you need to refresh the pivot table.
If you change the size of your data set by adding or deleting rows/columns, you need to update the source data for the pivot table. This way your data source will be updated automatically when you add or delete rows/columns.
You need to force a refresh every time there are changes. You can also by selecting any cell in the Pivot Table and using the keyboard shortcut ALT F5.
Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table. Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table.
I also share a non-macro solution to update the pivot tables when the file is opened. Skill Level: Intermediate If you learn best by doing it on your own, you can download the file I'm using in the video to follow along. Refresh Pivot Table (41.5 KB)Can your pivot tables be updated immediately and automatically when their source data changes? It requires the use of a really simple macro that I will show you how to create below.You can imagine therefore what the difference might be between refreshing every cache in the Work Book, compared to refreshing every Pivot Table in the Work Book. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).Sub Update Connection() Dim Server Name As String Dim Server Name Raw As String Dim Cube Name As String Dim Cube Name Raw As String Dim Connection String As String Server Name Raw = Active Workbook. Visible Slicer Items List(1) Server Name = Replace(Split(Server Name Raw, "[")(3), "]", "") Cube Name Raw = Active Workbook. Visible Slicer Items List(1) Cube Name = Replace(Split(Cube Name Raw, "[")(3), "]", "") If Cube Name = "All" Or Server Name = "All" Then Msg Box "Please Select One Cube and Server Name", vb OKOnly, "Slicer Info" Else Connection String = Get Connection String(Server Name, Cube Name) Update All Query Table Connections Connection String, Cube Name End If End Sub Function Get Connection String(Server Name As String, Cube Name As String) Dim result As String result = "OLEDB; Provider=MSOLAP.5; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2" '"OLEDB; Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; Use Procedure for Prepare=1; Auto Translate=True; Packet Size=4096; Use Encryption for Data=False; Tag with column collation when possible=False" Get Connection String = result End Function Function Get Connection String(Server Name As String, Cube Name As String) Dim result As String result = "OLEDB; Provider=MSOLAP.5; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=" & Cube Name & "; Data Source=" & Server Name & "; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2" Get Connection String = result End Function Sub Update All Query Table Connections(Connection String As String, Cube Name As String) Dim cn As Workbook Connection Dim oledb Cn As OLEDBConnection Dim Count As Integer, i As Integer Dim DBName As String DBName = "Initial Catalog=" Cube Name Count = 0 For Each cn In This Workbook. Name = "This Workbook Data Model" Then Exit For End If o Tmp = Split(cn. Connection, ";") For i = 0 To UBound(o Tmp) - 1 If In Str(1, o Tmp(i), DBName, vb Text Compare) = 1 Then Set oledb Cn = cn. Would you like to answer one of these unanswered questions instead?If you’re not too familiar with macros and VBA, I suggest checking out my free 3-part video series on getting started with Macros & VBA.
Also, if you are new to pivot tables, I have a series to walk you through what they are and how to use them.As you know, if you change the data in the original table, the relative pivot table does not refresh the data in it at the meantime.