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 att lära oss hur vi kan göra att alla pivottabeller i en arbetsbok automatiskt ändrar datakällan. Med andra ord, istä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.
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 att vi kan använda kalkylbladshändelser.
Om källdata och pivottabeller finns i olika blad skriver vi VBA -koden för att ändra pivottabellens datakälla i bladobjektet 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 rullgardinsmenyn och välj kalkylbladet. Välj inaktivera från den vänstra rullgardinsmenyn. Du kommer att se en tom sub skriven på kodområdets namn worksheet_deativate. Vår kod för dynamiskt förändring av källdata och uppfriskande pivottabell kommer i detta kodblock. Denna kod körs när du byter från databladet till något annat blad. Du kan läsa om alla arbetsbladshändelser här.
Nu är vi redo att implementera koden.
Källkod för dynamisk uppdatering av 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 är beroende av källdata för sheet2.
Nu har jag skrivit den här koden i bladets kodningsområde. Jag använder händelsen Worksheet_Deactivate så att denna kod körs för att uppdatera pivottabellen när vi byter från källdatablad.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Bestämmer sista raden och kolumnnumret lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Ställa in det nya intervallet Ställ källdata = Område (Celler (1, 1), Celler (lstrow, lstcol))' Kod för att gå igenom varje ark och pivottabell 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 End Sub
Om du har en liknande arbetsbok kan du direkt kopiera dessa data. Jag har förklarat den här koden fungerar nedan så att du kan ändra den enligt dina behov.
Du kan se effekten av denna kod i gif nedan.
Hur ändrar denna kod automatiskt källdata och uppdaterar pivottabeller?
Först och främst använde vi en arbetsblad_deaktivera händelse. Denna händelse utlöses endast när arket som innehåller koden byts eller inaktiveras. Så det är så koden körs automatiskt.
För att dynamiskt få hela tabellen som dataintervall bestämmer vi den sista raden och sista kolumnen.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column
Med hjälp av dessa två nummer definierar vi source_data. Vi är bestämda att källdataintervallet alltid kommer att börja från A1. Du kan definiera din egen början cellreferens.
Ange source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Nu har vi källdata som är dynamiska. 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. Så att inget pivottabell är kvar. För detta använder vi kapslade för slingor.
För varje gång i ThisWorkbook.Worksheets
För varje pt In ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Nästa pt
Nästa ws
Den första slingan går genom varje ark. Den andra slingan upprepas över varje pivottabell i ett ark.
Pivottabellerna tilldelas 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 -intervall som vi har beräknat tidigare.
Och det är 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ällintervall 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 denna artikel, låt mig veta 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.
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 ditt 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å 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.