VBA Code to use between for the Filter options in a Pivot Table

[jessesingh]

Dear Excel Gurus,


i am trying to arrange in my excel template a way where the VBA code can select a between range for a date range.

eg.


PromiseShip Date is the filer option and the range that i would like to select is 06/03/2017 - 12/03/2017.

The data set is coming from the SQL Cube, here is my code:

Dim wb As Workbook
Set wb = ThisWorkbook


Dim DATAD, Report, sales, MB, Cover As Worksheet


Set DATAD = wb.Worksheets("DATAD")
Set Report = wb.Worksheets("Report Setup")
Set MB = wb.Worksheets("MBData")
Set Cover = wb.Worksheets("Cover Sheet")


Dim Count, j As Integer


Application.ScreenUpdating = False


'Filtering Date in Sales table in DataD
Dim startDate As String
Dim endDate As String

DATAD.Select
startDate = Range("E1").Value
endDate = Range("D1").Value
pvtField.PivotFilters.Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate


Set sales = wb.Sheets.Add(After:=Sheets("DATAD"))
sales.Name = "Sales"