Hi,
I tried to run this code, but it calculate "count" instead of "distinctcount" in case of datafield "TT". If I do pivot table manually, in the Create PivotTable dialog box I have to check the box next to the label “Add this data to the Data Model”. But I don’t know how it works in VBA code. Can anybody help me?
Tis is the code:
I tried to run this code, but it calculate "count" instead of "distinctcount" in case of datafield "TT". If I do pivot table manually, in the Create PivotTable dialog box I have to check the box next to the label “Add this data to the Data Model”. But I don’t know how it works in VBA code. Can anybody help me?
Tis is the code:
Code:
Option Explicit
Sub Pivottable()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim LastPivRow As Long
Dim LastPivCol As Long
Dim PCache As PivotCache
Dim PTable As Pivottable
Dim PRange As Range
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot").Delete
Sheets.Add After:=Worksheets("Data")
ActiveSheet.Name = "Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")
LastPivRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastPivCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastPivRow, LastPivCol)
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="PivotTable")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")
With ActiveSheet.PivotTables("PivotTable").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("DB")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("DS")
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("TT")
.Orientation = xlDataField
.Position = 1
.Function = xlDistinctCount
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Month")
.Orientation = xlPageField
.CurrentPage = 12
End With
End Sub