Attribute VB_Name = "PivotTableAutomation"
Option Explicit
Public Sub BuildPnLPivotTable()
    Dim sourceSheet As Worksheet
    Dim pivotSheet As Worksheet
    Dim sourceRange As Range
    Dim pivotStart As Range
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim fieldMap As Object
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colIndex As Long
    Dim headerName As String
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
    lastCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Or lastCol < 2 Then
        MsgBox "Sheet1 must contain headers in row 1 and at least one data row.", vbExclamation
        Exit Sub
    End If
    ' Build a case-insensitive map so the macro still works if header casing changes.
    Set fieldMap = CreateObject("Scripting.Dictionary")
    For colIndex = 1 To lastCol
        headerName = Trim$(CStr(sourceSheet.Cells(1, colIndex).Value))
        If Len(headerName) > 0 Then
            fieldMap(LCase$(headerName)) = headerName
        End If
    Next colIndex
    If Not fieldMap.Exists("desk") Or Not fieldMap.Exists("book") Or Not fieldMap.Exists("date") Or Not fieldMap.Exists("p&l") Then
        MsgBox "Expected headers: Desk, Book, Date, and P&L.", vbCritical
        Exit Sub
    End If
    Set sourceRange = sourceSheet.Range(sourceSheet.Cells(1, 1), sourceSheet.Cells(lastRow, lastCol))
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("PnL Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Set pivotSheet = ThisWorkbook.Worksheets.Add(After:=sourceSheet)
    pivotSheet.Name = "PnL Pivot"
    Set pivotStart = pivotSheet.Range("B4")
    ' Create the cache once, then reuse it when the pivot is built on the new sheet.
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceRange.Address(True, True, xlR1C1, True) _
    )
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotStart, _
        TableName:="PnLPivotTable" _
    )
    With pivotTable
        .PivotFields(fieldMap("desk")).Orientation = xlRowField
        .PivotFields(fieldMap("desk")).Position = 1
        .PivotFields(fieldMap("book")).Orientation = xlRowField
        .PivotFields(fieldMap("book")).Position = 2
        .PivotFields(fieldMap("date")).Orientation = xlColumnField
        .PivotFields(fieldMap("date")).Position = 1
        .AddDataField .PivotFields(fieldMap("p&l")), "Sum of P&L", xlSum
        .DataFields(1).NumberFormat = "#,##0;[Red](#,##0)"
        .RowAxisLayout xlTabularRow
        .ShowTableStyleRowStripes = True
        .TableStyle2 = "PivotStyleMedium9"
        .RepeatAllLabels xlRepeatLabels
    End With
    ' Date grouping can fail on dirty inputs, so keep it non-fatal for desk users.
    On Error Resume Next
    pivotTable.PivotFields(fieldMap("date")).NumberFormat = "dd-mmm-yyyy"
    pivotTable.PivotFields(fieldMap("date")).AutoGroup
    On Error GoTo 0
    With pivotSheet
        .Range("B1").Value = "Automated P&L Pivot"
        .Range("B1").Font.Bold = True
        .Range("B1").Font.Size = 16
        .Range("B2").Value = "Source: Sheet1"
        .Columns.AutoFit
        .Activate
    End With
    MsgBox "Pivot table created on the 'PnL Pivot' sheet.", vbInformation
End Sub