Som vi alla vet, när vi gör ändringar i en källdata i en pivottabell, återspeglas det inte direkt i pivottabellen. Vi måste uppdatera pivottabellerna för att se ändringarna. Och om du skickar en uppdaterad fil utan att uppdatera pivottabellerna kan du känna dig generad.
Så i den här artikeln kommer vi att lära oss hur man automatiskt uppdaterar ett pivottabell med VBA. Detta sätt är lättare än du föreställt dig.
Detta är den enkla syntaxen för att automatiskt uppdatera pivottabeller i arbetsboken.
'Kod i källdatablad Objekt Privat under Arbetsblad_Deaktivera () bladnamn_of_pivot_tabell.Pivottabeller ("pivot_tabellnamn"). PivotCache.Refresh End Sub
Vad är Pivot Caches?
Varje pivottabell lagrar data i pivotcache. Det är därför pivot kan visa tidigare data. När vi uppdaterar pivottabellerna uppdateras cacheminnet med ny källdata för att återspegla ändringarna i pivottabellen.
Så vi behöver bara ett makro för att uppdatera cachen för pivottabeller. Vi kommer att göra detta med hjälp av en kalkylhändelse så att vi inte behöver köra makro manuellt.
Var ska man koda till automatisk uppdatering av pivottabeller?
Om dina källdata och pivottabeller finns i olika blad, ska VBA -koden finnas i källdatabladet.
Här kommer vi att använda Worksheet_SelectionChange Event. Detta gör att koden körs när vi byter från källdatabladet till ett annat blad. Jag förklarar senare varför jag använde denna händelse.
Här har jag källdata i sheet2 och pivottabeller i sheet1.
Öppna VBE med CTRL+F11 -tangenten. I projektutforskaren kan du se tre objekt, Sheet1, Sheet2 och Workbook.
Eftersom Sheet2 innehåller källdata dubbelklickar du på sheet2 -objektet.
Nu kan du se två nedrullningar längst upp i kodområdet. Från den första rullgardinsmenyn, välj kalkylbladet. Och från den andra rullgardinsmenyn väljer du Inaktivera. Detta kommer att infoga ett tomt undernamn Worksheet_Deactivate. Vår kod kommer att skrivas i denna del. Alla rader som skrivs i denna del, körs så snart användaren byter från detta blad till något annat blad.
På blad1 har jag två vridbord. Jag vill bara uppdatera ett pivottabell. För det måste jag veta namnet på pivottabellen. Om du vill veta namnet på någon pivottabell markerar du vilken cell som helst i den pivottabellen och går till fliken pivottabellanalys. På vänster sida ser du namnet på pivottabellen. Du kan också ändra namnet på pivottabellen här.
Nu vet vi namnet på pivottabellen, vi kan skriva en enkel rad för att uppdatera pivottabellen.
Private Sub Worksheet_Deactivate () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Och det är gjort.
Nu när du byter från källdata körs denna vba -kod för att uppdatera pivottabellen1. Som du kan se i gif nedan.
Hur uppdaterar jag alla pivottabeller i arbetsboken?
I exemplet ovan ville vi bara uppdatera en specifik pivottabell. Men om du vill uppdatera alla pivottabellerna i en arbetsbok behöver du bara göra små ändringar i din kod.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh For each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
I den här koden använder vi en For -loop för att gå igenom varje pivotcacher i arbetsboken. ThisWorkbook -objektet innehåller alla pivotcacher. För att komma åt dem använder vi ThisWorkbook.PivotCaches.
Varför använda Worksheet_Deactivate Event?
Om du vill uppdatera pivottabellen så snart någon ändring görs i källdata bör du använda Worksheet_Change -händelsen. Men jag rekommenderar det inte. Det kommer att få din arbetsbok att köra koden varje gång du gör några ändringar i arket. Du kan behöva göra hundratals ändringar innan du vill se resultatet. Men excel kommer att uppdatera pivottabellen vid varje förändring. Detta kommer att leda till slöseri med behandlingstid och resurser. Så om du har pivottabeller och data i olika blad är det bättre att använda Worksheet Deactivate Event. Det låter dig avsluta ditt arbete. När du väl byter till pivottabellark för att se ändringarna, ändrar det ändringarna.
Om du har pivottabeller och källdata på samma blad och du vill att pivottabellerna ska uppdatera det själv kan du använda Worksheet_Change Event.
Private Sub Worksheet_Change (ByVal Target As Range) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Hur uppdaterar jag allt i arbetsböcker när en ändring görs i källdata?
Om du vill uppdatera allt i en arbetsbok (diagram, pivottabeller, formler etc.) kan du använda kommandot ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Observera att denna kod inte ändrar datakällan. Så om du lägger till data under källdatan kommer den här koden inte att inkludera dessa data automatiskt. Du kan använda Excel -tabeller för att lagra källdata. Om du inte vill använda tabeller kan vi också använda VBA för att inkludera ny data. Vi lär oss det i nästa handledning.
Så ja kompis, så här kan du automatiskt uppdatera pivottabellerna i Excel. Jag hoppas att jag var tillräckligt förklarande och att den här artikeln tjänade dig bra. Om du har några frågor angående detta ämne kan du fråga mig i kommentarfältet nedan.
Hur du dynamiskt uppdaterar pivottabellens datakällor i Excel: För att dynamiskt ändra källdataintervallet för pivottabeller använder vi pivotcacher. Dessa få rader kan dynamiskt uppdatera alla pivottabeller genom att ändra källdataintervallet. I VBA använder du pivottabeller som visas nedan …
Kör makro om någon ändring görs på blad i specificerat intervall: I dina VBA -metoder skulle du behöva köra makron när ett visst område eller en cell ändras. I så fall använder vi ändringshändelsen för att köra makron när en ändring görs i ett målintervall.
Kör makro när någon ändring görs på arket | Så för att köra ditt makro när arket uppdateras använder vi Arbetsbladshändelser i VBA.
Enklaste VBA -kod för att markera aktuell rad och kolumn med | Använd det här lilla VBA -utdraget för att markera den aktuella raden och kolumnen i arket.
Arbetsbladshändelser i Excel VBA | Arbetsbladshändelsen är verkligen användbar när du vill att dina makron ska köras när en viss händelse inträffar på arket.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
VLOOKUP -funktionen i Excel | Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.