Kort gezegd: leer hoe u een eenvoudige macro gebruikt om draaitabellen automatisch te vernieuwen wanneer er wijzigingen in de brongegevens worden aangebracht. Ik deel ook een niet-macro-oplossing om de draaitabellen bij te werken wanneer het bestand wordt geopend. Inclusief videozelfstudie en downloaden van Excel-bestanden.

Vaardigheidsniveau: gemiddeld

Download het Excel-bestand

Als u het beste leert door het zelf te doen, kunt u dat download het bestand dat ik in de video gebruik om verder te volgen. Hier is het Excel-bestand dat de VBA-code bevat.

Draaitabel automatisch vernieuwen.xlsm (41.5 KB)

Draaitabellen automatisch bijwerken

Kunnen uw draaitabellen onmiddellijk en automatisch worden bijgewerkt wanneer hun brongegevens veranderen?

Absoluut. Het vereist het gebruik van een heel eenvoudige macro die ik u hieronder zal laten zien hoe u deze kunt maken.

Als u niet al te bekend bent met macro’s en VBA, stel ik voor om mijn gratis driedelige videoserie te bekijken om aan de slag te gaan met macro’s & VBA.

Ook als je nieuw bent bij draaitabellen, Ik heb een serie om je te laten zien wat ze zijn en hoe je ze kunt gebruiken. Bekijk de eerste video in die serie op draaitabellen & Dashboards

Om onze draaitabellen automatisch bij te werken, gaan we een macro schrijven met een simpele instructie. Die instructie zegt eigenlijk: als ik een wijziging aanbreng in mijn werkblad, vernieuw dan alle draaitabellen en gegevensverbindingen. Hier zijn de stappen om de macro te maken.

Open de Visual Basic Editor.

U kunt dit doen door op de Visual Basic-knop op het tabblad Ontwikkelaar van het lint te klikken.

De sneltoets voor het openen van de Visual Basic-editor is Alt + F11.

Als u dit niet ziet het tabblad Ontwikkelaar, kunt u dit zichtbaar maken met behulp van de instructies hier. U hoeft dit maar één keer te doen en dan is het tabblad Ontwikkelaar altijd zichtbaar elke keer dat u Excel in de toekomst opent.

Open de werkbladmodule die uw brongegevens bevat.

Zoek in het venster Projectverkenner van de Visual Basic-editor de werkmap die u wilt wijzigen. Onder die werkmap staan de bladen in de werkmap vermeld. Selecteer het blad dat de brongegevens bevat. Dubbelklik er vervolgens op.

Als u het venster Projectverkenner niet ziet, kunt u het inschakelen vanuit de weergave menu (sneltoets: Ctrl + R).

Voeg een nieuwe gebeurtenis toe voor werkbladwijzigingen.

Dubbelklikken op het blad opent de codemodule voor dat object. Binnen de codemodule willen we een gebeurtenismacro maken. Kies hiervoor Werkblad in de vervolgkeuzelijst Object aan de linkerkant.

Dat zal een Worksheet_SelectionChange-gebeurtenis toevoegen aan de module, die we eigenlijk niet willen, dus we zullen het zo meteen verwijderen. Voordat we dat doen, gaan we naar het vervolgkeuzemenu Procedure aan de rechterkant en kiezen we Wijzigen.

Dit voegt een nieuwe evenement bovenaan genaamd Worksheet_Change. Nu zullen we de onnodige code eronder markeren en verwijderen.

De gebeurtenismacro Worksheet_Change wordt uitgevoerd telkens wanneer er een wijziging wordt aangebracht naar cellen in dat werkblad. We kunnen VBA-code toevoegen aan de Worksheet_Change-gebeurtenis om acties uit te voeren wanneer de gebruiker cellen bewerkt.

