Universal name

[theta]

Hi all...I have produced a VBA script that pivots XML data

On some occasions there will be an additional pivotfield represented in the pivot as "Sum of NetCCY". All files contain the basic field "Sum of NetEUR"

I would like it so that if "NetCCY" exists, the GETPIVOTDATA formula use this as the criteria, if not then NetEUR is used

I thought I would create a variable (e.g. nNAME) in VBA so that if NetCCY exists, this is the value - else it is NetEUR

PMNCodeSDR.Cells(r, 5).FormulaR1C1 = "=GETPIVOTDATA(""" nNAME """,HPMN!R1C1,""HPMN"",RC1)"

Thoughts?

 

[theta]

Update. I have used the following code to determine the nPIVOT value

CreateXMLPivots

If oPT.PivotFields("TotalNetChargeSDR") <> 0 Then
        nPIVOT = "TotalNetChargeSDR"
    Else
        nPIVOT = "TotalNetCharge"
    End If

This is all within a Private Function (that creates the pivot), I need the whole project to have access to the value of this variable?

Public Sub

    ' Create Pivots & copy data --------------------------------------------------------
    If CreateXMLpivots(owb, oWS) Then
       'Copy filtered data if HPMN pivot successfully created
       Call CopyFilteredData(owb, oWS)
    End If

So that I can use GETPIVOTDATA with nPIVOT