Hi, I am trying to make my pivot table's "shop date" filter be updated by my macro based on some cell values in a settings tab. I originally tried to use an array to select multiple dates that would be in the filter to no avail.
Now I am trying to simply update the filter to select one date, using the value in a specific cell in my settings tab. This cell's value will by dynamically updated and will change many pivot tables hence why I'm trying to use vba to complete the task.
Below is the code I have so far, I had tried a few methods but had failed repeatedly to make it work so this is the latest attempt, stripped back to the most basic code rather than anything fancy I had found. Many thanks for any advice people can give me! Potentially useful to know - one problem I ran into was getting the String/Variant date that is stored from the source cell converted into a date format to work in the pivot table.
Here is a screenshot of the filter I'm trying to change:
I suspect I unfortunately can not post the file I'm testing this on as it is linked to databases which require specific server connections.
Option Explicit
Sub Update_shop_date()
Dim RSDate As String
Dim PTable As PivotTable
Dim PField As PivotField
Dim PItem As PivotItem
RSDate = Worksheets("Setting").Range("M10").Value
'cell m10 currently has "29/10/2019" written for reference
'to check if it's got the date+it's format, only for initial building
MsgBox (RSDate)
Set PTable = Worksheets("CW").PivotTables("PivotTable1")
Set PField = PTable.PivotFields("[Shop Date].[Year - Week - Date].[Date]")
'not sure how to use PivotItem properly which may the main problem
Set PItem = PField.[Shop Date].[Year - Week - Date].[Date]
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'I tried to test with simply typing in the date in the excat same format that manually recording the change showed medoes not work
PField.CurrentPageName = "[Shop Date].[Year - Week - Date].[Date].&[2019-10-29T00:00:00]"
'despite being very similar to me, the above does not work even though the below does - obtained from recording manual change. This gives indicator of format required in this pivot field?
'Worksheets("CW").PivotTables("PivotTable1").PivotFields( _
"[Shop Date].[Year - Week - Date].[Year]").CurrentPageName = _
"[Shop Date].[Year - Week - Date].[Date].&[2019-10-31T00:00:00]"
'I want to end up with being able to use RSDate effectively instead of manually typing the date in vba, example below
'PField.CurrentPageName = "[Shop Date].[Year - Week - Date].[Date].&[RSDate]"
ActiveWorkbook.RefreshAll
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Now I am trying to simply update the filter to select one date, using the value in a specific cell in my settings tab. This cell's value will by dynamically updated and will change many pivot tables hence why I'm trying to use vba to complete the task.
Below is the code I have so far, I had tried a few methods but had failed repeatedly to make it work so this is the latest attempt, stripped back to the most basic code rather than anything fancy I had found. Many thanks for any advice people can give me! Potentially useful to know - one problem I ran into was getting the String/Variant date that is stored from the source cell converted into a date format to work in the pivot table.
Here is a screenshot of the filter I'm trying to change:
I suspect I unfortunately can not post the file I'm testing this on as it is linked to databases which require specific server connections.
Option Explicit
Sub Update_shop_date()
Dim RSDate As String
Dim PTable As PivotTable
Dim PField As PivotField
Dim PItem As PivotItem
RSDate = Worksheets("Setting").Range("M10").Value
'cell m10 currently has "29/10/2019" written for reference
'to check if it's got the date+it's format, only for initial building
MsgBox (RSDate)
Set PTable = Worksheets("CW").PivotTables("PivotTable1")
Set PField = PTable.PivotFields("[Shop Date].[Year - Week - Date].[Date]")
'not sure how to use PivotItem properly which may the main problem
Set PItem = PField.[Shop Date].[Year - Week - Date].[Date]
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'I tried to test with simply typing in the date in the excat same format that manually recording the change showed medoes not work
PField.CurrentPageName = "[Shop Date].[Year - Week - Date].[Date].&[2019-10-29T00:00:00]"
'despite being very similar to me, the above does not work even though the below does - obtained from recording manual change. This gives indicator of format required in this pivot field?
'Worksheets("CW").PivotTables("PivotTable1").PivotFields( _
"[Shop Date].[Year - Week - Date].[Year]").CurrentPageName = _
"[Shop Date].[Year - Week - Date].[Date].&[2019-10-31T00:00:00]"
'I want to end up with being able to use RSDate effectively instead of manually typing the date in vba, example below
'PField.CurrentPageName = "[Shop Date].[Year - Week - Date].[Date].&[RSDate]"
ActiveWorkbook.RefreshAll
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