Opmerking: de SelectionChange-gebeurtenis die standaard wordt toegevoegd, wordt uitgevoerd telkens wanneer de gebruiker een cel in het blad selecteert. Omdat we alleen willen dat de code wordt uitgevoerd wanneer de gebruiker cellen bewerkt / wijzigt, gebruiken we de gebeurtenis Change. Bekijk mijn artikel over VBA-codemodules & Macro’s uitvoeren op basis van gebruikersgebeurtenissen voor meer informatie over de bladmodules en gebeurtenissen.

Voeg de VBA-code toe om te vernieuwen alle draaitabellen.

Typ vervolgens net onder de regel Worksheet_Change deze instructie:

ThisWorkbook.RefreshAll

De RefreshAll-methode vernieuwt alle draaitabellen, query’s en gegevensverbindingen in de werkmap. Deze actie is hetzelfde als wanneer u handmatig op de knop Vernieuwen op het tabblad Gegevens klikt.

Als u deze regel code toevoegt aan de gebeurtenis Worksheet_Change, wordt de werkmap vernieuwd wanneer er een wijziging wordt aangebracht in het werkblad dat de code is in.

Draaitabel & Brongegevens op hetzelfde blad

Aleksandrs stelde een geweldige vraag over de YouTube-videoreacties. Als uw draaitabel en brongegevens zich op hetzelfde blad bevinden, moet u code toevoegen om gebeurtenissen uit te schakelen.

Het vernieuwen plaatst de gebeurtenis in een recursieve lus en kan ertoe leiden dat Excel crasht. Hier is de code om dat te voorkomen.

Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True

Controleren of uw macro actief is

Een manier om te controleren of de macro werkt is om het te testen. Breng een wijziging aan in de brongegevens en kijk of dit wordt weerspiegeld in uw draaitabel.Als uw wijziging niet gemakkelijk te herkennen is omdat u te veel gegevens heeft, of om een andere reden, is er een andere manier om te zien of uw macro wordt geactiveerd.

In de VB-editor kunt u op de grijze kolom net links van uw Worksheet_Change-macro. Hierdoor verschijnt een rode cirkel. Het markeert ook die regel code in rood.

Dit wordt een stop of onderbrekingspunt genoemd.

De sneltoets om een breekpunt aan / uit te zetten is: F9

Wanneer er nu een actie plaatsvindt die de macro activeert, springt Excel naar de VB Editor en pauzeert de macro zodat u de code kunt controleren. In ons geval is die actie elke wijziging die wordt aangebracht in het werkblad.

U kunt vervolgens op F8 drukken om door elk regel, of druk op F5 om naar het einde (of het volgende breekpunt) te rennen.

Als u een wijziging aanbrengt in het werkblad en Excel u niet naar de VB Editor trekt, weet u dat er een probleem is met de macro is niet actief. Als dit het geval is, heeft u het bestand waarschijnlijk niet opgeslagen als een werkmap met ingeschakelde macro’s (.xlsm) en / of ingeschakelde macro’s. Mogelijk moet u & opslaan, het bestand sluiten en vervolgens opnieuw openen en macro’s inschakelen.

Om het onderbrekingspunt te verwijderen dat u op de macro hebt geplaatst, klik gewoon op de rode cirkel om deze te laten verdwijnen (sneltoets: F9).

De sneltoets om alle breekpunten te wissen is: Ctrl + Shift + F9

Draaitabellen vernieuwen zonder een Macro

Een nadeel van het gebruik van deze macro om uw draaitabellen te vernieuwen, is dat de geschiedenis van ongedaan maken elke keer dat de macro wordt uitgevoerd, verloren gaat. Met andere woorden, wanneer u op de knop Ongedaan maken klikt (of op Ctrl + Z drukt), onthoudt Excel het laatste dat u deed niet meer, dus het kan het niet ongedaan maken. Bijgevolg gebeurt er niets en wordt uw laatste wijziging niet ongedaan gemaakt.

Er is een alternatief waarmee u uw geschiedenis voor ongedaan maken kunt behouden. Met dit alternatief wordt uw draaitabel echter alleen vernieuwd wanneer de werkmap wordt geopend, niet elke keer dat er een wijziging wordt aangebracht. Hier is hoe u die optie kunt gebruiken.

