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
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