Trying to add Data Field to Pivot Table Data Model

[whytrigg]

Hey Everyone

For the last day I've been trying to get my VBA code to add a data field to my pivot table. I'm after a unique count of "Unit Numbers", but I keep getting "subscript out of range" run time error 9. Given it is distinct count, this needs to be added to the data model.

I've not included most of the code or data, just what I think is relevant - although, I may need to upload more.

I'm basically plotting the life % which can exceed 100% on the left and put a data field of the unique count of "Unit No" in. I'll be adding more after that but once I figure out this I hope the rest will be straight forward.

I suspect it maybe related to "xlPivotTableVersion15" but I'm not sure.

Any help is appreciated!

'------Decs--------
Public mFile As String
Public mSource As Workbook
Public mSheet as worksheet
Public mPivotCache As PivotCache
Public mPivot As PivotTable
Public mPrimeSht As Worksheet
Public mStartPvt As String
Public mStartPvt1 As Range
Public mSrcData As Range

'------Setup Pivot Table--------
mFile = Application.GetOpenFilename
Set mSource = Workbooks.Open(mFile)
Set mSheet = mSource.Worksheets("Data")


Set mSrcData = mSheet.Range("A1:BX" & mSheet.Range("A" & mSheet.Rows.Count).End(xlUp).Row)


Set mPrimeSht = mSource.Worksheets.Add
mPrimeSht.name = "Utilization Data"
Set mStartPvt1 = mPrimeSht.Range("A1")


mSource.Connections.Add2 "WorksheetConnection_Data!$A$1:$BX$" & mSheet.Range("A" & mSheet.Rows.Count).End(xlUp).Row, "", "WORKSHEET;[" & mSource.name & "]" & mSheet.name, mSheet.name & "!$A$1:$BX$" & mSheet.Range("A" & mSheet.Rows.Count).End(xlUp).Row, 7, True, False


Set mPivotCache = mSource.PivotCaches.Create(SourceType:=xlExternal, SourceData:=mSource.Connections("WorksheetConnection_Data!$A$1:$BX$" & mSheet.Range("A" & mSheet.Rows.Count).End(xlUp).Row), Version:=xlPivotTableVersion15)


Set mPivot = mPivotCache.CreatePivotTable(TableDestination:=mStartPvt1, TableName:="Utilization Cost", DefaultVersion:=xlPivotTableVersion15)


mPivot.CubeFields("[Range].[Life Percentage]").Orientation = xlRowField
mPivot.CubeFields("[Range].[Life Percentage]").Application.Cells.NumberFormat = "0%"


'---------STOPS WORKING HERE-------------


mPivot.AddDataField mPivot.CubeFields("[Measures].[Count of Unit No]"), "Count of Unit No"
ActiveSheet.PivotTables("Utilization Cost").AddDataField ActiveSheet.PivotTables("Utilization Cost").CubeFields("[Measures].[Count of Unit No]"), "Count of Unit No"

'---------Alternatives that didn't work----------
mPivot.AddDataField mPivot.CubeFields("[Measures].[Count of Unit No]"), "Count of Unit No"


mPivot.CubeFields.GetMeasure "[Range].[Unit No]", xlCount, "Count of Unit No"
'with mpivot.AddDataField mpivot.PivotFields("Unit No"), "total Unit No" end with
mPivot.AddDateField mPivot.CubeFields("[Measures].[Count of Unit No]")

 

[whytrigg]

Ok - I figured it out on my own.

For those who want to know how to do it, use the following

Set mCubeField = mPivot.CubeFields.GetMeasure( _    AttributeHierarchy:="[Range].[Unit No]", _
    Function:=xlDistinctCount, _
    Caption:="Units")