Pivot Table not displaying refreshed data

[mikeymay]

I have a pivot table (1 of 3) that is using a dynamic range as its source data.

The two other pivot tables, when having a new data set as the source data, display the correct data as per a pivot table should, but one of the pivot tables just refuses to show the refreshed data.

When I rebuild the table by creating a new pivot tale, it shows the correct data but when then a new data set is used as the data source, it looks like it is retaining the data it was displaying previously.

I have tried refreshing, changing the retained data to none but nothing other than creating a new pivot table seems to work.

The data to be used as the data source is added via a macro and needs to be fully automated so I can't use any workarounds that require the user to do anything to make it work.

Any advice would be hugely appreciated as I have no idea why Excel is acting like a petulant teenager!!??

 

[ouvay]

Have you tried meddling with those 2 active filters? Might give you the desired output

 

[mikeymay]

Sorry, the image was supposed to be part of the post - I have put together a long and complicated post and decided to go with something simpler.

But with regards to the filters, none are applied.

I am just putting together something with confidential data removed to demonstrate what's going on and will post soon.

 

[mikeymay]

So, after a lot of messing around I think this issue was a corrupted pivot table.

When I rebuilt the PT, I used the same name as the one that wasn't working as it was referred to in the VBA code.

When trying to run the process again, Excel just stopped when it came to changing the filter and I had to close via Task Manager.

I created a new PT but gave it a different name, changed the PT name in the code and it now seems to work...

I don't know if it's just me, but Excel has been playing up a LOT recently......lots of issues that just don't make sense in the last few months whereas, in the previous years, very few issues at all!

 

[mikeymay]

I spoke too soon....

Now the process is stopping dead when it is trying to change the PT filter

wsPT.PivotTables("ptVisitList").PivotFields("Class. Owner").CurrentPage = strPerson

When put a stop in on this line and step through it (F8), it works every time but when I let it go on its own it stops Excel dead again and I have to restart.

I will try rebuilding the sheets that contains the pivot tables in case the sheet is corrupted.

 

[mikeymay]

Nope....doesn't like that either, next is to rebuild the whole file...FFS

 

[Alex Blakenburg]

I am just putting together something with confidential data removed to demonstrate what's going on and will post soon.

If you want to follow through with this we can have a look.

 

[mikeymay]

If you want to follow through with this we can have a look.

I'm just in the process of rebuilding but I'd like to know what is causing it if you're happy to help?

 

[mikeymay]

I have just finished rebuilding the file and I have exactly the same problem - When one of the three PTs are being manipulated (the filer is being changed), it stops completely.

 

[Alex Blakenburg]

How much code is there ? Can you start with posting that (please use the VBA button) if you don't have a file you can share.
Are you getting an error message if so what is it and what line is highlighted ?