Add this data to the Data Model addition needed to current pivot table code

Hi all,

I have been racking my 1 core brain for hours today and am so stuck.

I need to modify the below code so that I can change a datafield from Count to distinct count but I dont know how to modify this code to change the pivot table type to include " te Add this data to the Data Model" option.

Can anyone help? please? Thank you Sarah

/Sub createMasterPivot()

'Clear Date within MasterPivot
Sheets("MasterPivot").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select



'declare variables to hold row and column numbers that define source data cell range

Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myLastColumn As Long

'declare variables to hold source and destination cell range address

Dim mySourceData As String
Dim myDestinationRange As String



'declare object variables to hold references to source and destination worksheets, and new Pivot Table

Dim mySourceWorksheet As Worksheet
Dim myDestinationWorksheet As Worksheet
Dim myPivotTable As PivotTable

'identify source and destination worksheets
With ThisWorkbook
Set mySourceWorksheet = .Worksheets("Data")
Set myDestinationWorksheet = .Worksheets("MasterPivot")
End With

'obtain address of destination cell range

myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)



'identify first row and first column of source data cell range

myFirstRow = 1
myFirstColumn = 1


With mySourceWorksheet.Cells



'find last row and last column of source data cell range
myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column



'obtain address of source data cell range
mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)



End With



'create Pivot Table cache and create Pivot Table report based on that cache

Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")



'add, organize and format Pivot Table fields

With myPivotTable
.PivotFields("CriticalPatch_IncidentID").Orientation = xlRowField
With .PivotFields("CriticalPatch_IncidentID")
'.Orientation = xlDataField
.Position = 1

End With

With .PivotFields("Description")
.Orientation = xlRowField
.Position = 2


End With
With .PivotFields("Patch_Status")
.Orientation = xlColumnField
.Position = 1



End With
With .PivotFields("HostID")
.Orientation = xlDataField
.Position = 1

End With
End With

End Sub
/

To clarify, I need a macro that can create a pivot against a dynamically changing list (in my DATA ws) and the pivot to appear or have the option for a distinct count. I cant get the instinct count part to work.

Thanks in advance all for your help.