Dynamisch bijwerken van alle gegevensbron van draaitabellen in Excel

In een vorig artikel hebben we geleerd hoe u individuele draaitabellen dynamisch kunt wijzigen en bijwerken door gegevensbronnen kleiner of groter te maken.

In dit artikel zullen we lees hoe we ervoor kunnen zorgen dat alle draaitabellen in een werkmap automatisch de gegevensbron wijzigen. Met andere woorden, in plaats van één draaitabel tegelijk te wijzigen, zullen we proberen de gegevensbron van alle draaitabellen in de werkmap te wijzigen om dynamisch nieuwe rijen en kolommen toe te voegen aan brontabellen en de verandering in draaitabellen onmiddellijk weer te geven. / p>

Schrijf code in brongegevensblad

Aangezien we willen dat dit volledig automatisch is, zullen we werkbladmodules gebruiken om code te schrijven in plaats van een kernmodule. Hierdoor kunnen we werkbladgebeurtenissen gebruiken.

Als de brongegevens en draaitabellen zich in verschillende bladen bevinden, zullen we de VBA-code schrijven om de gegevensbron van de draaitabel te wijzigen in het werkbladobject dat de brongegevens bevat ( niet dat draaitabellen bevat).

Druk op CTRL + F11 om de VB-editor te openen. Ga nu naar projectverkenner en zoek het blad met de brongegevens. Dubbelklik erop.

Er wordt een nieuw coderingsgebied geopend. Mogelijk ziet u geen enkele wijziging, maar nu heeft u toegang tot de werkbladgebeurtenissen.

Klik op het linker drop-down menu en selecteer het werkblad. Selecteer deactiveren in het vervolgkeuzemenu aan de linkerkant. U ziet een blanco sub geschreven op de naam van het codegebied worksheet_deativate. Onze code voor het dynamisch wijzigen van brongegevens en het vernieuwen van de draaitabel gaat in dit codeblok. Deze code wordt uitgevoerd wanneer u van het gegevensblad naar een ander blad overschakelt. U kunt hier over alle werkbladgebeurtenissen lezen.

Nu zijn we klaar om de code te implementeren.

Broncode om alle draaitabellen in werkmap dynamisch bij te werken met een nieuw bereik

Om uit te leggen hoe het werkt, heb ik een werkmap. Dit werkboek bevat drie bladen. Sheet1 bevat de brongegevens die kunnen wijzigen. Sheet2 en Sheet3 bevatten draaitabellen die afhankelijk zijn van de brongegevens van sheet2.

Nu heb ik deze code geschreven in het codeergebied van sheet1. Ik gebruik de gebeurtenis Worksheet_Deactivate, zodat deze code wordt uitgevoerd om de draaitabel wanneer we overschakelen van het brondatablad.

Als u een vergelijkbare werkmap heeft, kunt u deze gegevens rechtstreeks kopiëren. Ik heb hieronder uitgelegd dat deze code werkt, zodat u deze kunt aanpassen aan uw behoeften. / p>

Je kunt het effect van deze code hieronder in gif zien.

Hoe verandert deze code automatisch brongegevens en draaitabellen bijwerken?

Allereerst hebben we een worksheet_deactivate-gebeurtenis gebruikt. Deze gebeurtenis wordt alleen geactiveerd wanneer het blad met de code wordt omgeschakeld of gedeactiveerd. Dus dit is hoe de code automatisch wordt uitgevoerd.

Om de hele tabel dynamisch als gegevensbereik te krijgen, bepalen we de laatste rij en de laatste kolom.

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

lstcol = Cellen (1, Columns.Count) .End (xlToL eft) .Column

Met behulp van deze twee getallen definiëren we de source_data. We zijn er zeker van dat het brongegevensbereik altijd begint vanaf A1. U kunt uw eigen begincelverwijzing definiëren.

Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Nu hebben we de brongegevens die dynamisch. We hoeven het alleen in de draaitabel te gebruiken.

Omdat we niet weten hoeveel draaitabellen een werkmap tegelijkertijd zal bevatten, zullen we elk blad en draaitabellen van elk blad doorlopen. dat er geen draaitabel over is. Hiervoor gebruiken we geneste for-loops.

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Volgende pt

Volgende ws

De eerste lus loopt door elk blad. De tweede lus herhaalt zich over elke draaitabel in een blad.

De draaitabellen zijn toegewezen naar variabele pt. We gebruiken de ChangePivotCache-methode van pt-object. We maken dynamisch een pivot-cache met behulp van ThisWorkbook.PivotCaches.Create

Method. Deze methode heeft twee variabelen SourceType en SourceData. Als brontype declareren we xlDatabase en als SourceData geven we source_data-bereik door dat we eerder hebben berekend.

En dat i zitten. We hebben onze draaitabellen geautomatiseerd. Hiermee worden automatisch alle draaitabellen in de werkmap bijgewerkt.

Dus ja jongens, dit is hoe je dynamisch de gegevensbronbereiken van alle draaitabellen in een werkmap in Excel kunt wijzigen. Ik hoop dat ik voldoende verklarend was. Als je vragen hebt over dit artikel, laat het me weten in de comments hieronder.

Hoe u het gegevensbronbereik van de draaitabel dynamisch kunt bijwerken in Excel: Om het brongegevensbereik van draaitabellen dynamisch te wijzigen, gebruiken we draaitabellen. Deze paar regels kunnen elke draaitabel dynamisch bijwerken door het brongegevensbereik te wijzigen.

Draaitabellen automatisch vernieuwen met VBA: om uw draaitabellen automatisch te vernieuwen, kunt u VBA-gebeurtenissen gebruiken. Gebruik deze eenvoudige regel code om uw draaitabel automatisch bij te werken. U kunt een van de drie methoden voor het automatisch vernieuwen van draaitabellen gebruiken.

Macro uitvoeren als er een wijziging is aangebracht op blad in gespecificeerd bereik: in uw VBA-praktijken zou u de noodzaak krijgen om macros uit te voeren wanneer een bepaald bereik of cel verandert. In dat geval gebruiken we de wijzigingsgebeurtenis om macros uit te voeren wanneer er een wijziging wordt aangebracht in een doelbereik.

Macro uitvoeren wanneer er een wijziging op het blad wordt aangebracht | Dus om uw macro uit te voeren wanneer het blad wordt bijgewerkt, gebruiken we de werkbladgebeurtenissen van VBA.

Eenvoudigste VBA-code om de huidige rij en kolom te markeren met | Gebruik dit kleine VBA-fragment om de huidige rij en kolom van het blad te markeren.

De werkbladgebeurtenissen in Excel VBA | De werkbladgebeurtenis is erg handig als u wilt dat uw macros worden uitgevoerd wanneer een bepaalde gebeurtenis op het blad plaatsvindt.

Populaire artikelen:

50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller bij uw taak. Met deze 50 sneltoetsen kunt u nog sneller werken in Excel. De functie VERT.ZOEKEN in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en werkbladen.

AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. De functie Countif is essentieel om uw dashboard voor te bereiden.

De functie SUMIF gebruiken in Excel | Dit is een andere essentiële functie voor het dashboard. Hiermee kunt u waarden samenvatten onder specifieke voorwaarden.