Graph Based on Pivot Table Not Updating When New Data Added 1

[shelby55]

Hi

I am using Excel 2010. I have a worksheet with data and used named ranges so the graphs change automatically. The data is based on dates and I wanted to also have a monthly graph so created a pivot table where I could group by month and I'm graphing the results.

Everything is working fine except it isn't refreshing when I add new data. I've seen some other threads on TekTips dealing with this but the examples are all for where the data source is external or via MS Query. My data source is just entered data on a worksheet in the same workbook. Will workbook.refreshall work or is it different becuase it's a pivot table?

Thanks.

 

[SkipVought]

Hi,

1. Enter data in source table.

2. Refresh the Pivot Table referencing your source table.

You stated that your source table is a named range. How is it named so the name range changes as the table range changes.

MUCH BETTER if you make the source table a STRUCTURED TABLE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip...but I still don't know what to do.

I'm not sure what event I should put it in. The issue is that I don't even need to access the pivot table itself or the worksheet it's on, I just need it updated because it feeds a graph that I need to update.

Any help greatly appreciated.

 

[shelby55]

P.S.
My data table is a dynamic named range(DataTable)

 

[SkipVought]

Graph Based on Pivot Table Not Updating When New Data Added

The issue is that I don't even need to access the pivot table itself or the worksheet it's on...

Regardless, since the Picot Chart is based on the Pivot Table, the Pivot Table must be refreshed...

PivotTableSheetObject.PivotTables("The Right PT").PivotCache.Refresh

So procedurally, what occurs immediately after changing the Source Data, until the time when you view the graph in question? Please specify each step.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI Skip

Okay, I go into the workbook and to the data worksheet. I add data in for the day I'm working on and then I go to the graph worksheet (whose source of data is the pivot table) to print the graph. As mentioned, I don't need to access the pivot table worksheet if I know the data is refreshing properly. Thanks.

 

[SkipVought]

So is there anything unanswered?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Skip

I'm still not getting it.

In the VBA for the worksheet containing the graph I've got:

Private Sub Worksheet_Activate()
ThisWorkbook.RefreshAll
PivotTableSheetObject.PivotTables("The Right PT").PivotCache.Refresh
End Sub

But nothing happens.

 

[SkipVought]

well WHAT is YOUR PivotTableSheetObject??? In other words the Sheet Object where your PT resides.

and WHAT is YOUR The Right PT Name??? In other words the Name of the PivotTable or 1 (not "1") if it is the ONLY PT on that sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I didn't pay attention, but RefershAll should refresh any and all PTs & external data imports in your workbook, and you should NOT need the last line of code that I belabored in my previous post, just the RefreshAll.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And there is no reason, 1) if your Dynamic Named Range (or Structured Table Name) is the source reference for the PT and 2) no filters are set in your PT Chart -- should work! Works for me!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Both conditions apply i.e. no filters and named range is source of data. I also know the named ranged is working properly because otherwise there would be no changes when I manually selected Refresh All i.e. the Pivot Table does refresh when done manually, I just can't get it to work via VBA.

Just to check, the code is going in the worksheet code where the graph sits or should it be somewhere else? Any global features that it could be? Thanks.

 

[SkipVought]

When you activate that sheet, all PTs should refresh.

Test the state of Application.EnableEvents. If it is FALSE, then this code will not execute in the Worksheet_Activate event!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Not sure how I test the state but I put the code in to enable them.

Private Sub Worksheet_Activate()
Application.EnableEvents = True
ThisWorkbook.RefreshAll
End Sub

It still isn't working. Just to test I put the "refreshall" in the WorkBook_Open event and that worked but I don't want my colleague to have to close it and then open it to get the recalc to work. Any other thoughts on this Skip?

 

[SkipVought]

Not sure how I test the state

In the VB Editor, open the IMMEDIATE WINDOW.

Paste the following and WITH THE CURSOR IN THAT DATA hit ENTER

?Application.EnableEvents

The Application.EnableEvents = True code must be executed OUTSIDE OF and Event code, like Worksheet_Activate! Otherwise it will NEVER run!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Not sure what you were saying but in in an immediate window I copied the code you provided, I made sure my cursor was on the worksheet with the code I want and I hit enter. It came back "true"...but still not working as it should.

 

[shelby55]

Hi Skip

I decided to do something different by adding the code to the deactivate event of the data worksheet. Now it works.

Thanks for all your help.

 

[SkipVought]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}