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!
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!