Skapa pivottabeller med VBA

• On Error Resume Next i kombination med DisplayAlerts och .Delete är ett säkert "ta bort om det finns"-mönster: att ta bort ett blad som inte finns skulle normalt ge ett fel och stoppa makrot, men On Error Resume Next instruerar VBA att tyst fortsätta förbi just det felet; DisplayAlerts = False undertrycker Excels egen "är du säker på att du vill ta bort det här bladet?"-bekräftelse;
• On Error GoTo 0 direkt efteråt återställer normal felrapportering — om On Error Resume Next lämnas aktiv för resten av Sub skulle även senare, orelaterade fel tyst ignoreras, vilket är en fälla att undvika;
• ThisWorkbook.PivotCaches.Create tar en ögonblicksbild av tabellens data — PivotCache, inte själva PivotTable — vilket är objektet som varje PivotTable faktiskt byggs från i bakgrunden;
• pc.CreatePivotTable omvandlar ögonblicksbilden till en synlig pivottabell, placerad med start i cell A3 på det nya Pivot-bladet och får namnet ptProfitByRegion så att senare kod (t.ex. RefreshTable) kan hitta den igen via namnet;
• PivotFields("Region").Orientation = xlRowField och raden med Month direkt under är den direkta kodmotsvarigheten till att dra Region till radrutan och Month till kolumnrutan i fältlistan;
• AddDataField fyller i värdeområdet — det andra argumentet ("Sum of Profit") är bara etiketten som Excel visar som kolumnrubrik, och xlSum anger att värdena ska summeras istället för att medelvärdesberäknas eller räknas.

Här är koden för att lägga till en sjätte region som en ny Mars-rad i tblReports, med hjälp av ListRows.Add istället för att skriva in den manuellt:

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

Kör detta en gång och kör sedan RefreshProfitPivot-Sub:en från tidigare — pivottabellen ska nu visa "Southwest" som en ny rad tillsammans med North, South, East, West och Central, utan att du har ändrat något i makrot som bygger pivottabellen.

1. Köra BuildProfitPivot som den är

• Kopiera Sub-proceduren exakt som den står i kapitlet till din modul och kör den en gång.
• Kontrollera Project Explorer eller dina bladflikar — ett nytt blad med namnet "Pivot" ska nu finnas, med Region listat nedåt i raderna och Month utspritt över kolumnerna, summerande Profit.

2. Lägga till en sjätte region och endast uppdatera

• Skriv in den nya raden direkt i kalkylbladet (inte via kod) — gå längst ner i tblReports och lägg till en Mars-rad för en påhittad region, t.ex. "Southwest."
• Kör inte om BuildProfitPivot — det skulle ta bort och bygga om hela Pivot-bladet från början, vilket motverkar syftet med denna övning.
• Kör istället endast den enradiga RefreshTable-satsen från kapitlet — du behöver referera till den befintliga pivottabellen med namn, på samma sätt som kapitlets eget exempel för uppdatering gjorde.

3. Byta fält och ändra summerat värde

• Tre rader inuti With pt-blocket behöver ändras: vilket fält som är xlRowField, vilket som är xlColumnField och vilket fält AddDataField pekar på.
• Ge denna modifierade version ett annat Sub-namn och ett annat TableName — att återanvända samma namn som originalet skulle antingen ge fel eller tyst skriva över den första pivottabellen.
• Etiketten som skickas till AddDataField (det andra argumentet, som "Sum of Profit") är bara visningstext — uppdatera den så att den matchar det du faktiskt summerar nu.

Punkt 2 — efter att du manuellt lagt till raden för den sjätte regionen, kör bara detta:

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

Punkt 3 — en separat, modifierad version:

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

Kör BuildSalesPivotByMonth och du ska få ett nytt "SalesPivot"-blad med Month nedåt i raderna, Region över kolumnerna och Sales-totaler i tabellkroppen — spegelbilden av den ursprungliga layouten.