Extract data from Pivot Table

Hi All,

This is probably a relatively simple macro to program but I have extremely limited experience with coding so any help is welcome.

What I am trying to do is pull a series of specific rows from a pivot table into separate "reports"Example.xls. Reference the affixed Excel workbook under the worksheet 'Pivot Table'. I want to pull each of the different product names (Consults, MLPA, PIA, etc.) and the associated rows into a separate report. I would use the grouping function but, as you can tell, several of the rows include multiple products.

Is there a way to pull out the rows for each of the products? I've included an example of a report I created manually by selecting the rows in the product filter drop-down.

I also am looking to create a report that summarizes the product count (number of MF for example), the product frequency (how many times did MF show up in September) and message type count (number of Business Messaing for example).

Again, I have no idea how to even approach this considering the lack of VBA knowledge but any help is very much appreciated.

Example.xls

Hi All,

Would love some feedback on this before the end of the day! Thanks.

Hi All,

I really need some assistance on this task. It seems relatively straightforward, so if anyone could push me in the right direction it would be much appreciated. Thanks.

Hi Marvin,

Thanks for the response.

The pivot table to the left of the worksheet you sent along is what I am looking for. I want to have an automatic process that produces these reports for each of the products (Consults, UMA, etc.). The problem is that I have to go through and tick the boxes for all the different variations of Consukts (Consults, UMA and then Consults, UMA, PIA).

Also, I am trying to sort by the month instead of the days to be able to see how many times Consults sent a Business Messaging communications in December, for example. Is there a way to do this and have Excel automatically do this for several of the products? I want to be able to produce these mini-reports every couple of months.

Skipper

Hi Marvin,

That's really helpful, thanks.

The next thing I want to do is automate this process somewhat. Two things on this:

I. Is there a way to create an external 'search box' linked to the pivot table where someone could type in the query instead of having to go into Label Filters --> Contains?

II. Is there a macro that could automate the process? Instead of going through and repeating the process for UMA, Consults, etc. I could press a button and have each of these mini-reports spit out for say the last 8-12 weeks.

Thoughts?

Thanks again!

Hi Marvin,

Thanks for the info, it's been really helpful. Slicers is probably what I am looking for. If you think of any macros that would allow me to pull the 'Label Filters --> Contains' funtion into a searchbox in a cell, let me know.

Skipper

Hi Marvin,

I actually do have Excel 2007, so I am able to utilize the Label Filter option, which is great.

What I am still looking for is a basic VBA that automates the process of typing in 'Consults' or 'UMA'. It'd be great if one could simply click a button that would automatically input the 8 or so different products I wanted to pull data form, define the timeframe from which to pull the data (say between October '11 and December '11), and spit out 8 different reports. Is there a way to do this?

If not, is there a way to create a searchbox that serves the function of the Label Filter? That is, can I create a cell that mimics this function so that one could simply type in 'Consults' into the cell and receive the report that way?

I do appreciate all the support.

Skipper

@ Skipper Jones

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

@ Cutter,

Thanks for the heads up, I'll be sure to do so in future posts. Any idea about the questions I posed @ Marvin?

Appreciate all the assistance.

Hi All,

Thanks for all the help thus far.

My final report is due Friday so I would love so assistance regarding assigning macros to drop-down (i.e. Data Validation) lists, if possible. I've tried to use the 'call' function to assign macros to specific items, but have been unable to execute the macro correctly as of yet. Any help on this would be greatly appreciated.

Thanks!

Skipper