VBA to insert Pivot Table Last Refresh Date

[macattackpro]

Hi Gurus,

First, I'm fairly new to VBA but have realized it is the only way to accomplish what I'm trying to do and 100% willing to learn.

What I have is a workbook with 3 different pivot tables. I'd like for a reference above each to say when that particular pivot table has been refreshed. I've found code that will work beautifully (below) but when I view the workbook online I see that VBA does not work. So the question becomes: How do I get this code to post to the cell the refresh date of a specific pivot table?

Option Explicit
Public Function LastPvtUpdate(rngCell As Range) As Variant
Dim cPvtCell As Range
Dim pvtTbl As PivotTable
On Error Resume Next
Set pvtTbl = rngCell.Cells(1, 1).PivotTable
If Err.Number = 0 Then
   With pvtTbl
      LastPvtUpdate = .RefreshDate
   End With
Else
   LastPvtUpdate = "Error: No Pivot Table Reference!"
End If
End Function

Thanks so much for your time!

 

[raven_squire]

Is this what you are looking for?
Sheets("Your_Sheet_Name").Range("A1").Value = LastPvtUpdate

It sounds too simple, perhaps I don't understand what you are after.

 

[macattackpro]

Hi Raven,

I think we're on the right path with that but would it work for all 3 pivot tables independently? To make matters more confusing, all 3 pivot tables are on the same sheet.

I've thought about just using the below code but it works when "any" pivot table is refreshed. I need to account for the user potentially not refreshing one of the pivot tables so being able to make this target only the pivot table that gets refreshed would be fabulous.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Range("C1").Value = Now()
Range("G1").Value = Now()
Range("K1").Value = Now()
End Sub

 

[raven_squire]

I is my understanding that the user refreshing the pivot table manually refreshes all pivot tables in the workbook. So the value for .refreshdate will be the same for all pivot tables.

How often does the user need to refresh the pivot table?
Would automatically refreshing all pivot tables when a sheet that contains a pivot table is selected meet your needs?
Would automatically refreshing all pivot tables at set intervals meet your needs?

 

[macattackpro]

One could actually refresh each pivot table independently by right-clicking the pivot table and choosing Refresh. There is an option in the ribbon to refresh all pivot tables but I need to account for the fact that the person responsible for refreshing these pivot tables may not "Refresh All" and simply refresh one of the tables.

The frequency of the refresh is only when building the report which would be once a week potentially.
Automatically refreshing the pivot table at an interval wouldn't work due to the file ultimately being housed and accessed through Office 365 Online which doesn't support VBA.

I would be open to any ideas on how to get all pivot tables to refresh once one of them is refreshed. That work will be done in Excel when building the report so VBA is fair game here.

 

[raven_squire]

What I have done is to put this code in the worksheet containing the pivot table and it will refresh all pivot tables when that sheet is selected. You could also have it execute when the sheet is opend.

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

In your code you refer to the pivot table in relation to the selected cells. But I beleive you can reference them from the sheet name (https://peltiertech.com/referencing-pivot-table-ranges-in-vba/)Set pvtTbl1 = <tt class="tt">ActiveSheet.PivotTables(1)
Set pvtTbl2 = <tt class="tt">ActiveSheet.PivotTables(2)</tt>

Rather than displaying the refresh times in cells I would just check that all of the pivot tables had been updated in the last 24 hours prior to executing the rest
of the code that generates your report.

You put up some code that executed when any pivot table is refreshed. You could use that with the .RefreshAll but you might neet to incorperate some
mechanisim to stop an infininate loop.

</tt>

 

[macattackpro]

This seems too simple to work but it looks like it is. I appreciate your help!

 

[jamesraywebber]

Is there a way for the pivot table to look at a cell for the date and filter the table upon that date?

 

[raven_squire]

I can think of a way but it is not straight forward.

Firstly record a macro and fiddle with the filters for the table, so that you can learn how to reference the filters.

Use a macro that is trigered when the sheet is selected.

Have the macro read the value from the cell and set the filters acordingly.