Export Pivot Table Data Using Report Filters

[Todd Johnson]

Hello,
I have some data that I have combined into a pivot table. I currently have "salesman" as my report filter with some other attributes in the row and values. I understand when I use the report filter pages and "show report filters" Excel will create multiple pages based on my report filter. When excel exports this data the new pages are in the form of a pivot table. Is it possible to have the source data behind the newly created pages displayed instead of the individual pivot tables? What I would do is double click on the total from the new page to show all of the data but when you have 100+ salesman it becomes a time consuming task. I've uploaded an example. The green tabs are what I would like exported when using the report filter.

Thanks,
Todd

 

[David Evans]

Hi Todd -

What are you proposing to do with the green tabbed sheets? Given that you have 100 salespersons, do you want to have a sheet for each?

You can arrive at each sheet by throwing a salesperson slicer on the original data - you can drive that from some VBA to loop through each salesperson and create the necessary list. I've done that with an application in the past - it's not too difficult but I want to be sure I understand where you're trying to get to ...

 

[Todd Johnson]

Hi Todd -

What are you proposing to do with the green tabbed sheets? Given that you have 100 salespersons, do you want to have a sheet for each?

You can arrive at each sheet by throwing a salesperson slicer on the original data - you can drive that from some VBA to loop through each salesperson and create the necessary list. I've done that with an application in the past - it's not too difficult but I want to be sure I understand where you're trying to get to ...

Thanks David. I would like a sheet for each. The sheet will eventually go to the salesman to verify data. Ideally it would be in a separate workbook for each salesman but I'm sure I would need to write VBA to do that, which I don't know how to do. If I could get the source data into individual worksheets that would work for me (the data isn't sensitive) and I thought I may be able to accomplish that without writing code. If there isn't a good way to do it, I'll have to have my IS department create a macro for me.

Thanks for your response.
Todd

 

[David Evans]

Thanks David. I would like a sheet for each. The sheet will eventually go to the salesman to verify data. Ideally it would be in a separate workbook for each salesman but I'm sure I would need to write VBA to do that, which I don't know how to do. If I could get the source data into individual worksheets that would work for me (the data isn't sensitive) and I thought I may be able to accomplish that without writing code. If there isn't a good way to do it, I'll have to have my IS department create a macro for me.

Thanks for your response.
Todd

I did something very similar recently, except I wrote about a thousand files out from a Table using VBA - it was how the client wanted to do it - I could think of better ways of achieving the same goal, but he who pays the piper gets to name the tune ...

One way to do it is to create an array of all your salespeople and then you step through each salesperson, filter the table to reflect that person, and then use a VBA command ActiveWorkbook.SaveCopyAs Filename: where you save the file under a naming convention based on the Saleperson's name and possibly date and whatever else you want to include there - essentially what you have is a Master Worksheet that "spawns" child files by looping through your list of salespeople.

It might appear a little complicated, but it's actually quite straightforward .... If i get a little time over the coming days I shall dig up the old code and adapt it to your example so that you can give it a whirl ...