Macro not re-creating my pivot table

[blair878]

Good morning / afternoon everyone. I have a macro that is not completing the pivots which were recorded. This happens with not only deleting the pivot information but also creating a new table.

I'm not sure if anyone know a good solution for this as I have tried several times to record pivot table creation and it is not working.

I've inserted my issue below but as a warning, there are 3 pivot tables which need to be created and I'm not a VBA pro.


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"CORE Recon Refi!R1C1:R250C8", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Daily Summary!R5C4", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12


Thanks!!

TB

 

[Domenic]

Try replacing...

TableDestination:="Daily Summary!R5C4"

with

TableDestination:=wksDest.Range("D5")

Here's another way...

Option Explicit
Sub test()
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    Set wksSource = Worksheets("CORE Recon Refi")
    
    Set wksDest = Worksheets("Daily Summary")
    
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wksSource.Range("A1").CurrentRegion, _
        Version:=xlPivotTableVersion12)
        
    Set PT = wksDest.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=wksDest.Range("D5"), _
        TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion12)
        
    [COLOR=#008000]'optional (change the pivot field names, property settings, etc, accordingly)[/COLOR]
    With PT
        .PivotFields("PivotField1").Orientation = xlRowField
        .PivotFields("PivotField2").Orientation = xlColumnField
        .PivotFields("Pivotfield3").Orientation = xlDataField
        .DataBodyRange.NumberFormat = "#,##0"
        .TableStyle2 = "PivotStyleMedium2"
        .DisplayFieldCaptions = False
    End With
End Sub