Pivot table Filter update through VBA code

**way2suresh** · 01-01-2013, 06:58 AM

Dear Friends,

I have a database for which i made a pivot table for report purpose.

in pivot table, i have some raw heads & values . And mainly in report filter, invoice received date.

In i need to prepare a report weekly. i have StartDate & EndDate in excel, need to update pivot table by filtering only those dates which comes under the start date and end date. or else RptDt1, RptDt2, RptDt3, RptDt5, RptDt5.

i haves some code, its not working properly, please help.

    Sheets("Pivot Report").Select
    Range("B1").Select
'    On Error Resume Next
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Date of Receiving from Vendor")
        .PivotItems("02/04/12").Visible = False
        
        .PivotItems(Range("RptDt1").Value).Visible = True
        .PivotItems(Range("RptDt2").Value).Visible = True
        .PivotItems(Range("RptDt3").Value).Visible = True
        .PivotItems(Range("RptDt4").Value).Visible = True
        .PivotItems(Range("RptDt5").Value).Visible = True
        .PivotItems(Range("RptDt6").Value).Visible = True
    End With
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

Thanks in Advance,
Suresh

**oeldere** · 01-01-2013, 07:34 AM

I don't have the solution for your VBA problem.

Though I have a suggestion.

If it is weekly based report, why don't you add an extra column to the data and count the weeknumber.

Then you can choose the weeknumber (instead of the dates).

**way2suresh** · 01-01-2013, 08:48 AM

Hi... Oeldere... thank you for your suggestion, the pivot table is not my report, i am getting my report through this pivot,

i mean, there are some more work to be done, like dashboard update, making charts related weekly figure, all i am doing through macro only, i stuck with this portion... if possible, i want this to be automated.