Disconnect Slicers, change data source for Pivot and then re connect slicers

[Paul33]

Hi, I'm new to all this VBA coding so forgive me because I know this question has been around in various guises before but I cant find the code to do exactly what I need to do. Here goes...

I have a workbook with the worksheets named Complaint Chart, Pivot Table, Pivot Charts.

I run a user form which allows the user to add complaints to the next blank line on the Complaint Charts worksheet, (I have named the range of data currently there ComplaintData). The pivot tables (x3 named PTComplaintsByDay, PTCreditValueByDay and PTComplaintsByType) are populated from the named range (ComplaintData). They also lead into the pivot charts which have slicers attached.

I have a button on the user form to show the Pivot Charts worksheet but this doesn't update the pivot tables and charts to include any new complaints that have been entered since I named the data range. So what I need to do is produce code to dis-connect the slicers, change the named range to include any new complaints that have been entered, refresh the pivot tables and then re-connect the slicers.

I have seen some examples of code to do most of those things individually but I have been unable to combine them together to get it to work.

Any help on how to produce this code or any examples of code that may do this with my small knowledge to adapt it would be very gratefully received.

 

[Paul33]

Please can anyone one out there give any help with this??

 

[RoryA]

Why do you need to disconnect the slicers? Simply updating the named range and refreshing the pivots should be fine.

 

[Paul33]

Hi Rory,

Thanks for your reply.

I was under the impression that as the slicers are linked to the pivot tables (and pivot charts) that they had to be disconnected before refreshing the pivot tables with the new data or it caused an error message in Excel.

As I said, I am new to all this so if that is not the case then sorry for my mistake. In case I just need to get an idea of the code required to update the named range to include any new data added and refresh the pivot tables.

Can you help please?


Here's hoping!
Paul.

 

[RoryA]

I would suggest you use a Table or dynamic named range for the source data, since they will automatically expand to include new rows of data and so all you need to do is refresh the Pivot tables. You shouldn't have to do anything to the slicers for this.

 

[Paul33]

That sounds exactly like what I need Rory, however I have named the range the current data is using can you tell me how to turn this into a dynamic named range or table please?

Thanks loads for your help.

 

[ThatMathGuy]

paul33,

If you select the range you'd like to convert, you can just go to the Home tab and to the left of the Styles box there's an option to 'Format as Table". From there you can rename it, set a color scheme, and as previous posts said it will auto expand upon new entries. Then set your pivot to reference the named table range and you never have to reset your pivot table range again, as it references the dynamic table. So you can click refresh all and you should be good, slicers and all!