VBA Pivot Table Distinct Count

Hi

I have created the following code to create a pivot table, but I can't get a distinct count of the Groupage Number Column.

Sub HUEMUELM()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Summary"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Summary")
Set DSheet = Worksheets("SCSGroupageT")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="HUEMUELM")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="HUEMUELM")

'Insert Row Fields
With ActiveSheet.PivotTables("HUEMUELM").PivotFields("grp route")
.Orientation = xlRowField
.Position = 1
.Name = "Route"
End With

'Insert Data Field
With ActiveSheet.PivotTables("HUEMUELM").PivotFields("Groupage Number")
.Orientation = xlDataField
.Position = 1
.Function = xlDistinctCount
.NumberFormat = "#,##0"
.Name = "Consols"
End With

'Insert Data Field
With ActiveSheet.PivotTables("HUEMUELM").PivotFields("Job Number")
.Orientation = xlDataField
.Position = 2
.Function = xlCount
.NumberFormat = "#,##0"
.Name = "Jobs"
End With
'Insert Data Field
With ActiveSheet.PivotTables("HUEMUELM").PivotFields("Pieces")
.Orientation = xlDataField
.Position = 3
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Pcs"
End With

End Sub

Any help would be much appreciated

Thanks

Matt

Thanks for the quick response Rory

How can I achieve this using VBA?

Matt

Recorded macro gave the following, but no idea how to apply this code to the original code

Sub huetest()
'
' huetest Macro
'

'
ActiveCell.Offset(-32, -6).Range("A1:I17").Select
Workbooks("HUEMUELMTEST.xlsm").Connections.Add2 _
"WorksheetConnection_SCSGroupageT!$A$1:$I$17", "", _
"WORKSHEET;C:\Users\mharri\Desktop\[HUEMUELMTEST.xlsm]SCSGroupageT", _
"SCSGroupageT!$A$1:$I$17", 7, True, False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_SCSGroupageT!$A$1:$I$17"), _
Version:=6).CreatePivotTable TableDestination:="Sheet10!R3C1", TableName _
:="PivotTable2", DefaultVersion:=6
Sheets("Sheet10").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").CubeFields("[Range].[grp route]")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").CubeFields.GetMeasure _
"[Range].[Groupage Number]", xlCount, "Count of Groupage Number"
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").CubeFields("[Measures].[Count of Groupage Number]"), _
"Count of Groupage Number"
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Measures].[Count of Groupage Number]")
.Caption = "Distinct Count of Groupage Number"
.Function = xlDistinctCount
End With
ActiveSheet.PivotTables("PivotTable2").CubeFields.GetMeasure _
"[Range].[Job Number]", xlCount, "Count of Job Number"
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").CubeFields("[Measures].[Count of Job Number]"), _
"Count of Job Number"
End Sub

HI Rory

That works perfectly for the range I used for the recorded macro, but when i try to run on the original data which has 2000 rows & 10 columns, it only creates a Pivot Table for Range("A1:I17"), though I can't see any reference to this range in your code?

That's brilliant

Thanks very much for your help Rory

Regards

Matt

This saved my life man! 13 years old Solution, still works like a charm! Thanks Man

Hello guys

can you please show us the code eventually

I did not understand the last step