Solved

VBA error when filtering pivottable-rows: 1004

Posted on 2013-06-03
3
824 Views
Last Modified: 2013-06-06
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
0
Comment
Question by:Steven Vermoere
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 400 total points
ID: 39216077
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
 

Author Comment

by:Steven Vermoere
ID: 39224897
Bummer...You are right...Big shame on me :-(

Thx for pointing it

Steven
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39224916
:)

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

Good luck with the rest of your project.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2016 - Black cell borders 11 28
Automating Excel Weekly Report 13 63
Access Excel export not behaving 2 26
NEED TO UPDATE DATA IN EXCEL 18 32
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now