Manipulate Pivot Table Criteria Using VBA 1

[CopperWire]

Is there a way to reference the data in a pivot table field in order to manipulate the data.

For instance. I have a month field and I want to be able to choose a month "say month 10" and have the month fields on my Pivot table show Month <=10, so 1-10, but "click off" months 11 and 12.

If someone could help, I'd greatly appreciate it!

 

[SkipVought]

Hi,

You don't need VBA to do this.

You could drag the Month field to the PAGE area.

Skip,
[sub]
[red][/red]
[/sub]

 

[CopperWire]

I probably didn't explain very well. What I want to do is to be able to filter the pivot based on criteria selected on a different sheet. This is a budget file, so if someone chooses month 10, then I need months 1 - 10 to be displayed on the pivot, but not months 11 and 12.

I would do this manually by clicking months 11 and 12 off in the month drop down list.

What I'm asking is if someone chooses month 10, if with VBA I can automatically have months 1 -10 selected and months 11 and 12 unselected.

The reason for this, is I only want the sum of the budget dollars and actual thru month 10 for a YTD amount.

Does that make sense?

 

[SkipVought]

You can STILL use a PAGE field.

But whichever way you go, turn on your macro recorder and do what you need to do for one selection.

Turn off the macro recorder and observe your code.

Modify as necessary.

If you need helpwith your VBA, please post your code and question in ...

VBA Visual Basic for Applications (Microsoft) forum707.

Skip,
[sub]
[red][/red]
[/sub]

 

[KenWright]

Personally I would add another field to your source data that fed from the selections chosen on the other sheet, eg

Assume you already have a field in your data called Month, and that contains values such as 1-12. You could now have another field that looked ath that value and then using something like COUNTIF, looked at the selected options on another sheet and determined whether or not that record fell within the selection criteria. This will give you a load of TRUE/FALSES in that field.

Now just drag that into the page fields, filter on TRUE, and then have code refresh the Pivot table every time the selection criteria is changed.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

now that's slick Ken - like that one !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

I use that all the time, especially for date parameters, which makes it real easy to constrain your data to within two dates, ie just using an existing date field and a simple >= and a <= comparison against two specific dates elsewhere in the file.

Also use it to put in groupings into the data where it doesnt already exist, using the same principle.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Used that for groupings but like the idea of a TRUE/FALSE output for changing criteria to be used like a filter - just never really thought of it like that before

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CopperWire]

Thank you for your help. You may thought that I forgot about this post, I didn't, have had some issues, just now getting back to it. Thanks again!