Pivot Table Data Model referencing wrong range

[Andy2021]

I have created some code that created a data model based pivot table that just displays a type, qty and concatenated string built from a column of references. This all works fine on the first run. Where it goes wrong is if I run it again on the same worksheet, even defining a different range, it is pulling in the total of the qty from the first table.
When I look at the Pivot Table field list it has actually ticked the Qty field from the first table, and not the same field from the second. As far as I can see I have made the code that it uniquely identifies the table it should be using.
Any help would be gratefully received.
Here are the parts I believe to be relevant.

    'Define Source Range and location of Pivot Table
    Set Source_Range = Application.InputBox("Select Source Table:", "WT_PivotTable", Selection.Address(0, 0), Type:=8)
    Set PT_Location = Application.InputBox("Select PivotTable Location:", "WT_PivotTable", Selection.Address(0, 0), Type:=8)
    PT_Name = "WT_PT_" & CountPivotsInWorkbook
    PT_Conn = "PT_Connection" & CountPivotsInWorkbook
    Range_Name = "Range_" & CountPivotsInWorkbook
    ActiveWorkbook.ActiveSheet.Range(Source_Range.Address).Name = Range_Name
    myDestinationRange = ActiveSheet.Name & "!" & PT_Location.Address(ReferenceStyle:=xlR1C1)
    

    'Connection
    ActiveWorkbook.Connections.Add2 _
    Name:=PT_Conn, _
    Description:="", _
    ConnectionString:="WORKSHEET;" & ActiveWorkbook.FullName & "]" & ActiveSheet.Name, _
    CommandText:=ActiveWorkbook.Name & "!" & Range_Name, _
    lCmdtype:=7, _
    CreateModelConnection:=True, _
    ImportRelationships:=False

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections(PT_Conn), Version _
    :=7).CreatePivotTable TableDestination:=myDestinationRange, _
    TableName:=PT_Name, DefaultVersion:=7


            With Source_Range
               Col1_Title = ActiveSheet.Range(.Cells(1).Address(0, 0)).Value        'Type
               Col2_Title = ActiveSheet.Range(.Cells(1, 2).Address(0, 0)).Value     ' Ref
               Col3_Title = ActiveSheet.Range(.Cells(1, 3).Address(0, 0)).Value     ' Qty
            End With

            With ActiveSheet.PivotTables(PT_Name).CubeFields("[" & Range_Name & "].[" & Col1_Title & "]")
                .Orientation = xlRowField
                .Position = 1
            End With
            ActiveSheet.PivotTables(PT_Name).CubeFields.GetMeasure "[" & Range_Name & "].[" & Col3_Title & "]", xlSum, "Sum of Qty" & CountPivotsInWorkbook
            ActiveSheet.PivotTables(PT_Name).AddDataField ActiveSheet.PivotTables(PT_Name).CubeFields("[Measures].[Sum of Qty]"), "Sum of Qty" & CountPivotsInWorkbook

 

[RoryA]

Shouldn't you be using:

CubeFields("[Measures].[Sum of Qty" & CountPivotsInWorkbook & "]")

in the last line?

 

[Andy2021]

Shouldn't you be using:

CubeFields("[Measures].[Sum of Qty" & CountPivotsInWorkbook & "]")

in the last line?

Thank you, but trying this throws up a subscript out of range error.

 

[RoryA]

Then it would appear that your previous line either didn't work, or gave the measure a different name. I suggest you check manually what the measure is called now.

 

[Andy2021]

A bit of additional info.
When I run my code and look at the field list for the second pivot table, you can see here that it has somehow referenced the field in the first range.
If I just click on the correct one in Range_1 it's all fine.

 

[RoryA]

That's because you're still using [Sum of Qty] as the measure name. You might be better off using a CubeField object like this:

            Set SumField = ActiveSheet.PivotTables(PT_Name).CubeFields.GetMeasure("[" & Range_Name & "].[" & Col3_Title & "]", xlSum, "Sum of Qty" & CountPivotsInWorkbook)
            ActiveSheet.PivotTables(PT_Name).AddDataField SumField, "Sum of Qty" & CountPivotsInWorkbook

 

[Andy2021]

That's because you're still using [Sum of Qty] as the measure name. You might be better off using a CubeField object like this:

            Set SumField = ActiveSheet.PivotTables(PT_Name).CubeFields.GetMeasure("[" & Range_Name & "].[" & Col3_Title & "]", xlSum, "Sum of Qty" & CountPivotsInWorkbook)
            ActiveSheet.PivotTables(PT_Name).AddDataField SumField, "Sum of Qty" & CountPivotsInWorkbook

Fantastic thank you so much.
You stare at code for so long you go blind to it.

I really appreciate your fast response and it worked beautifully!

This was my first time creating pivot tables with data models using VBA so I was happy I got this far.