How to generate drilled down data from pivot table alike view into a single newsheet?

  • Hello folks,

    I am relatively new to VBA and I need to accomplish this task using vba which I am trying from few weeks. I have a pivot table connected to a sql sever database. Now, my users would like to make edits in pivot table data values but as pivot table doesn't allow edits, I created a similar looking view using =getpivotdata() function in same sheet just beside pivot table so that users can manually adjust some values. My goal is that when the user is done editing values in that view, vba copies all cell values from that view into a single newsheet (no different sheets for each double-click) just as drilldown functionality of pivot table. The issue is that view doesn't have drill down capability, so on double -click vba cant show underlying data for all cell values into a single newsheet.

    I could accomplish this when I double-clicked on any cell from pivot table,but I dont know how to work the same with that view that I created using getpivot function. End goal is I need a drilldown of underlying data (just as we see on double-clicking any cell in pivot table) for all cellvalues from that view into a single newsheet.

    Any help would be highly appreciated.Let me know if I need to throw more details.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!