Auto Refresh Pivot Tables Excel 2016 for Mac

I would like a Pivot Table and Chart to refresh automatically when the source data has changed. I have tried some VBA codes that were posted on the internet but they made Excel crash.

Any suggestions? Look forward to your input.

Try below.

Worksheets("YourWorksheetName").PivotTables("YourPivotTableName").RefreshTable

If this doesn't work, upload sample workbook which mirrors your actual workbook set up.

That line itself did not work. I am not familiar with VBA. Is there anything else that needs to be entered? Attached is a part of the file that I am working on with the Pivot Table.

Depends on what event should trigger refresh.

If you want the pivottable to be refreshed when your workbook is opened.
Place below code in ThisWorkbook Module.

Private Sub Workbook_Open()
Worksheets("Sheet1").PivotTables("PivotTable2").RefreshTable
End Sub

If you need some other event to trigger refresh of the table. You'll need to specify what should trigger pivottable refresh.

The trigger should be the location in the attached spreadsheet. As soon as the location of one of the units changes, I would like the table to refresh. I have tried a similar line as CK76 has posted, but that crashes Excel...

Then in Sheet1 module. Something like below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Table3[Location]")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Me.PivotTables("PivotTable2").RefreshTable
End Sub

See attached (fixed broken link etc).

That seems to work. Excellent. Thank you very much...