Sådan opdateres dynamisk alle pivottabeller datakilde i Excel

I en tidligere artikel lærte vi, hvordan du dynamisk kan ændre og opdatere individuelle drejetabeller med skrumpende eller udvidende datakilder.

I denne artikel vil vi lær hvordan vi kan få alle pivottabeller i en projektmappe til at ændre datakilden automatisk. Med andre ord, i stedet for at ændre en pivottabel ad gangen, vil vi forsøge at ændre datakilden for alle pivottabellerne i projektmappen for dynamisk at inkludere nye rækker og kolonner, der er tilføjet til kildetabellerne og afspejler ændringen i pivottabeller med det samme. / p>

Skriv kode i kildedataarket

Da vi ønsker, at dette skal være helt automatisk, bruger vi arkmoduler til at skrive kode i stedet for et kernemodul. Dette giver os mulighed for at bruge regnearkhændelser.

Hvis kildedataene og pivottabellerne er i forskellige ark, skriver vi VBA-koden for at ændre pivottabeldatakilden i det arkobjekt, der indeholder kildedataene ( ikke der indeholder pivottabeller).

Tryk på CTRL + F11 for at åbne VB-editoren. Gå nu til projektudforsker og find arket, der indeholder kildedataene. Dobbeltklik på det.

Et nyt kodningsområde åbnes. Du kan muligvis ikke se nogen ændring, men nu har du adgang til regnearkbegivenhederne.

Klik på venstre rullemenu, og vælg regnearket. Vælg deaktiver i venstre rullemenu. Du vil se en tom sub skrevet på kodeområdet navn worksheet_deativate. Vores kode til dynamisk ændring af kildedata og opdatering af pivottabellen vil blive vist i denne kodeblok. Denne kode kører, når du skifter fra databladet til ethvert andet ark. Du kan læse om alle regnearkbegivenheder her.

Nu er vi klar til at implementere koden.

Kildekode til dynamisk opdatering af alle pivottabeller i projektmappe med nyt interval

For at forklare, hvordan det fungerer, har jeg en projektmappe. Denne projektmappe indeholder tre ark. Ark1 indeholder de kildedata, der kan ændres. Sheet2 og Sheet3 indeholder pivottabeller, der afhænger af kildedataene for sheet2.

Nu har jeg skrevet denne kode i arkets kodningsområde. Jeg bruger begivenheden Worksheet_Deactivate, så denne kode kører for at opdatere pivottabel, når vi skifter fra kildedataark.

Hvis du har en lignende projektmappe, kan du direkte kopiere disse data. Jeg har forklaret, at denne kode fungerer nedenfor, så du kan ændre den efter dine behov.

Du kan se effekten af denne kode i gif nedenfor.

Hvordan ændres denne kode automatisk kildedata og opdater pivottabeller?

Først og fremmest brugte vi et regneark_deaktiver begivenhed. Denne begivenhed udløses kun, når arket, der indeholder koden, skiftes eller deaktiveres. Så sådan kører koden automatisk.

For dynamisk at få hele tabellen som dataområde bestemmer vi den sidste række og sidste kolonne.

lstrow = Celler (Rækker.Tælling, 1) .End (xlUp) .Re

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

Ved hjælp af disse to tal definerer vi kildedataene. Vi er sikre på, at kildedataområdet altid starter fra A1. Du kan definere din egen startcellehenvisning.

Indstil kildedata = Område (Celler (1, 1), Celler (lstrow, lstcol))

Nu har vi de kildedata, som er dynamisk. Vi skal bare bruge det i pivottabellen.

Da vi ikke ved, hvor mange pivottabeller en projektmappe vil indeholde ad gangen, løber vi gennem hvert ark og pivottabeller på hvert ark. Så at der ikke er nogen pivottabel tilbage. Til dette bruger vi indlejrede til sløjfer.

For hver ws I ThisWorkbook.Worksheets

For hver pt I ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Næste pt

Næste ws

Den første sløjfe løber gennem hvert ark. Den anden sløjfe gentages over hver drejetabel i et ark.

Drejetabellerne er tildelt til variabel pt. Vi bruger ChangePivotCache-metoden for pt-objekt. Vi opretter dynamisk en pivot-cache ved hjælp af ThisWorkbook.PivotCaches.Create

Metode. Denne metode tager to variabler SourceType og SourceData. Som kildetype erklærer vi xlDatabase og som SourceData passerer vi kildedataområdet, som vi har beregnet tidligere.

Og at jeg s det. Vi har vores drejetabeller automatiseret. Dette opdaterer automatisk alle pivottabeller i projektmappen.

Så ja fyre, det er sådan, du dynamisk kan ændre datakildeområder for alle pivottabellerne i en projektmappe i Excel. Jeg håber, jeg var forklarende nok. Hvis du har spørgsmål vedrørende denne artikel, så lad mig det vide i kommentarfeltet nedenfor.

Sådan opdateres dynamisk pivottabeldatakildeområde i Excel: For at ændre kildedataområdet for pivottabeller dynamisk bruger vi pivotcacher. Disse få linjer kan dynamisk opdatere enhver pivottabel ved at ændre kildedataområdet.

Sådan opdateres automatisk pivottabeller ved hjælp af VBA: For automatisk at opdatere dine pivottabeller kan du bruge VBA-begivenheder. Brug denne enkle kodelinje til automatisk at opdatere din pivottabel. Du kan bruge en af tre metoder til automatisk opdatering af pivottabeller.

Kør makro, hvis der foretages ændringer på arket i et specificeret interval: I din VBA-praksis vil du få behov for at køre makroer, når et bestemt interval eller celleændringer. I så fald bruger vi ændringshændelsen til at køre makroer, når der foretages en ændring i et målinterval.

Kør makro, når der foretages en ændring på ark | Så for at køre din makro, når arket opdateres, bruger vi VBAs regnearkhændelser.

Enkleste VBA-kode til at fremhæve nuværende række og kolonne ved hjælp af | Brug dette lille VBA-uddrag til at fremhæve den aktuelle række og kolonne på arket.

Arbejdsarkhændelserne i Excel VBA | Arbejdsarkhændelsen er virkelig nyttig, når du vil have dine makroer til at køre, når en bestemt begivenhed opstår på arket.

Populære artikler:

50 Excel-genveje for at øge din produktivitet | Bliv hurtigere på din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel. VLOOKUP-funktionen i Excel | Dette er en af de mest anvendte og populære funktioner i Excel, der bruges til at slå værdi fra forskellige områder og ark.

COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke filtrere dine data for at tælle en bestemt værdi. Countif-funktionen er vigtig for at forberede dit dashboard.

Sådan bruges SUMIF-funktion i Excel | Dette er en anden vigtig funktion til dashboard. Dette hjælper dig med at opsummere værdier på specifikke betingelser.