Memory Issue " Pivot Tables vs Dynamic drop down list

[acousticlife81]

What is better for memory management pivot tables that feed into a dashboard for slicers and pivot charts or a dynamic drop down list that populates the same information to a chart? I was also wondering does anyone have a solution to auto-refreshing a pivot table and pivot chart when new data is entered into the data set.

Thanks,

J

 

[sijpie]

Don't know the answer to the fiost question.

As to the second question, you could write some code in the worksheet module for the sheet holding the data that runs whenever changes are made to the sheet (Worksheet_Change() sub). Here you can tell Excel to refresh the pivot table/chart.

open the worksheet module by right clicking the worksheet tab and selecting 'View code...'
This opens the VBA editor in the worksheet module.

paste this code there:

<font face=Calibri><SPAN style="color:#007F00">'==============================================================</SPAN><br><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">' if changes are made to worksheet, refresh all pivot tables</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> ws.PivotTables<br>                    pt.RefreshTable<br>        <SPAN style="color:#00007F">Next</SPAN> pt<br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#007F00">'==============================================================</SPAN></FONT>