Auto Refresh of Pivot tables when source data updates based on data validation list selection

yunxi

New Member
Joined
Dec 12, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, I have one source data sheet, two pivot tables that are linked to the same source data and the source data has formulas that will update based on data validation list selections in a separate sheet. I'm trying to add a vba code so that the pivot tables will automatically update when source data changes.
Below code works if I manually change source data or add new rows of data but doesn’t work if the source data is updated due to changes in the data validation lists in the separate worksheet that links to formulas in the source data.

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub


Please note the sheets containing the pivot tables also have existing codes below that allow users to select the fields to add to pivot tables by clicking on buttons.

How can I change the code so that the pivot tables will auto refresh when source data is changed (whether it's manual change in source data/additional fields/arise from changes in selection on data validation list) but still allow users to select the fields to add to pivot tables by clicking on buttons?

Sub Toggle_Row_Field()
'Add/Remove the field to the pivot table.
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim shp As Shape

'Set variables
Set pt = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text

'Toggle field
'If visible then hide it
If pt.PivotFields(sField).Orientation = xlRowField Then
pt.PivotFields(sField).Orientation = xlHidden
shp.Fill.ForeColor.Brightness = 0.5
Else 'Add to Rows area
pt.PivotFields(sField).Orientation = xlRowField
shp.Fill.ForeColor.Brightness = 0
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top