Pivot table date filter dynamically using cell value

DC2595

New Member
Joined
Feb 1, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
1580580925272.png


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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top