Add and Remove the Fields in an existent Pivot Table through VBA

**RagonichaFulva** · 02-23-2012, 12:54 PM

Hello,

I am trying to create a system consisting of:

A dropdown menu
A pivot table
A chart

The idea is that selecting a field from a Dropdown menu, I change the fields in the pivot table (so it changes its display, let's say from having "clients" in the row labels to "locations") and then I get the data from a certain row of the pivot table to create a chart.

I know how to create pivot tables through VBA, and I know how to refresh them, but I don't know how to control the fields I wish to show in the pivot table. I am sure there might be a way.

I know that I can add fields with this instruction:

Please Login or Register  to view this content.

So I suppose that "Line of Business" is above "model" in the "Row Labels" box, and the same would be with column labels.

To change the fields, I used the macro recorder and obtained this code:

Please Login or Register  to view this content.

The problem is that I would like to know the fields I have in the pivot table in order to always keep control of what I have in each part. If this isn't possible, maybe I can just empti the rows/columns field and re-fill them with the fields I want.

I would much appreciate if someone could lend me a hand in this issue.

**RagonichaFulva** · 02-23-2012, 01:31 PM

I have seen that maybe it would be better if I worked with a pivot chart (it includes already a Pivot table associated) so I should only control the fields of the chart/pivot table (both are the same entity).

I'll keep investigating this line of work.

**RagonichaFulva** · 02-23-2012, 01:45 PM

Originally Posted by OnErrorGoto0

When you use AddFields, the existing fields should be replaced (unless you specify True for the last argument).

Hello OnErrorGoto0,

That's great then.

What I am thinking is to modify the pivot table associated to my pivot chart, but I wonder how I can apply filters. Which command/method should I use?

I would try to do:

a.) Filters: Program
b.) Rows: Quarter (year)
c.) Columns: Operation type

Quite a simple pivot table... I would use AddFields for b.) and c.), but how can I specify the filter and which element of the filter to use?

Thanks for your assistance.

**RagonichaFulva** · 02-23-2012, 01:53 PM

I found the following code:

Please Login or Register  to view this content.

But it seems a little inefficient.

Is there a way of modifying directly the filter?

**OnErrorGoto0** · 02-23-2012, 04:59 PM

If you are using a report filter, then for one item you would simply assign the CurrentPage property. For multiple items I think you will need the loop.

**RagonichaFulva** · 02-24-2012, 07:20 AM

Originally Posted by OnErrorGoto0

If you are using a report filter, then for one item you would simply assign the CurrentPage property. For multiple items I think you will need the loop.

Hello OnErrorGoto0,

I haven't found the Currentpage property. For the time being I think I'll use the loop option.

Thanks for your feedback.

**OnErrorGoto0** · 02-24-2012, 07:41 AM

CurrentPage is a property of the pivotfield object.

Please Login or Register  to view this content.

for example.

**RagonichaFulva** · 02-24-2012, 10:15 AM

Oh, I see.

Thanks for the info.

Add and Remove the Fields in an existent Pivot Table through VBA

LinkBack

Thread Tools

Rate This Thread

Display

Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Re: Add and Remove the Fields in an existent Pivot Table through VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions