Freddie has attended:
Excel VBA Introduction course
Updating Pivot Tables with latest data
Hi Guys
I'm trying to automatically update a pivot table by showing the latest source data (without the need to constantly keep selecting it). I have the below but it keeps falling over.
FileYear = Year(Date)
strFileMonth = Format(DateAdd("m", -1, Now), "YYYYMMDD")
FileDate = Format(Date - 1, "dd.mm.yyyy")
RateMonth = Format(Date - 1, "dd.mm.yyyy")
Sheets("PIVOT LDN").Select
ActiveSheet.PivotTables("PivotTable5").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable5").PivotFields("FSDATE").CurrentPage = _
("ALL")
With ActiveSheet.PivotTables("PivotTable5").PivotFields("FSDATE")
.PivotItems("strFileMonth").Visible = True
End With
End Sub
Any advice on this?
Thanks
Freddie
RE: Updating Pivot Tables with latest data
Hi Freddie
Difficult to reproduce your question. Maybe send all the code or the workbook file to info@stl-training.co.uk
One thing to check. As strFileMonth is a variable I don't think it should have quotes around? See last line but one of your code.
Regards
Doug
Best STL
RE: Updating Pivot Tables with latest data
Hi Freddie
Thanks for sending your full code. For the part where you are filtering the pivot table (last 3 lines) try replacing with this
With ActiveSheet.PivotTables("PivotTable5").PivotFields("FSDATE")
.NumberFormat = "yyyymmdd"
.CurrentPage = strFileMonth
End With
Doug
Best STL
