Using VBA to Automatically Filter and Pull Data from a Pivot Table

[BambooRoot]

Hi guys,

I'm loving this forum so far, I've always subscribed to the beleif that if you're trying to learn any new skill, surround yourself with as many 'gurus' as possible. You've all been a huge help.

So for everyday purposes I'd consider myself an 'intermediate' Excel user. I'm looking to upgrade my skillset slightly by incorporating VBA. I’ve been playing with the macro editor, but I’m currently stuck on the following:<o></o><o> </o><o></o>
<o></o>
<o></o>
I have a pivot table located in Workbook1. My task is to filter the data in this table, and cut and paste the results into another workbook, Workbook2. I’m wondering if there is a way to automate the entire pivot-table filter process, such that the code applies the filters, pulls the required data, and pastes it into the appropriate rows. If anyone could point me in the right direction (don’t write out the entire code as I’d like to figure it out eventually) that would be great.
<o></o>
<o> </o>
<o></o>
<o></o>
<o></o>
Thanks in advance,
<o></o>
Steve
<o></o>

 

[Jerry Sullivan]

Hi Steve,

The code for the filtering step will depend on some details.
1. To which areas of the PivotTable are you applying the filter (Report Filters, Row Labels, Column Labels or Data Values)?
2. Are you filtering to show a single item, mulitiple items in a list, or a criteria-based filter like (dates after 1/1/13, or sales > 10,000)?
3. Will the source for the filter criteria be written into the VBA code, or read from somewhere like a range or data validation cell?

Regarding the copy part, you can get a good start using the Macro recorder; however it will record a specific range address that was copied.
To make that more dynamic, look in the VBA Help documentation to understand how to reference different parts of a PivotTable using its properties.
If your PivotTable object is assigned to the variable PT, you can use references like: PT.TableRange1, PT.DataBodyRange to copy the parts that you want.

 

[BambooRoot]

Hi Jerry, thanks for the reply!

So I've managed to work out the code over a lengthy VBA session last night. The one thing that I am still stuck on though is this:

The report that I'm populating is a monthly one. Each month one additional row is added to the report, meaning the range that I'm populating will shift. So far my query will only populate the range I initially designate, meaning next month, it will pull the numbers from Workbook2 and paste them over the numbers from this month.

I imagine I can use either the match function (i.e. to match the appropriate months with each other) or an offset function, but I can't figure out how to code either of these. Do I place the formula at the beginning of the code? Or somewhere in the middle?

 

[Jerry Sullivan]

Steve, If I'm understanding you correctly, you can use code like this to find the last row of data in Column B, then paste your PivotTable data for the current month in the blank row below it.

With Workbooks("Workbook1").Sheets("Summary")
    .Cells(.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
End With

 

[mattress58]

Did you get this complete. Are you able to send me the thread? This is exactually what I am trying to do. I can just change the cells.

Thanks for any help,

matt.terriault@gmail.com