Macro to refresh all data when a selection is made in lists

[_eNVy_]

I have 2 drop down lists for the user to select in Dashboard tab.
Based on the selection, information appears on screen (Dashboard).

I am running a pivot table in the background (Customer Filter tab) and a Pivot Chart in Dashboard. (I mean, i could just run a Pivot Chart but anyway).

I would like for the macro to refresh all data whenever a selection is made from either of the lists.

List names:
All_Service_Priority_2
ServiceArea_List2

Any help would be extremely helpful.
Thank you.

 

[jxb]

Have you had a look at worksheet_change? This can be executed automatically every time a change is done to the worksheet. Can restrict the execution to specific cell

 

[_eNVy_]

Thank you for your reply!
This would have been ideal however, I do not think I have properly explained.

Dashboard holds the 2 lists.
Custom Filter holds a data set which gets filtered based on what selections are done via Dashboard
Based on the selection from Dashboard, the data set changes in Custom Filter. A Pivot Table in Custom Filter was created to reference this change. (This is so that I do not have to make various Pivot Tables for every selection). A Pivot Chart was created and placed in Dashboard.

As soon as a change in either of the lists in Dashboard is done and the data set in Custom Filter has been automatically changed, I want the Pivot Table and Chart to be refreshed.

Private Sub Worksheet_Change(ByVal Target As Range)
 

If Target.Address = "$F$30" Then
 
Worksheets("Custom Filter").PivotTables("PivotTable1").PivotCache.Refresh

End If
 
End Sub

I have the following code which holds no errors but the Pivot Table in Custom Filter is not refreshing meaning the Pivot Chart in Dashboard is not refreshing.

Thoughts?

 

[_eNVy_]

Update :

I got the following code to work :

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address = "$C$25" Then
 
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh


End If
 
End Sub

But I need to add in another section which says:

If Target.Address = "$C$26" Then

As there are two lists which the user needs to choose from.

List 1 in C25 the user can change while List 2 in C26 the user may wish for the selection to remain.
List 1 in C25 the user may wish for the selection to remain while they change the selection in List 2 in C26.

 

[jxb]

how about having variable defined to capture the user choice and pass it to the sub? or simply grab it on the fly

or maybe add a msg box?

If Target.Address = "$C$25" Then
 
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
   response = msgbox("update  List2")
      if response =vbyes then ...
End If

 

[_eNVy_]

Thank you jxb.

I have the following code which works:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("C27:C28")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh



End If
End Sub