Beginnend vanuit elke cel in uw draaitabel:

1. Ga naar het tabblad Analyseren in het lint.
2. Kies de knop Opties.
3. Ga naar het tabblad Gegevens in het nieuwe venster dat wordt geopend.
4. Vink het vakje aan met de tekst “Gegevens vernieuwen bij openen van bestand”.

Nadat u op OK heeft geklikt, krijgt u mogelijk het volgende waarschuwingsbericht als u meerdere draaitabellen heeft gemaakt uit hetzelfde brongegevensbereik. Klik gewoon op OK om er doorheen te gaan.

Nogmaals, ter vergelijking, als u deze optie gebruikt, behoudt u de geschiedenis Ongedaan maken, maar wordt de draaitabel alleen vernieuwd wanneer de werkmap wordt gesloten en opnieuw geopend. Als u de macro-optie gebruikt, verliest u de geschiedenis Ongedaan maken, maar wordt de draaitabel automatisch bijgewerkt tes wanneer er een wijziging wordt aangebracht in de werkmap.

Variaties voor het vernieuwen van draaitabellen

De macro die we hebben bekeken, vernieuwt niet alleen uw draaitabellen, maar vernieuwt ook alle zoekopdrachten . Als u alleen draaitabellen wilt vernieuwen, kunt u de opdracht “ThisWorkbook.RefreshAll” vervangen door deze code:

Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub

Elke draaitabel is verbonden met een onderliggende pivot-cache, die is verbonden met de brongegevens. De code doorloopt alle draaitabellen in de werkmap en vernieuwt ze allemaal. Draaitabellen uit hetzelfde bronbereik kunnen draaitabellen delen, dus deze methode is sneller dan alle draaitabellen doorlopen .

Evenzo, stel dat u slechts één bepaalde draaitabel wilt vernieuwen. In dat geval kunt u de code “ThisWorkbook.RefreshAll” vervangen door de onderstaande code.

En tot slot, als je gebruikt Power Query en je wilt de achtergrondvernieuwing uitschakelen zodat query’s worden vernieuwd VOORDAT draaitabellen, ik heb een artikel geschreven om uit te leggen hoe je dat moet doen door de achtergrondvernieuwing voor de queries uit te schakelen.

Gebruik de In plaats daarvan gebeurtenis deactiveren

Een andere optie is om de gebeurtenis Worksheet_Deactivate te gebruiken in plaats van Worksheet_Change. De gebeurtenis Worksheet_Deactivate wordt elke keer uitgevoerd wanneer de gebruiker het blad verlaat en een ander blad selecteert. Hierdoor kan de gebruiker alle wijzigingen aanbrengen in de brongegevens, waarna de draaitabel automatisch wordt vernieuwd wanneer ze naar een ander blad gaan, inclusief de bladen die de draaitabel bevatten.

Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub

Deze code nog steeds in de werkbladmodule met de brongegevens worden geplaatst. Dit is een goede optie als uw draaitabellen of gegevensverbindingen een enkele seconden of langer om bij te werken, en u wilt niet elke keer wachten wanneer er een wijziging in de brongegevens wordt aangebracht.

De enige keer dat u dit misschien niet wilt gebruiken, is als uw draaitabel en bron gegevens staan op hetzelfde blad.Dat zal meestal een zeldzaam geval zijn, en iets wat ik over het algemeen niet aanbeveel.

Dankzij de suggestie van Ted hierover.

Bespaar tijd & Verlegenheid

Ik hoop dat dit artikel u tijd bespaart en het gemakkelijker maakt voor gebruikers van uw bestanden. Het kan ook helpen voorkomen dat u in verlegenheid wordt gebracht als u vergeet draaitabellen te vernieuwen voordat u rapporten verstuurt. Geloof Ik heb deze fout vaker gemaakt dan ik zou willen toegeven … 🙂