Excel VBA Uppdatera pivottabell
När vi infogar en pivottabell i arket, när data ändras pivottabelldata inte förändras i sig själv, måste vi göra det manuellt men i VBA finns det ett uttalande för att uppdatera pivottabellen som är uttryck. Uppfriskbar , med detta kan vi uppdatera pivottabell genom att referera till kalkylbladet som består av det eller så kan vi hänvisa till hela pivottabellerna i kalkylbladet och uppdatera dem alla på en gång.
Pivottabellen är viktig för att analysera den enorma mängden data. Det hjälper direkt från att analysera, sammanfatta och göra användbar datatolkning. Ett av problemen med denna pivottabell är dock att den inte automatiskt uppdateras om det finns någon förändring i källdata. Användaren måste uppdatera pivottabellen genom att gå till den specifika pivottabellen varje gång det sker en förändring. Men säg adjö till den manuella processen för här har vi metoden att uppdatera pivottabellen så snart du gör någon förändring i pivottabellen.
Hur uppdaterar man automatiskt VBA-kod för pivottabelldata?
Den enda gången pivottabellen behöver uppdateras är varje gång det ändras källdata för pivottabellen som vi hänvisar till.
Se till exempel nedanstående data och pivottabell.
Nu kommer jag att ändra siffrorna i källdata, dvs från A1 till B17.
I cell B9 måste jag ändra värdet från 499 till 1499, det vill säga en ökning av data på 1000, men om du tittar på pivoten visar resultatet fortfarande som 4295 istället för 5295. Jag måste uppdatera min pivottabell manuellt för att uppdatera vridbord.
För att lösa problemet måste vi skriva en enkel excel-makrokod för att uppdatera pivottabellen när det finns någon förändring i källdata.
# 1 - Enkel makro för att uppdatera alla tabeller
Steg 1: Ändra händelse i databladet
Vi måste utlösa ändringshändelsen för databladet. Dubbelklicka på databladet i Visual Basic Editor.
När du dubbelklickar på arket väljer du "Kalkylark" och väljer händelsen som "Ändra".
Du kommer att se en automatisk delprocedur som öppnas som Worksheet_Change (ByVal Target As Range)
Steg 2: Använd kalkylbladets objekt
Se databladet med hjälp av arbetsarkobjektet.
Steg 3: Se pivottabellen efter namn
Se pivottabellens namn med namnet på pivottabellen.
Steg 4: Använd Uppdatera tabellmetoden
Välj metoden som "Uppdatera tabell."
Nu kommer den här koden att uppdatera pivottabellen "PivotTable1" när det finns någon förändring i källdatabladet. Du kan använda koden nedan. Du måste bara ändra pivottabellens namn.
Koda:
Privat underarbetsblad_ändra (ByVal-mål som intervall) Kalkylblad ("datablad"). Pivottabeller ("pivottabell1"). Uppdateringstabell
# 2 - Uppdatera alla pivottabeller i samma kalkylblad
Om du har många pivottabeller i samma kalkylblad kan du uppdatera alla pivottabellerna med ett enda klick. Använd koden nedan för att uppdatera alla pivottabeller i arket.
Koda:
Sub Refresh_Pivot_Tables_Example1() Worksheets("Data Sheet").Select With ActiveSheet .PivotTables("Table1").RefreshTable .PivotTables("Table2").RefreshTable .PivotTables("Table3").RefreshTable .PivotTables("Table4").RefreshTable .PivotTables("Table5").RefreshTable End With End Sub
You need to change the name of the worksheet and pivot table names as per your worksheet details.
#3 - Refresh All Tables in the Workbook
It is highly unlikely we have all the pivot tables on the same worksheet. Usually, for each report, we try to add separate pivot tables in separate sheets. In these cases, we cannot keep writing the code for each pivot table to be refreshed.
So, what we can do is with a single code using loops, we can loop through all the pivot tables on the workbook and refresh them with a single click of the button.
The below code will loop through each pivot table and refresh them.
Code 1:
Sub Refresh_Pivot_Tables_Example2() Dim PT As PivotTable For Each PT In ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub
Code 2:
Sub Refresh_Pivot_Tables_Example3() Dim PC As PivotCache For Each PC In ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Both the codes will do the refreshing of pivot tables.
If you want the pivot table to be refreshed as soon as there is any change in the datasheet of the pivoting sheet, you need to copy and paste the above codes to the Worksheet Change event in that workbook.
#4 - Avoid Loading Time by using Worksheet Deactivate Event
När vi använder händelsen "Kalkylbladbyte" uppdateras den även när det inte sker någon ändring i datakällan, men om någon ändring sker i kalkylbladet.
Även om du anger en enda punkt i kalkylbladet försöker den att uppdatera pivottabellen. Så för att undvika detta kan vi använda metoden "Kalkylblad avaktivera" istället för "Kalkylarkbyte" -metoden.
Inaktivera händelseuppdateringarna i pivottabellen när du flyttar från ett ark till ett annat ark.
