How to make a universal pivot table with VBA?

Question

I have a calculation using a pivot table.
I would like to make a pivot table that is universal and will work for all the times, I have this same calculation to do.
I have tried but it doesn't work the calculation always blocks at the pivot table.

    Sheets.Add.Name = "tdc_flux"
    Sheets("flux phf a+1").Select

      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "flux phf a+1!R1C1:R1048576C16", Version:=6).CreatePivotTable _
        TableDestination:="tdc_flux!R3C1", TableName:="Tableau croisé dynamique3", _
        DefaultVersion:=6

    Sheets("tdc_flux").Select
    Cells(1, 1).Select

    With ActiveSheet.PivotTables("Tableau croisé dynamique3")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique3").RepeatAllLabels _
        xlRepeatLabels
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Concatner (ref +div)")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Type de flux")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique3").PivotFields("    En DICtrPr"), _
        "Somme de     En DICtrPr", xlSum

   ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique3").PivotFields("    En DICtrPr"), _
        "Somme de     En DICtrPr", xlSum

This is the part where it blocks.
"Somme" is sum in english, and "Tableau croisé dynamque" is the name of the pivot table.

Answer 1

I strongly suggest to use references, e. g. "pc" for the pivotcache and "pt" for the pivottable.

At the end of your code you must add the datafields like the rowfield and columnfield before.

Private Sub GeneralPivot()
    Dim pc As PivotCache
    Dim pt As PivotTable

    Sheets.Add.Name = "tdc_flux"

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "flux phf a+1!R1C1:R1048576C16", Version:=6)

    With pc
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault ' xlMissingItemsNone might be better!
    End With

    Set pt = pc.CreatePivotTable( _
        TableDestination:="tdc_flux!R3C1", _
        TableName:="Tableau croisé dynamique3", _
        DefaultVersion:=6)

    With pt
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
        .RepeatAllLabels xlRepeatLabels
    End With

    With pt.PivotFields("Concatner (ref +div)")
        .Orientation = xlRowField
        .Position = 1
    End With

    With pt.PivotFields("Type de flux")
        .Orientation = xlColumnField
        .Position = 1
    End With

    With pt.PivotFields("    En DICtrPr")
        .Orientation = xlDataField
        .Function = xlSum
        .Name = "Somme de En DICtrPr"
    End With

End Sub

To prevent outdated data, it might be useful to set MissingItemsLimit = xlMissingItemsNone.

It is typically not necessary to select or activate anything.