Geautomatiseerde Rapporten Opstellen

Het maandelijkse rapportpatroon Elk geautomatiseerd rapport in deze cursus volgt dezelfde structuur: oude status wissen, gegevens filteren of samenvatten, visuele output vernieuwen of opnieuw opbouwen, opmaak voor presentatie toepassen en voltooiing bevestigen aan de gebruiker.

Uitgewerkt Voorbeeld: Maandrapport met één klik

Option Explicit
 
Sub GenerateMonthlyReport()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim targetMonth As String
 
    Set ws = ThisWorkbook.Worksheets("Reports")
    Set tbl = ws.ListObjects("tblReports")
    targetMonth = "March"
 
    ' 1. Start from a clean slate
    If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
 
    ' 2. Filter to the month being reported on
    tbl.Range.AutoFilter Field:=1, Criteria1:=targetMonth
 
    ' 3. Refresh the summary PivotTable so it reflects current data
    On Error Resume Next
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
    On Error GoTo 0
 
    ' 4. Apply export-ready formatting
    With ws.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = tbl.Range.Address
    End With
 
    ' 5. Confirm completion
    MsgBox targetMonth & " report is ready — filtered, " & _
        "refreshed, and print-formatted."
End Sub

De vijf genummerde opmerkingen zijn niet alleen labels — ze vormen het rapportpatroon uit het begin van deze sectie, letterlijk gemaakt, één stap per fase. Enkele details die extra aandacht verdienen:

• targetMonth As String, ingesteld op een vaste waarde bovenaan, is de ene regel die je moet aanpassen naar "January" — omdat elke volgende stap deze enkele variabele gebruikt in plaats van het woord "March" elders in de Sub te herhalen, hoeft de doelmaand van het rapport nooit op meer dan één plek aangepast te worden;
• Stap 3 plaatst RefreshTable tussen On Error Resume Next / On Error GoTo 0 om dezelfde reden als de macro voor het bouwen van de draaitabel in sectie 4.3: als het Pivot-blad nog niet bestaat, zou deze regel anders een fout veroorzaken en de hele rapportmacro stoppen, in plaats van gewoon een stap over te slaan die nog niet klaar is;
• Stap 4's PrintArea = tbl.Range.Address koppelt het afdrukgebied direct aan het bereik van de tabel, zodat als er later rijen worden toegevoegd via ListRows.Add, het afdrukgebied nog steeds exact overeenkomt met de gegevens — geen apart onderhoud van het afdrukgebied nodig;
• Stap 5's MsgBox voegt targetMonth samen in de bevestigingstekst, zodat het bericht altijd de maand noemt die zojuist verwerkt is.

Dashboard Vernieuwen

Als een werkmap meerdere draaitabellen en grafieken bevat die een dashboardblad voeden, herberekent RefreshAll elke gegevensverbinding en PivotCache in één keer — de éénregelige versie van wat GenerateMonthlyReport handmatig doet voor één draaitabel: ThisWorkbook.RefreshAll

Deze ene regel doet hetzelfde als stap 3 in GenerateMonthlyReport, maar dan op het niveau van de hele werkmap in plaats van één specifieke draaitabel — handig zodra een dashboard meerdere draaitabellen, externe gegevensverbindingen of gekoppelde query's bevat die allemaal synchroon moeten blijven.

Exportklare Opmaak

Naast pagina-instelling moet een afgerond rapport vaak Excel volledig verlaten. ExportAsFixedFormat maakt direct vanuit de code een PDF aan:

ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\March_Report.pdf", _
    Quality:=xlQualityStandard

ThisWorkbook.Path geeft de map terug waarin de huidige werkmap is opgeslagen, zonder een afsluitende backslash — daarom wordt de bestandsnaam opgebouwd door "\March_Report.pdf" er expliciet aan toe te voegen. Als ThisWorkbook nog niet is opgeslagen, geeft .Path een lege tekenreeks terug en probeert deze regel op te slaan als alleen "\March_Report.pdf" op de hoofdmap van het huidige station, dus het is verstandig om te controleren of de werkmap minstens één keer is opgeslagen voordat je op dit patroon vertrouwt.

Opdracht

1. Typ GenerateMonthlyReport exact zoals getoond (je hebt het Pivot-blad uit eerdere hoofdstukken nodig) en voer het uit. Bevestig dat de tabel filtert op March en de draaitabel wordt vernieuwd.
2. Wijzig targetMonth naar "January" en voer opnieuw uit — bevestig dat het rapport wordt bijgewerkt naar de nieuwe maand.
3. Voeg één regel toe aan het einde van de Sub, vóór de MsgBox, die het Reports-blad exporteert naar PDF met ExportAsFixedFormat zoals hierboven getoond.