Pre-configuring Pivot table "views"
in [Functions]
|
Prev: How to calculate the present value of a security, $5000 @ 7%, 20y.
Next: Loose formula/value (when building graphs)
From: Mahurshi Akilla on 2 Jun 2010 02:34 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.
From: Herbert Seidenberg on 2 Jun 2010 21:30 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
From: Roger Govier on 3 Jun 2010 02:37 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" <herbds7-msxls(a)yahoo.com> wrote in message news:78097121-fc87-44e0-be10-35ca56361e25(a)n37g2000prc.googlegroups.com... > 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
From: Herbert Seidenberg on 3 Jun 2010 11:08 Roger, Downloaded from CloudFiles, but could not see either error. Could you please upload your edited file to MediaFire or somewhere? Herb
From: Roger Govier on 4 Jun 2010 01:42
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" <herbds7-msxls(a)yahoo.com> wrote in message news:33b0bd53-c391-4033-b700-6267beabb2c7(a)z13g2000prh.googlegroups.com... > 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 |