VBA error when filtering pivottable-rows: 1004

Hello,

When I'm running the following code, I always get Error 1004, 'Application defined or object defined error' when I'm coming at the line where the filter on the "Composed"-field is done:

This is the code

    Dim oWSData                                   As Worksheet
    Dim oWSPivot                                  As Worksheet

    Dim dtDateEnd                                 As Date
    Dim dtDateStart                               As Date
    Dim oRNGTemp                                  As Range
    Dim lLastRow                                  As Long
    Dim ptTemp                                    As PivotTable
    Dim ptCache                                   As PivotCache
    Dim ptTable                                   As PivotTable
    Dim ptField                                   As PivotField
    Dim ptItem                                    As PivotItem

    Set oWSData = ThisWorkbook.Sheets("temp")
    Set oWSPivot = ThisWorkbook.Sheets("pivot")

    dtDateStart = ThisWorkbook.Names("PARAM_DATE_BEGIN").RefersToRange.Value
    dtDateEnd = ThisWorkbook.Names("PARAM_DATE_END").RefersToRange.Value + 1

    lLastRow = oWSData.Range("A3").End(xlDown).Row
    Set oRNGTemp = oWSData.Range("A2:J" & lLastRow)

    Set ptCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, sourceData:=oRNGTemp)
 
   Set ptTable = ptCache.CreatePivotTable(TableDestination:=oWSPivot.Range("A1"), TableName:="parkdata")

    With ptTable
        .AddFields RowFields:="Composed", ColumnFields:="farm"
        .AddDataField ptTable.PivotFields("cat"), "Sum cat", xlSum
    End With

    Set ptField = ptTable.PivotFields("Composed")
    
    ptField.ClearAllFilters
    
    For lCounterA = 1 To ptTable.PivotFields("Composed").PivotItems.Count
    Set ptItem = ptTable.PivotFields("Composed").PivotItems(lCounterA)
        If ptItem.Name >= dtDateStart And ptItem.Name >= dtDateEnd Then
            ptItem.Visible = True
        Else
            ptItem.Visible = False
        End If
    Next lCounterA

End sub

Open in new window


He is comparing tow by row and is reading it correctly. But when he wants to put it not visible, it is crashing on the line   'ptItem.Visible = True' or  'ptItem.Visible = False'


I also tried this, with the same error. And this is also crashing

ptField.PivotFilters.Add Type:=xlDateBetween, Value1:=dtDateStart, Value2:=dtDateEnd

Open in new window


Thank you for your help
Steven VermoereAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

Just an observation:

If you are looking for rows with dates between the dtDateStart & dtDateEnd values, then this line of code seems incorrect:

If ptItem.Name >= dtDateStart And ptItem.Name >= dtDateEnd Then

Open in new window


Should it be like this?...

If ptItem.Name >= dtDateStart And ptItem.Name <= dtDateEnd Then

Open in new window


BFN,

fp.
0
 
Steven VermoereAuthor Commented:
Bummer...You are right...Big shame on me :-(

Thx for pointing it

Steven
0
 
[ fanpages ]IT Services ConsultantCommented:
:)

Sometimes just a second pair of eyes is all it takes.

Good luck with the rest of your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.