How To Update/Refresh Drill Down Pivot Report from MS Query Source Data ???

[raghav007]

Hii Excel Pros ,

I am lover of Pivot Tables and use them every where I see data . However In my new job I am stuck with a problem which
I am hoping friends here can help me out with.

Problem:
My Organisation has a complaint software that lets me export all the data to a excel file. Below are the column headers I get.

SL.No
IsRead
ClientName
Weight
ComplaintNo <---
Type
Label
NDox
SType
emailstatus
Send by
Send To
Send By2
Sent To <-----
BookingDate <----
AWBNo
ComplaintDate
Priority
ComplaintDetails
ComplaintStatus
PhoneNo
Email
Mode
Origin
Destination
To Email
User
Reply
Reply SMS
ForwardedToLocation
LoggedByLocation
ForwardedDate
TAT
Follow Up By <-----
Days CD <-----

Now the whole data goes from =$A$1:$AI$398 which can change with the increase in complaints. I use only colums i`ve marked with arrows.

I have a created a new excel file to import these specific columns using micrsoft query and making data appear as a pivot table.

The issue that I face is , I`ve created plenty of drill down sheets using the pivot( created via ms query ) for my different employees.

When I update my source data , my pivot tables refreshes and matches up with my fresh data , either increases complaints or
reduced complaints.

However the Drill Down Sheets don`t update ,they remain the same.

I want to them to refresh aswell in lines with fresh source data.

Hoping if you friends can help me sort out this issue.

Thanks to ya all......

 

[sergioMabres]

what do you mean by drill down sheet?
The sheets that show when you double click a cell in a pivot table?
Sergio

 

[raghav007]

Yes Sergio...

 

[sergioMabres]

I have try to re produce your problem but I cannot.
If I add a record in the table
Pivot table does not update and drill down does not update
Then I update the Pivot Table using menu, pivot table, update or code

    ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
    Range("C4").Select
    Selection.ShowDetail = True

And the drill down shows updated

 

[raghav007]

Sergio, how should i add the code.

 

[raghav007]

Bump """

 

[sergioMabres]

Create a macro, here is some instructions on how to create a macro
Create or delete a macro - Excel - Office.com
I hope this helps