Pre-configuring Pivot table "views"

[Mahurshi Akilla]

I need to create several different pivot tables from the same set of
data. However, I need to keep switching between the different tables
and it quickly becomes a cumbersome process selecting different rows,
columns, and sigma values, each time I want to look at a certain pivot
table.

I know I can create different pivot tables and put them in seperate
sheets instead of dynamically updating the same pivot table to get the
data I want. I was wondering if there was a way I could preconfigure
all these tables and simply "select" one of the "views" so I wouldn't
have to keep selecting the rows, columns and sigma values all the time
for each pivot table "view." Is it possible to pre-configure these
pivot table "views"? I am using Excel 2007.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Select unique views.
With macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_02_10.xlsm
Pdf preview:
http://www.mediafire.com/file/nmtnmzoyzrd/06_02_10.pdf

 

[Roger Govier]

Hi Herbert
Very nice coding (as always)
However, I cannot get it to run properly.
In Sub FieldSelect, the program chokes on the line
.PivotItems(i).Visible = True
with "Error 1004 - Unable to set the visible Property of the pivot item
Class"
with the first and last item where they are Dates with the values
"<1/10/2007" or ">12/23/2008"
in both the Filed DDate and Years.

I can get it to run if I include an On error Resume Next
On Error Resume Next
With Sheets("PivotTable")
With .PivotTables("PivotTable1").PivotFields(fld)

and On error Goto 0 at the end of the Sub.

With Sub PageItems I get an "Error 13 Type mismatch", on the line
If .PivotItems(i).Visible Then

which I can overcome if I change the range of N to start 1 later and finish
1 earlier e.g. ignoring those 2 items in Ddate which have the ">" and "<"
values in their title.

For i = 2 To n - 1
If .PivotItems(i).Visible Then
s = s & ", " & .PivotItems(i).Name


I get the same result in both XL2007 and XL2010 and I cannot understand why
it is doing this.
I guess it didn't happen with you.

--

Regards
Roger Govier

Excel 2007 PivotTable
Select unique views.
With macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_02_10.xlsm
Pdf preview:
http://www.mediafire.com/file/nmtnmzoyzrd/06_02_10.pdf



__________ Information from ESET Smart Security, version of virus
signature database 5167 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5167 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Herbert Seidenberg]

Roger,
Downloaded from CloudFiles, but
could not see either error.
Could you please upload your edited file
to MediaFire or somewhere?
Herb

 

[Roger Govier]

Hi Herb

I tried downloading the original file again, and still had the same
problems.
That was without making any changes whatsoever.
The changes I made (as per my previous posting) were just to get it to run.
I have uploaded it as #2 version at
http://www.mediafire.com/download.php?wzaximljzje

We failed to make direct contact with each other once before.
my email address is
roger at technology4u dot co dot uk
Change the at and dot and remove spaces to make valid email address.
--

Regards
Roger Govier

Roger,
Downloaded from CloudFiles, but
could not see either error.
Could you please upload your edited file
to MediaFire or somewhere?
Herb


__________ Information from ESET Smart Security, version of virus
signature database 5170 (20100603) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5170 (20100603) __________

The message was checked by ESET Smart Security.

http://www.eset.com