Hur man dynamiskt uppdaterar alla pivottabeller datakälla i Excel

I en tidigare artikel lärde vi oss hur du dynamiskt kan ändra och uppdatera enskilda pivottabeller med krympande eller expanderande datakällor.

I den här artikeln kommer vi lära dig hur vi kan göra att alla pivottabeller i en arbetsbok automatiskt ändrar datakällan. Med andra ord, i stället för att ändra en pivottabell åt gången kommer vi att försöka ändra datakällan för alla pivottabellerna i arbetsboken för att dynamiskt inkludera nya rader och kolumner som läggs till i källtabellerna och återspegla förändringen i pivottabeller direkt. / p>

Skriv kod i källdatablad

Eftersom vi vill att detta ska vara helt automatiskt kommer vi att använda arkmoduler för att skriva kod istället för en kärnmodul. Detta gör det möjligt för oss att använda kalkylhändelser.

Om källdata och pivottabeller finns i olika ark kommer vi att skriva VBA-koden för att ändra pivottabellens datakälla i arkobjektet som innehåller källdata ( inte som innehåller pivottabeller).

Tryck på CTRL + F11 för att öppna VB-redigeraren. Gå nu till projektutforskaren och hitta arket som innehåller källdata. Dubbelklicka på den.

Ett nytt kodningsområde öppnas. Du kanske inte ser någon ändring men nu har du tillgång till kalkylbladshändelserna.

Klicka på den vänstra listrutan och välj kalkylbladet. Välj inaktivera från vänster rullgardinsmeny. Du kommer att se en tom sub skriven på kodområdets namn worksheet_deativate. Vår kod för dynamisk ändring av källdata och uppfriskande pivottabell kommer att finnas i detta kodblock. Den här koden körs när du byter från databladet till ett annat ark. Du kan läsa om alla kalkylhändelser här.

Nu är vi redo att implementera koden.

Källkod för att dynamiskt uppdatera alla pivottabeller i arbetsbok med nytt intervall

För att förklara hur det fungerar har jag en arbetsbok. Denna arbetsbok innehåller tre ark. Sheet1 innehåller källdata som kan ändras. Sheet2 och Sheet3 innehåller pivottabeller som beror på källdata för sheet2.

Nu har jag skrivit den här koden i kodområdet för sheet1. Jag använder händelsen Worksheet_Deactivate, så att den här koden körs för att uppdatera pivottabell när vi byter från källdatablad.

Om du har en liknande arbetsbok kan du direkt kopiera dessa data. Jag har förklarat att den här koden fungerar nedan så att du kan ändra den enligt dina behov.

Du kan se effekten av den här koden i gif nedan.

Hur ändras den här koden automatiskt källdata och uppdatera pivottabeller?

Först och främst använde vi ett kalkylblad_deaktivera händelse. Den här händelsen utlöses bara när kalkylarket som växlar eller inaktiveras. Så här körs koden automatiskt.

För att dynamiskt få hela tabellen som dataområde bestämmer vi den sista raden och den sista kolumnen.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Celler (1, Columns.Count) .End (xlToL eft) .Kolumn

Med hjälp av dessa två siffror definierar vi källdata. Vi är säkra på att källdataområdet alltid börjar från A1. Du kan definiera din egen början cellreferens.

Ställ in source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Nu har vi källdata som är dynamisk. Vi behöver bara använda den i pivottabellen.

Eftersom vi inte vet hur många pivottabeller en arbetsbok kommer att innehålla åt gången, kommer vi att gå igenom varje ark och pivottabeller för varje ark. att ingen pivottabell finns kvar. För detta använder vi kapslade för loopar.

För varje ws i ThisWorkbook.Worksheets

För varje pt I ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Nästa pt

Nästa ws

Den första slingan slingrar genom varje ark. Den andra slingan itererar över varje pivottabell i ett ark.

Pivottabellerna tilldelas till variabel pt. Vi använder ChangePivotCache-metoden för pt-objekt. Vi skapar dynamiskt en pivotcache med ThisWorkbook.PivotCaches.Create

Metod. Denna metod tar två variabler SourceType och SourceData. Som källtyp deklarerar vi xlDatabase och som SourceData passerar vi source_data-intervallet som vi har beräknat tidigare.

Och att jag s det. Vi har våra pivottabeller automatiserade. Detta uppdaterar automatiskt alla pivottabeller i arbetsboken.

Så ja killar, så här kan du dynamiskt ändra datakällans intervall för alla pivottabeller i en arbetsbok i Excel. Jag hoppas att jag var tillräckligt förklarande. Om du har några frågor angående den här artikeln, låt mig veta i kommentarfältet nedan.

Hur man dynamiskt uppdaterar pivottabellens datakällområde 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.

Så här uppdaterar du automatiskt pivottabeller med VBA: För att automatiskt uppdatera dina pivottabeller kan du använda VBA-händelser. Använd denna enkla kodrad för att uppdatera din pivottabell automatiskt. Du kan använda någon av tre metoder för automatisk uppdatering av pivottabeller.

Kör makro om någon ändring görs på ark inom angivet intervall: I din VBA-praxis skulle du behöva köra makron när ett visst intervall eller cellförändringar. 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å ark | Så för att köra ditt makro när arket uppdateras använder vi VBA: s arbetsbladshändelser.

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.

Kalkylbladshändelserna i Excel VBA | Kalkylbladshä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 genvägar i Excel för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare i Excel. VLOOKUP-funktionen i Excel | Detta är en av de mest använda och populära funktionerna i Excel som används för att slå upp värde från olika områden och ark.

COUNTIF i Excel 2016 | Räkna värden med förhållanden med den här fantastiska funktionen. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif-funktionen är viktig för att förbereda din instrumentpanel.

Så här använder du SUMIF-funktionen i Excel | Detta är en annan viktig instrumentfunktion. Detta hjälper dig att summera värden på specifika villkor.