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.
Thanks,
Matt
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.
Code:
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