Distinct Count in VBA

[mattc7786]

Hi all,

I am setting up a macro to run a few pivot tables that from a the same data source in a single worksheet. I know how to manually make the pivot table a data model which will enable me to do the function of distinct count in the pivot table. But I have not been able to make that work in vba.

Is there a way to code my macro to run in vba? I have tried the dcount function as a column but that doesn't seem to be working for me either. I can put an extra column if that is the best way to do it. I have already coded two columns to be manually added so if a column is the best way to do it then it would be in Column I.


Here is my code below. I have highlighted in red what I have tried and it isn't working. I get Run-time error '1004': Unable to set the Function property of PivotField Class.

Sub CreatePivotTableandPivotCacheSMT4()

    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Sheet1.Name & "!" & Sheet1.Range("A1").CurrentRegion.Address, _
        Version:=xlPivotTableVersion15)
        
    Worksheets.Add
    Range("A3").Select
    ActiveSheet.Name = "Breakdown by Provider"
    
    Set pt = pc.CreatePivotTable( _
        TableDestination:=ActiveCell, _
        TableName:="ProviderPivot")
    
    Set pf = pt.PivotFields("Provider")
    pf.Orientation = xlRowField
    
    Set pf = pt.PivotFields("Cust Id")
    pf.Orientation = xlDataField
    
    Set pf = pt.PivotFields("Cust Id")
    pf.Orientation = xlDataField
[COLOR=#ff0000]    pf.Function = xlDistinctCount[/COLOR]
    
    Range("A1:C1").Select
    Selection.Merge
    Range("A1").Value = "Breakdown by Provider"


End Sub

Thanks,

Matt