Macros to change Pivot Field

S

Sandip

Hi,

I have a 6 pivot tables with different views on a single sheet linked
to the same source.

At the top of the page/Sheet, I have given the user the 2 options
using a drop down.

1st Option - Select Month - Jan, Feb, Mar etc....
2nd Option - Select View - Month to date / Year to date

The input provided by the user is "Named" as "Select_Month" and
"Select_View"

I would like to know I can pass the value provided by the year for the
month and Pivotitems matching that month becomes True. Also I would
like to select the same month of the previous year. for eg if
February, 2007 is selected, February, 2006 should also be selected.

The case becomes more complex when the month and YTD option is
selected. For eg if December, 2006 is selected and YTD. The Months
from Jul to Dec, 2006 and 2005 needs to be made True.

Any help on this is appreciated.

With ActiveSheet.PivotTables("Overview1").PivotFields("Fiscal Month")
.PivotItems("July, 2005").Visible = False
.PivotItems("August, 2005").Visible = False
.PivotItems("September, 2005").Visible = False
.PivotItems("October, 2005").Visible = False
.PivotItems("November, 2005").Visible = False
.PivotItems("December, 2005").Visible = False
.PivotItems("January, 2006").Visible = False
.PivotItems("February, 2006").Visible = False
.PivotItems("March, 2006").Visible = False
.PivotItems("April, 2006").Visible = False
.PivotItems("May, 2006").Visible = False
.PivotItems("June, 2006").Visible = False
.PivotItems("July, 2006").Visible = True
.PivotItems("August, 2006").Visible = False
.PivotItems("September, 2006").Visible = False
.PivotItems("October, 2006").Visible = False
.PivotItems("November, 2006").Visible = False
.PivotItems("December, 2006").Visible = False
.PivotItems("January, 2007").Visible = False
.PivotItems("February, 2007").Visible = False
.PivotItems("March, 2007").Visible = False
.PivotItems("April, 2007").Visible = False
.PivotItems("May, 2007").Visible = False
.PivotItems("June, 2007").Visible = False
End With

Regards
Sandip.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I want to create a Year to date button for a pivot table 1
Pivot Table Field 2
VBA and Pivot Table Refresh 1
Mapping strings to integers 6
Creating a common starting point 6
Compare shorter date range against longer date range 2
Month days over a time period 5
Excel file for deliberations on proper entry date into a retirementplan 1

Top