Using VBA to create Pivot Table with Date Range filter and distinct count on ID

[jimdac]

Hi All
I have read a few forums but I am now stumped and hope some one can help me
I have managed to create the pivot table using VBA and I can filter the rows and columns in the table but I cant filter the date range required
Not sure how to add Sample data but I have the following fields with some data.

Edit- Can someone let me know also how to add spreadsheet within the guidelines as well

ID JOB VetDates LOC VET TYPE City OTHER Country Code
123456 DOM 1/7/16 BNE JPBNE DOG SIN - Singapore (SG) DEPOT SG
123456 DOM 1/7/16 BNE JPBNE CAT SIN - Singapore (SG) DEPOT SG
114477 INT 2/7/16 MEL JPMEL DOG LHR - London-Heathrow (GB) DEPOT GB
114477 INT 2/7/16 MEL JPMEL DOG LHR - London-Heathrow (GB) DEPOT GB


The Pivot Table should look like this
LOC BNE SYD MEL
Country
Code
SG 1
LHR 1

The issue is that I have the same ID number on same date but I need to find unique ID count for that date and put it into the Country Code and LOC

Here is my code

Sub createPivot()
Dim StartDate As Date, EndDate As Date, DataCount
StartDate = Worksheets("Vet Workings").Range("N23").Value
EndDate = Worksheets("Vet Workings").Range("O23").Value

Worksheets("Sheet1").Range("A1").Select
ActiveWorkbook.PivotCaches. _
Create( _
SourceType:=xlDatabase, _
SourceData:=Worksheets("Vet Workings").Range("A1:J25103")). _
CreatePivotTable _
TableDestination:=Worksheets("Sheet1").Range("A1"), _
TableName:="PivotTable1"
Set Pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Pt.ClearAllFilters

FilterVetDates

With Pt.PivotFields("Country Code")
.Orientation = xlRowField
.Position = 1
End With
With Pt.PivotFields("LOC")
.Orientation = xlColumnField
.Position = 1
End With
With Pt.PivotFields("ID")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
End With

ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow


FilterCountryCodes
End Sub

Sub FilterVetDates()
Dim StartDate As Date, EndDate As Date, DataCount, countrycode
StartDate = Worksheets("Vet Workings").Range("N23").Value
EndDate = Worksheets("Vet Workings").Range("O23").Value

With ActiveSheet.PivotTables("PivotTable1").PivotFields("VetDates")
For i = 1 To .PivotItems.Count
If .PivotItems(i) >= CLng(StartDate) And .PivotItems(i) <= CLng(EndDate) Then
.PivotItems(i).Visible = True
Else

.PivotItems(i).Visible = False
End If
Next i
End With

Sub FilterCountryCodes()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country Code")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = "AE" Or .PivotItems(i) = "CA" Or .PivotItems(i) = "CN" Or .PivotItems(i) = "DE" Or .PivotItems(i) = "FR" Or .PivotItems(i) = "GB" Or .PivotItems(i) = "HK" Or .PivotItems(i) = "IE" Or .PivotItems(i) = "IT" Or .PivotItems(i) = "JP" Or .PivotItems(i) = "MY" Or .PivotItems(i) = "NZ" Or .PivotItems(i) = "SG" Or .PivotItems(i) = "US" Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next i
End With
FilterLocations
End Sub
Sub FilterLocations()
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LOC")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = "BNE" Or .PivotItems(i) = "SYD" Or .PivotItems(i) = "MEL" Or .PivotItems(i) = "PER" Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next i
End With
End Sub

End Sub

What I have found out is that I feel that it is the Pivot Items "VetDates" where when going through the filter it only gives me the first date and looks for the next date that is not the same ths giving me only records for each date but I can count the LOC or the Country Code numbers

Pls let me know if you need more clarification

Cheers

Jim

 

[jimdac]

Hi Again
I feel that the first post might be difficult so I have now made 6 different pivot tables manually
I would like to change them using VBA is possible
below is the code I am using
Pivot Table name is PTTotalThisYr
The filed I want to change is a date filed called VetDates

Private Sub UpdateDates()
Dim ThisYrStartDate As Date, ThisYrEndDate As Date, pt As PivotTable
ThisYrStartDate = Worksheets("VetWorkings").Range("M1")
ThisYrEndDate = Worksheets("VetWorkings").Range("N1")

Set pt = Worksheets("VetWorkings").PivotTables("PTTotalThisYr")
With pt.PivotFields("VetDates")
.ClearAllFilters
.PivotFilters.Add2 _
Type:=xlDateBetween, _
Value1:=CLng(ThisYrStartDate), _
Value2:=CLng(ThisYrEndDate)
End With

End Sub

the issue is I get an error 1004 application- Defined or object- defined error

Can anyone help pls

Jim