I have a worksheet that get recreated every time a macro is run. I also have a pivot table that is dependent on data from this 'macro generated' worksheet.
What I have noticed is that, every time the macro is run, a new worksheet is created (old one is deleted) with same attributes as old one (like column names, etc)
Now, on the pivot table (is located in different worksheet), the range shifts/changes every time a new worksheet is changed.
Is there anyway to assign the range of the pivot from VBA? Or keep the range constant regardless of things happening to source?
What I am trying to do is get data from pivot which refers back to the macro-generated table.
Is there any way I can reset the data range of the pivot and refresh it? Or is there better suggestions?
Appreciate your help!