A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pre-configuring Pivot table "views"



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 07:34 AM posted to microsoft.public.excel.worksheet.functions
Mahurshi Akilla
external usenet poster
 
Posts: 8
Default Pre-configuring Pivot table "views"

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.

  #2  
Old June 3rd, 2010, 02:30 AM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Pre-configuring Pivot table "views"

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


  #3  
Old June 3rd, 2010, 07:37 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Pre-configuring Pivot table "views"

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

"Herbert Seidenberg" wrote in message
...
Excel 2007 PivotTable
Select unique views.
With macro.
http://c0718892.cdn.cloudfiles.racks.../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



  #4  
Old June 3rd, 2010, 04:08 PM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Pre-configuring Pivot table "views"

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

  #5  
Old June 4th, 2010, 06:42 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Pre-configuring Pivot table "views"

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

"Herbert Seidenberg" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.