CODEWITHSUNDEEP
exceldatefilterpivot-tablevba
bawpie
bawpie

Reputation: 477

Pivot Table Date Filter Issue (Not a valid date)

I have a requirement to filter an existing pivot table (PivotTable1 in 'Pivot' worksheet) in a workbook based on dates input by the user in another worksheet (Control). I should add that the cells in the control sheet have data validation on them to force dates only in the cells. The code I have so far is this:

Sub FilterPivotfromCell()

Dim Invoice_Start_Date As Date
Dim Invoice_End_Date As Date

Invoice_Start_Date = CDate(Worksheets("Control").Cells(3, "E").Value)

Invoice_End_Date = CDate(Worksheets("Control").Cells(3, "G").Value)

Sheets("Pivot").Select

MsgBox IsDate(Invoice_End_Date)
MsgBox IsDate(Invoice_Start_Date)

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlDateBetween, Value1:=Invoice_Start_Date, Value2:=Invoice_End_Date

End Sub

When stepping through the code using F8, and with watches on the variables Invoice_Start_Date and Invoice_End_Date, everything points to the variables being dates (type shows as date in the watch window, and the isdate message boxes come back as 'True' in both instances. However, when running the macro, as soon as it comes to the part where the pivot table is filtered, I get the message 'Run-time error '1004': The date you entered is not a valid date. Please try again.'

I wonder if the issue is something to do with having UK regional settings as I know I've had issues in the past with this.

Also, to clarify, cell contents of E3 is 01/10/2016 and cell contents of G3 is 31/10/2016.

Edit: I've tried following the suggestion here, but it had made no difference. The source of my pivot table does include blank rows also, but I've tried limiting it to just the data and it has made no difference.

Edit 2: Well, wouldn't you know it. A little more googling and came up with the solution here. Basically adding clng format around the dates in the pivot filter has done the trick?

Upvotes: 0

Views: 1974

Answers (2)

PhoenixmotoP
PhoenixmotoP

Reputation: 1

The work around for this problem is we should clear all filters applied to the field before filtering so that all filters are cleared for that field. like this...

set pf = pt.pivotfields("FilmReleaseDate")
pf.clearallfilters

Upvotes: 0

bawpie
bawpie

Reputation: 477

Here is the revised working code, following the answer provided by @MP24 in this question.

Sub FilterPivotfromCell()

Dim Invoice_Start_Date As Date
Dim Invoice_End_Date As Date


Invoice_Start_Date = CDate(Worksheets("Control").Cells(3, "E").Value)

Invoice_End_Date = CDate(Worksheets("Control").Cells(3, "G").Value)

Sheets("Pivot").Select

MsgBox IsDate(Invoice_End_Date)
MsgBox IsDate(Invoice_Start_Date)


ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlDateBetween, Value1:=CLng(Invoice_Start_Date), Value2:=CLng(Invoice_End_Date)

End Sub

Upvotes: 1

Related Questions