PivotTable auto refresh

I've found this code on the internet and can't get it to work properly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Set pt = Worksheets("Pivot table").PivotTables("PivotTable1")
If Intersect(Target, pt.TableRange1) Is Nothing Then
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End If
End Sub

My PivotTable & Chart are on the same sheet ('data sheet') as my reference table. The reference table takes its info from 14 other sheets. When I update one of the other sheets, the reference table on the 'data sheet' updates without problem. But the PivotTable & Chart will only update if a change is manually made to the 'data sheet'.

Can someone recommend any tweaks to this code to get the PivotTable & Chart to update when changes are made on the other sheets please?

Thanks for the reply Norie, however this doesn't work

With the code I showed, when a change is made on the data sheet, the PivotTable would update. Unfortunately with your code, the PivotTable won't update after any manual changes are made.

Do you have any other suggestions please?

I replaced my previous code with it, in the data sheet tab.

At the moment the reference table for the pivottable is auto generated when data on the other sheets is manually changed. I'd like the pivottable to auto update once the reference table has been updated. The code I posted lets the pivottable update, when any manual change is done on that data sheet.

This is my first go at VBA/ Macro etc, so apologies if I'm mixing anything up

Thanks. I've put it in there now and I get a run-time error on the second last line - Worksheets("November Data").PivotTables("PivotTable8").PivotCache.Refresh

run time error.jpg

Thanks for the reply romperstomper. Could you let me know what/ where I'd need to input for that to work?

Thats worked perfectly!!!

Many thanks for your help (and for your help also Norie)

Could I bend your ear one more time please, on a slightly different problem

I have another spreadsheet with each sheet having its on PivotTable. Can you recommend how I can get the PivotTable to auto update, when the data that has been changed is on the same sheet as the PivotTable?

Many thanks again. This works great when the data is manually entered. However...

I'm using 2 tables on one sheet. The first table contains a lot of data pulled from sharepoint, which has filters. I've set up another table to pull info from this, which the PivotTable pulls its data from.

When I change the filter on my first table, the 2nd table updates as it should, but the PivotTable isn't reading it. Interestingly though, the PivotChart keeps the old quantities but the bars don't show any info...

Bef Aft.jpg

Thanks again

That's brilliant and works like a charm. Really appreciate your help