Need help creating excel pivot table via vba

Hello,

I am trying to create a pivot table but am having problems with the create pivot table method.  I am creating it within Access via vba.  Below is the relevant code.  I can create the source data worksheet and used a debug.print rng.rows count to verify that the source data range contains data.

ws.cells.EntireColumn.AutoFit
    ws.Range("A2").Select
    objExcelApp.ActiveWindow.SplitRow = 1
    objExcelApp.ActiveWindow.SplitColumn = 0
    objExcelApp.ActiveWindow.FreezePanes = True
    
    'Set up pivot table
    Dim rng As Excel.Range
    Dim rngPiv As Excel.Range
    Dim sPT As Excel.PivotTable
    
    Set rng = ws.UsedRange
    Debug.Print rng.Rows.Count
    'Set up newest version pivot table
    wb.Sheets.Add
    Set wsPiv = wb.Sheets("Signal_Code")
    wsPiv.Activate
    
    Dim PTCache As Excel.PivotCache
    Dim PivotDataRange As Excel.Range

    Set PivotDataRange = rng
    Set PTCache = wb.PivotCaches.Create _
        (SourceType:=xlDatabase, _
        SourceData:=PivotDataRange, _
        Version:=xlPivotTableVersion12)
    Set sPT = PTCache.CreatePivotTable(TableDestination:=wsPiv.cells(4, 1), TableName:="SignalCode", DefaultVersion:=xlPivotTableVersion12)
    

     With sPT.PivotFields("Signal_Code")
        .Orientation = xlRowField
        .Position = 1
    End With
   sPT.AddDataField ActiveSheet.PivotTables("Total Value").PivotFields("Total Value"), "Sum of Total Value", xlSum
    'Change number VBA.Format
    With sPT.PivotFields("CountOfItemCode")
        .NumberFormat = "0"
    End With

Select allOpen in new window