I have a master pivot table. I need to break it into a bunch of smaller tables so that every manager only gets his details without being able to see anybody else's data
I figured instead of coding pivot table creation from scratch- I'll just copy Summary and Data tabs into a new sheet, delete all the information that does not belong to a particular manager, point pivot cache to a new range, save and repeat for how many managers I have.
I did that and a weird thing happened. When I open a file my code creates, whenever I try to manipulate the pivot - I get a message "The PivotTable was saved without the underlying data. Use the Refresh Data command to update the report". once I refresh Data- everything works fine, I am able to drill and the pivot range is accurate. I re-save the file but once I open - same story- "no underlying data message" again....
I googled and somebody was suggesting updating Pivot cache before closing instead of refreshing- I tried that too...
Can somebody please help?
Bookmarks