Draaitabellen Maken met VBA

• On Error Resume Next in combinatie met de DisplayAlerts-schakelaar en .Delete is een veilige "verwijder als deze bestaat"-methode: het verwijderen van een werkblad dat niet bestaat zou normaal gesproken een fout veroorzaken en de macro stoppen, maar On Error Resume Next zorgt ervoor dat VBA stilletjes doorgaat bij die specifieke fout; DisplayAlerts = False onderdrukt Excel's eigen bevestigingspop-up "weet u zeker dat u dit werkblad wilt verwijderen?";
• On Error GoTo 0 direct daarna schakelt de normale foutmelding weer in — als On Error Resume Next actief blijft voor de rest van de Sub, worden ook latere, niet-gerelateerde fouten stilletjes genegeerd, wat een valkuil is die je wilt vermijden;
• ThisWorkbook.PivotCaches.Create maakt een momentopname van de gegevens van de Tabel — de PivotCache, niet de draaitabel zelf — dit is het object waar elke draaitabel daadwerkelijk op wordt gebaseerd achter de schermen;
• pc.CreatePivotTable zet die momentopname om in een zichtbare draaitabel, geplaatst vanaf cel A3 op het nieuwe Pivot-werkblad en krijgt de naam ptProfitByRegion zodat latere code (zoals RefreshTable) deze weer kan vinden op naam;
• PivotFields("Region").Orientation = xlRowField en de Month-regel direct daaronder zijn het directe code-equivalent van het slepen van Region naar het Rijenvak en Month naar het Kolomvak in de veldlijst;
• AddDataField vult het Waarden-gebied — het tweede argument ("Sum of Profit") is alleen het label dat Excel als kolomkop weergeeft, en xlSum geeft aan dat de waarden moeten worden opgeteld in plaats van gemiddeld of geteld.

Hier is de code om een zesde regio toe te voegen als een nieuwe maart-rij in tblReports, met gebruik van ListRows.Add in plaats van deze handmatig in te voeren:

Sub AddSixthRegion()
    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set newRow = tbl.ListRows.Add

    newRow.Range(1, 1).Value = "March"        ' Month
    newRow.Range(1, 2).Value = "Southwest"    ' Region
    newRow.Range(1, 3).Value = 41500          ' Sales
    newRow.Range(1, 4).Value = 28200          ' Expenses
    newRow.Range(1, 5).Value = 13300          ' Profit
    newRow.Range(1, 6).Value = 39000          ' Target
End Sub

Voer dit één keer uit en voer daarna de RefreshProfitPivot Sub uit van eerder — de draaitabel zou nu "Southwest" als een nieuwe rij moeten tonen naast North, South, East, West en Central, zonder dat je de macro voor het bouwen van de draaitabel hebt aangepast.

1. BuildProfitPivot uitvoeren zoals het is

• Kopieer de Sub exact zoals geschreven in het hoofdstuk naar je module en voer deze één keer uit.
• Controleer de Projectverkenner of je werkbladtabbladen — er zou een nieuw werkblad met de naam "Pivot" moeten verschijnen, met Region in de rijen en Month over de kolommen, waarbij Profit wordt opgeteld.

2. Een zesde regio toevoegen en alleen verversen

• Typ de nieuwe rij direct in het werkblad (niet via code) — ga naar de onderkant van tblReports en voeg een maart-rij toe voor een verzonnen regio, bijvoorbeeld "Southwest."
• Voer BuildProfitPivot niet opnieuw uit — dat zou het hele Pivot-werkblad verwijderen en opnieuw opbouwen, wat het doel van deze oefening tenietdoet.
• Voer in plaats daarvan alleen de éénregelige RefreshTable-instructie uit het hoofdstuk uit — je moet verwijzen naar de bestaande draaitabel op naam, op dezelfde manier als het voorbeeld voor verversen in het hoofdstuk.

3. Velden wisselen en de samengevatte waarde wijzigen

• Drie regels binnen het With pt-blok moeten worden aangepast: welk veld xlRowField is, welk veld xlColumnField is, en naar welk veld AddDataField verwijst.
• Geef deze aangepaste versie een andere Sub-naam en een andere TableName — dezelfde namen als het origineel hergebruiken zou een fout veroorzaken of de eerste draaitabel stilzwijgend overschrijven.
• Het label dat wordt meegegeven aan AddDataField (het tweede argument, zoals "Sum of Profit") is alleen weergavetekst — pas dit aan zodat het overeenkomt met wat je nu samenvat.

Punt 2 — na het handmatig toevoegen van de rij voor de zesde regio, voer alleen dit uit:

Sub RefreshProfitPivot()
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
End Sub

Punt 3 — een aparte, aangepaste versie:

Sub BuildSalesPivotByMonth()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("SalesPivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "SalesPivot"

    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptSalesByMonth")

    With pt
        .PivotFields("Month").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
    End With
End Sub

Voer BuildSalesPivotByMonth uit en je krijgt een nieuw werkblad "SalesPivot" met Month in de rijen, Region over de kolommen en Sales-totalen in het midden — het spiegelbeeld van de oorspronkelijke indeling.