In een vorig artikel hebben we geleerd hoe u individuele draaitabellen dynamisch kunt wijzigen en bijwerken met krimpende of uitbreidende gegevensbronnen.
In dit artikel leren we 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 op te nemen die aan brontabellen zijn toegevoegd en de wijziging in draaitabellen onmiddellijk weer te geven.
Schrijf code in brongegevensblad
Omdat we willen dat dit volledig automatisch gaat, zullen we bladmodules 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 draaitabelgegevensbron te wijzigen in het bladobject dat de brongegevens bevat (niet dat draaitabellen bevat).
Druk op CTRL+F11 om de VB-editor te openen. Ga nu naar de projectverkenner en zoek het blad met de brongegevens. Dubbelklik erop.
Er wordt een nieuw coderingsgebied geopend. Mogelijk ziet u geen verandering, maar nu hebt u toegang tot de werkbladgebeurtenissen.
Klik op het linker vervolgkeuzemenu en selecteer het werkblad. Selecteer in het linker vervolgkeuzemenu deactiveren. U ziet een lege sub geschreven op de naam van het codegebied worksheet_deativate. Onze code voor het dynamisch wijzigen van brongegevens en het vernieuwen van de draaitabel komt in dit codeblok. Deze code wordt uitgevoerd wanneer u van het gegevensblad naar een ander blad overschakelt. U kunt hier alle werkbladgebeurtenissen lezen.
Nu zijn we klaar om de code te implementeren.
Broncode om alle draaitabellen in werkmap dynamisch bij te werken met nieuw bereik
Om uit te leggen hoe het werkt, heb ik een werkboek. Dit werkboek bevat drie bladen. Blad1 bevat de brongegevens die kunnen veranderen. Blad2 en Blad3 bevatten draaitabellen die afhankelijk zijn van de brongegevens van blad2.
Nu heb ik deze code in het coderingsgebied van blad1 geschreven. Ik gebruik de Worksheet_Deactivate-gebeurtenis, zodat deze code wordt uitgevoerd om de draaitabel bij te werken wanneer we overschakelen van het brongegevensblad.
Private Sub Worksheet_Deactivate() Dim source_data As Range 'Laatste rij- en kolomnummer bepalen lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Het nieuwe bereik instellen Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Code om door elk blad en draaitabel te lussen 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 End Sub
Als u een vergelijkbare werkmap heeft, kunt u deze gegevens rechtstreeks kopiëren. Ik heb uitgelegd dat deze code hieronder werkt, zodat u deze naar wens kunt aanpassen.
Je kunt het effect van deze code in gif hieronder zien.
Hoe verandert deze code automatisch brongegevens en werkt draaitabellen bij?
Allereerst hebben we een worksheet_deactivate-gebeurtenis gebruikt. Deze gebeurtenis wordt alleen geactiveerd wanneer het blad met de code wordt omgeschakeld of gedeactiveerd. Dus zo wordt de code automatisch uitgevoerd.
Om de hele tabel dynamisch als gegevensbereik te krijgen, bepalen we de laatste rij en laatste kolom.
lstrow = Cellen(Rijen.Aantal, 1).Einde(xlOmhoog).Rij
lstcol = Cellen(1, Kolommen.Aantal).End(xlNaarLinks).Kolom
Met behulp van deze twee getallen definiëren we de source_data. We zijn er zeker van dat het brongegevensbereik altijd vanaf A1 zal beginnen. U kunt uw eigen begincelverwijzing definiëren.
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Nu hebben we de brongegevens die dynamisch zijn. We hoeven het alleen in de draaitabel te gebruiken.
Omdat we niet weten hoeveel draaitabellen een werkmap tegelijk zal bevatten, doorlopen we elk blad en draaitabellen van elk blad. Zodat er geen draaitabel meer over is. Hiervoor gebruiken we geneste for-lussen.
Voor elke ws in dit werkboek.Werkbladen
Voor elke pt In ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
Brontype:=xlDatabase, _
SourceData:=source_data)
Volgende punt
Volgende ws
De eerste lus loopt door elk vel. De tweede lus herhaalt elke draaitabel in een blad.
De draaitabellen zijn toegewezen aan variabele punt We gebruiken de ChangePivotCache-methode van het pt-object. We maken dynamisch een pivot-cache met ThisWorkbook.PivotCaches.Create
Methode. Deze methode heeft twee variabelen SourceType en SourceData. Als brontype declareren we xlDatabase en als SourceData geven we het source_data-bereik door dat we eerder hebben berekend.
En dat is het. We hebben onze draaitabellen geautomatiseerd. Hiermee worden automatisch alle draaitabellen in de werkmap bijgewerkt.
Dus ja jongens, dit is hoe je het gegevensbronbereik van alle draaitabellen in een werkmap in Excel dynamisch kunt wijzigen. Ik hoop dat ik voldoende uitleg heb gegeven. Als je vragen hebt over dit artikel, laat het me dan weten in de comments hieronder.
Dynamisch bijwerken van het gegevensbronbereik van de draaitabel in Excel: Om het brongegevensbereik van draaitabellen dynamisch te wijzigen, gebruiken we draaicaches. 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 gebruiken om draaitabellen automatisch te vernieuwen.
Voer macro uit als er een wijziging is aangebracht op het blad in het opgegeven bereik: In uw VBA-praktijken zou u de noodzaak krijgen om macro's uit te voeren wanneer een bepaald bereik of cel verandert. In dat geval gebruiken we de change-gebeurtenis om macro's uit te voeren wanneer een wijziging in een doelbereik wordt aangebracht.
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 huidige rij en kolom te markeren met behulp van | 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 wanneer u wilt dat uw macro's worden uitgevoerd wanneer een opgegeven gebeurtenis op het blad plaatsvindt.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.De VERT.ZOEKEN-functie 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 bladen.
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. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.