VBA - Change Date in PivotTables

quin1997

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
Hello! I am trying to write a macro that will change the date on a pivot table in a different workbook. I have vlookups in my current workbook to find the correct data but I want to be able to change the date without having to open the other workbook. I have written the following which worked well on a test file, however, on the actual documents I am working with I get the following error: "Run-time error 1004: Unable to get the pivotFields property of the pivot table class." The macro is as follows:

Dim pt as PivotTable
Dim ws as WorkSheet
Dim d as string

d = Thisworkbook.worksheets("Sheet2").Range("d4") 'this is the cell that contains the date so I can change it as I wish

Workbooks.Open... 'just opens the relevant workbook

Set ws = Activeworkbook.Worksheets("Standard")
Set pt = ws.PivotTables("PivotTable22")

pt.PivotFields("Date").PivotFilters.Add Type:= xlDateBetween, Value1:=d, Value2:=d

End Sub

As mentioned this worked on my test file but not on the actual one I need. I think it may be because of the pivot field list as seen in the image below, date is a 'subfield' of Reporting date. Is there a way to select only date? Thanks!

MicrosoftTeams-image.jpeg
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,484
Try

PivotFields("[Reporting Date].[Date]")

Or record a macro and make the selection, should let you know the syntax
 

quin1997

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
Not sure why I didn't think of recording a macro! But yes have it working now so thanks!

Just for reference it works with:

Dim d as String
d = range ("D4") 'the cell containing the date but in text formatting
pt.PivotFields("[Reporting Date].[Date]").PivotFilters.Add Type xlCaptionEquals, Value1:=d
 

Watch MrExcel Video

Forum statistics

Threads
1,112,772
Messages
5,542,436
Members
410,552
Latest member
Yogesh977
Top