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

 

[gmazza76]

I haver tried the following but I get an error "Run-time error '1004': Application-defined or object-defined error on the last line and I havent a clue where to go

DateFrom = Worksheets("Sheetname").Range("B12").Value
DateTo = Worksheets("Sheetname").Range("B13").Value
    
ActiveSheet.PivotTables("tablename").PivotCache.Refresh
ActiveSheet.PivotTables("tablename").PivotFields("FilterFieldDate").ClearAllFilters
ActiveSheet.PivotTables("tablename").PivotFields("FilterFieldDate").PivotFilters.Add Type:=xlDateBetween, Value1:=DateFrom, Value2:=DateTo

 

[SpillerBD]

You need to convert the dates to text.

 

[gmazza76]

sorry @SpillerBD what do you mean in the "Date From" part Text(worksheets("sheetname").range("").Value?

 

[gmazza76]

I have tried the following as when I try Text I get an error Compile error : sub or function not defined
With the code below I am back at the 1004 error

DateFrom = Format(Worksheets("LiveStaffLoader").Range("B12").Value, "Short Date")
DateTo = Format(Worksheets("LiveStaffLoader").Range("B13").Value, "Short date")

thanks in advance

 

[SpillerBD]

FORMAT can work, but I think your defining the short date with the wrong terminology.

DateFrom = Format(Worksheets("LiveStaffLoader").Range("B12").Value, vbShortDate)

TEXT is worksheet function and need to called like... (I make this poor choice because it is sometimes easier to expect others to recognize the Excel function versus the VBA function.

DateTo = WorksheetFunction.Text(Worksheets("LiveStaffLoader").Range("B13").Value, "MM/DD/YYYY")

I guess another potential issue is if the cell your dates are going into are already formatted as Text, then trying to use functions in the VBA expecting dates will be a problem in reverse.

 

[gmazza76]

thanks for the above @SpillerBD but i still get the 1004 and I am unsure where to go with it now

 

[SpillerBD]

Timeline?

 

[gmazza76]

afternoon @SpillerBD,

I am thinking instead of trying to do a to and from date just choose a week number either setting a slicer when the update is done that way I can adjust a few tables in 1 go?

Do you think this would be a good idea, but whats the best way to do this?
I have seen the below code, but I am unsure how to get the CWN value to be select as that would be the current week for the filter to align to as well as the CYear for the correct year

Dim pf As PivotField

CYear = Worksheets("LiveStaffLoader").Range("B11").Value
CWN = Worksheets("LiveStaffLoader").Range("B12").Value
PWN = Worksheets("LiveStaffLoader").Range("B13").Value

PivotTables("PivotTable2").ClearAllFilters
Set pf = ActiveSheet.SlicerCaches("Year") _
     .VisibleSlicerItemsList = VisibleItemsList(Select)

thanks in advance

 

[gmazza76]

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