Excel VBA Uppdatera pivottabell

När vi infogar en pivottabell i arket, när data ändras pivottabeldata inte ändras i sig själva måste vi göra det manuellt men i VBA finns det ett uttalande för att uppdatera pivottabellen som är uttryck. Uppdaterbar , med detta kan vi uppdatera pivottabell genom att hänvisa till kalkylbladet som består av det eller så kan vi hänvisa till hela pivottabellerna i kalkylbladet och uppdatera dem 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. Men ett av problemen med denna pivottabell är 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 finns en förändring. Men säga 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 du 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.

Titta till exempel på 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, dvs. en ökning av data på 1000, men om du tittar på pivoten visas fortfarande resultatet som 4295 istället för 5295. Jag måste uppdatera min pivottabell manuellt för att uppdatera pivottabellen.

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.

Du kan ladda ner den här VBA Refresh Pivot Table Excel-mallen här - VBA Refresh Pivot Table Excel-mall

# 1 - Enkel makro för att uppdatera alla tabeller

Steg 1: Ändra händelse i databladet

Vi måste utlösa ändringshändelsen i databladet. Dubbelklicka på databladet i Visual Basic Editor.

När du dubbelklickar på arket väljer du "Arbetsblad" 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 kalkylarkobjektet.

Steg 3: Se pivottabellen efter namn

Se pivottabellens namn med namnet på pivottabellen.

Steg 4: Använd metoden Uppdatera tabell

Välj metoden som "Uppdatera tabell".

Nu kommer den här koden att uppdatera pivottabellen ”Pivottabell1” när det finns någon ändring i källdatabladet. Du kan använda koden nedan, du behöver bara ändra pivottabellens namn.

Koda:

 Privat underarbetsark_Change (ByVal Target As Range) Kalkylblad ("Datablad"). Pivottabeller ("Pivottabell1"). Uppdateringstabell Slutunder

# 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 () Kalkylblad ("Datablad"). Välj med ActiveSheet. PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable. PivotTables ("Table5"). RefreshTable End With End Sub

Du måste ändra namnet på kalkylbladet och pivottabellnamnen enligt dina kalkylbladsdetaljer.

# 3 - Uppdatera alla tabeller i arbetsboken

Det är mycket osannolikt att vi har alla pivottabeller på samma kalkylblad. För varje rapport försöker vi vanligtvis lägga till separata pivottabeller i separata ark. I dessa fall kan vi inte fortsätta skriva koden för varje pivottabell som ska uppdateras.

Så vad vi kan göra är att med en enda kod med hjälp av loopar kan vi gå igenom alla pivottabellerna i arbetsboken och uppdatera dem med ett enda klick på knappen.

Koden nedan kommer att slinga igenom varje pivottabell och uppdatera dem.

Kod 1:

 Sub Refresh_Pivot_Tables_Example2 () Dim PT som pivottabell för varje PT i ActiveWorkbook.PivotTables PT.RefreshTable Nästa PT End Sub

Kod 2:

 Sub Refresh_Pivot_Tables_Example3 () Dim PC som PivotCache för varje dator i ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub

Båda koderna kommer att uppdatera pivottabellerna.

Om du vill att pivottabellen ska uppdateras så snart det finns någon förändring i databladet för pivotbladet måste du kopiera och klistra in ovanstående koder i arbetsbladets ändringshändelse i den arbetsboken.

# 4 - Undvik laddningstid genom att använda Arbetsblad Deactivate Event

När vi använder händelsen "Arbetsbladbyte" uppdateras den även om det inte sker någon ändring i datakällan utan om någon ändring sker i kalkylbladet.

Även om du anger en enda punkt i kalkylbladet försöker den uppdatera pivottabellen. Så för att undvika detta kan vi använda metoden "Kalkylblad avaktivera" istället för "Kalkylbladbyte" -metoden.