Pivot Table VBA Question

[gmazza76]

Good afternoon,

Is there a way to use two cell references (A1 From Date & A2 To Date) to update a filter in a pivot table without actually showing the filter itself?

I have used the following code to align a name of the person who loads the file so they can only see their data, but I am insure how I would get the
"ActiveSheet.PivotTables("AgentManager").PivotFields("Call Date")" to look between the first date ("A1") and the final date ("A2"), is this something that can be done.

Sub AgentManagerAlign()

Dim pvFld As PivotField
Dim strFilter As String

   Set pvFld = ActiveSheet.PivotTables("AgentManager").PivotFields("Team Manager")
   strFilter = ActiveWorkbook.Sheets("LiveStaffLoader").Range("B5").Value
   pvFld.CurrentPage = strFilter

End Sub

thanks in advance
Gavin

 

[SpillerBD]

Sorry @SpillerBD, no set timescale as I am trying to make data smother to acces at the moment

I was actually suggesting using the TimeLine available. I haven't looked at the code created if recording a macro on changes to the timeline or how useful it might be.
In your macro, are you stepping through the code with items in the watch window to see the values assigned to variables as the macro progresses?

 

[gmazza76]

Yes, it always fails where the value it looks up against the range ie week number

 

[gmazza76]

Good afternoon all,

I have managed to cover the original problem by the following

Create your pivot table as needed with the filters as selectable fields (in the first instance while setting up)

Dim pvFld As PivotField
Dim strFilter As String
    
    Sheets("NameOfSheet").Select ' where the pivot table first filter is
    Range("G9").Select
            Set pvFld = ActiveSheet.PivotTables("PivotTableName").PivotFields("Team Manager")
            strFilter = ActiveWorkbook.Sheets("Source").Range("B4").Value ' where your filter is automatically identified, mine is a load page that holds various info
            pvFld.CurrentPage = strFilter
                Set pvFld = ActiveSheet.PivotTables("PivotTableName").PivotFields("Year")
                strFilter = ActiveWorkbook.Sheets("Source").Range("B17").Value ' where your filter is automatically identified, mine is a load page that holds various info
                pvFld.CurrentPage = strFilter
                    Set pvFld = ActiveSheet.PivotTables("PivotTableName").PivotFields("Week No")
                    strFilter = ActiveWorkbook.Sheets("Source").Range("B18").Value ' where your filter is automatically identified, mine is a load page that holds various info
                    pvFld.CurrentPage = strFilter

This is currently working on my example as I have then hidden all the rows that have the filters showing on and added slicers for ease of data manipulation by the users of the filr.

thanks