Dashboard

[Joeman187]

Hi Chandoo,


I have a question for you regarding dashboards. I have my data in one worksheet and my pivot table and charts in another worksheet. When I go to insert another row in the data it doesn't seem to update the pivot table automatically. I can use the change data source to update it but that isn't quite what I want. Any suggestions if I can do another way? Thanks Joe from Michigan.

 

[Hui]

Joeman187

You can use some VBA code to trigger the PT update when a certain area/cell on the first sheet changes


Copy and Paste this into the Worksheet Module of the worksheet that has the changing data in VBA

Then Adjust ranges/names to suit

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub 'Change address to suit
Application.EnableEvents = False
Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh 'Change Worksheet and PT Name to suit
Application.EnableEvents = True
End Sub

[/pre]

 

[Joeman187]

Hi Hui,


Not familiar with VBA codes. Can you tell me step by step if you have the time? Thanks, Joe.

 

[SirJB7]

Hi, Joeman187!


I hope Hui won't mind if I irrupt into your conversation. He's just told you the step by step process, but let me try again:

a) open a clean instance of Excel (so as to not get confused with other workbooks)

b) open your workbook

c) for versions 2007-2010, if your workbook's extension is .xlsx save it as .xslm (macro enabled workbook)

d) go to the VBA editor (Alt-F11) from Excel

e) In the left pane check for "VBA Project (<your workbook name&#62" and expand all the sub-tree

f) within that branch double click on the name of the related worksheet (the code area for that workbook will be displayed in the big top right -or just big right- pane)

g) check that first line says "Option Explicit", if not type it (not necessary but the safest practice)

h) copy Hui's code from his post

i) paste it below the "Option Explicit" line

j) go back to Excel (Alt-F11 from the VBA editor)

k) save the workbook

l) enjoy it!


Hope it helps.


Regards!

 

[Joeman187]

Thanks Sir,


That both you guys helped. They didn't cover VBA in my class. Thanks again.

 

[Joeman187]

Sorry fellas, I don't get it. I typed this:


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect (Target, Range("A1:B10)) Is Nothing Then Exit Sub

Application.EnableEvents = False

Worksheets("Sheet1").PivotTables("Pivottable1").PivotCache.Refresh

Application.EnableEvents = True


End Sub


I guess I'm not a programmer (lol)