Excel VBA ile Pivot Table Otomasyonu

Why automate a PivotTable?

A PivotTable is the fastest way to summarise long transaction records by dimensions such as region, product or date. But building the same report by hand every month — picking the source, dragging fields, setting the format — costs time and carries the risk of producing a slightly different report each time.

Turn the process into code with VBA and the report is built with the same steps every period: find the source, create the cache, place the report on the designated sheet, assign fields to the right areas. When new records arrive, running a single macro is enough.

Preparing the file properly

The point where automation breaks most often is not the code but the source data. The workbook will have two sheets: Veri (data) and Rapor (report). On the source sheet the headers must be in the first row, with no blank rows or columns in between.

1. Click inside the data range and press Ctrl+T to convert it into an Excel Table.
2. On the Table Design tab, set the table name to Satislar.
3. Check that headers are unique and non-empty, that Tutar is a number type and that Tarih is a real date format.
4. Save the file as a macro-enabled .xlsm workbook.

The PivotTable object model in VBA

Understanding the chain makes the code easier to read. PivotCaches.Create produces a cache from the source data — a copy of the data Excel holds in memory. CreatePivotTable then places that cache into a target cell as a report. Several reports can share one cache, which saves memory but means refreshing one affects the other.

Where a field lands in the report is set by the Orientation property:

The end-to-end macro

The code below creates the Rapor sheet if it does not exist and, if it does, clears the old PivotTable and rebuilds the report. Field names must match the source headers exactly — including accented characters and spaces.

Option Explicit

Public Sub SatisPivotRaporuOlustur()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsRapor As Worksheet
    Dim lo As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim ptAd As String

    On Error GoTo Hata
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Veri")
    Set lo = wsData.ListObjects("Satislar")
    ptAd = "ptBolgeUrun"

    ' Find the report sheet, create it if missing
    On Error Resume Next
    Set wsRapor = wb.Worksheets("Rapor")
    On Error GoTo Hata

    If wsRapor Is Nothing Then
        Set wsRapor = wb.Worksheets.Add( _
            After:=wb.Worksheets(wb.Worksheets.Count))
        wsRapor.Name = "Rapor"
    End If

    ' Reset the sheet and write a title
    wsRapor.Cells.Clear
    wsRapor.Range("A1").Value = "Sales by Region and Product"

    ' Build the cache from the Excel Table name
    Set pc = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=lo.Name)

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

    With pt
        With .PivotFields("Bölge")
            .Orientation = xlRowField
            .Position = 1
        End With

        With .PivotFields("Ürün")
            .Orientation = xlColumnField
            .Position = 1
        End With

        With .PivotFields("Tarih")
            .Orientation = xlPageField
            .Position = 1
        End With

        ' The caption must NOT equal the field name
        .AddDataField .PivotFields("Tutar"), "Toplam Tutar", xlSum
        .DataFields(1).NumberFormat = "#,##0.00"

        .RowAxisLayout xlTabularRow
        .TableStyle2 = "PivotStyleMedium9"
        .RefreshTable
    End With

    wsRapor.Columns.AutoFit
    wsRapor.Activate
    MsgBox "PivotTable report created.", vbInformation

TemizCikis:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

Hata:
    MsgBox "Could not create the report: " & Err.Description, vbExclamation
    Resume TemizCikis
End Sub

There are three deliberate design choices in that code:

• A single exit point. If you switch ScreenUpdating and DisplayAlerts off, they must come back on even when something fails. The TemizCikis label and Resume guarantee that. Otherwise, when the macro crashes, Excel looks frozen.
• A different field caption. If the caption you pass to AddDataField equals the field name, Excel raises an error. That is why the "Tutar" field gets the caption "Toplam Tutar".
• The sheet is wiped. wsRapor.Cells.Clear deletes everything on the report sheet. If there is other content you need to keep there, remove that line and clear only the old PivotTable's range (TableRange2.Clear).

Open the VBA editor with Alt+F11, paste the macro into a standard module, then run it from Developer → Macros.

Changing the field layout

The layout determines which question the report answers. For a trend over time, Tarih belongs in the rows; for a regional comparison, Bölge belongs in the columns. You can also add more than one measure.

' Total quantity and average amount
pt.AddDataField pt.PivotFields("Adet"), "Toplam Adet", xlSum
pt.AddDataField pt.PivotFields("Tutar"), "Ortalama Tutar", xlAverage

' Transaction count: count a text field
pt.AddDataField pt.PivotFields("Ürün"), "İşlem Sayısı", xlCount

' Move a field to the filter area
pt.PivotFields("Bölge").Orientation = xlPageField

Be careful when choosing the summary function. If Excel sees a single text value or blank cell in a column, it may default to Count instead of Sum. When a figure in the report is far smaller than expected, that is the first place to look: is the value field really being summed, or counted?

Refreshing the report

After new records are added you do not need to rebuild the report; refreshing is enough.

Public Sub PivotRaporunuYenile()
    Dim ws As Worksheet
    Dim pt As PivotTable

    On Error GoTo Hata
    Set ws = ThisWorkbook.Worksheets("Rapor")
    Set pt = ws.PivotTables("ptBolgeUrun")

    pt.PivotCache.Refresh
    MsgBox "Report updated. Row count: " & _
           ThisWorkbook.Worksheets("Veri") _
             .ListObjects("Satislar").DataBodyRange.Rows.Count, _
           vbInformation
    Exit Sub

Hata:
    MsgBox "Refresh failed: " & Err.Description, vbExclamation
End Sub

Showing the source row count in the message box is a small but valuable habit: when the report updates, you immediately see how many records it ran on. If that number is far from what you expected, something has gone wrong.

To refresh every cache in the workbook you can use ThisWorkbook.RefreshAll, but that command also triggers external data connections. If one query is slow, Excel will look locked up while the user waits — use it knowing its scope.

Common errors

Checklist before running

• The data sheet and table name match the code.
• Table headers are non-empty and unique.
• Measure fields are numeric and the date field is a real date.
• There is no other content on the report sheet that must survive.
• Field names are spelled exactly right in the code.
• The macro was first tried on a copy of the file.
• The resulting totals were checked by hand or against the UI.
• Refreshing was tested after adding new data.

Conclusion

VBA makes PivotTable reports reproducible — but the robustness of the automation depends far more on data preparation than on code. A clean Excel Table, unique and unambiguous field names, correct data types, and a controlled structure that restores settings on error: with those four in place, the code stays simple.

My advice: run it on a small sample file first and verify the totals by hand. When the report requirement changes, update the fields centrally in code rather than dragging them in the interface. That way, a month later, the answer to "how was last month's report produced?" sits inside the file.