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.

0
Mahurshi
6/2/2010 6:34:28 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
931 Views

Similar Articles

[PageSpeed] 8

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


0
Herbert
6/3/2010 1:30:17 AM
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@yahoo.com> wrote in message 
news:78097121-fc87-44e0-be10-35ca56361e25@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



0
Roger
6/3/2010 6:37:30 AM
Roger,
Downloaded from CloudFiles, but
could not see either error.
Could you please upload your edited file
to MediaFire or somewhere?
Herb

0
Herbert
6/3/2010 3:08:30 PM
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@yahoo.com> wrote in message 
news:33b0bd53-c391-4033-b700-6267beabb2c7@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



0
Roger
6/4/2010 5:42:46 AM
Reply:

Similar Artilces:

How to change multiple folders views in Outlook
When you first create an account within Outlook (currently using 2007 but I'm sure it's the same for other versions) it defaults the folders so that certain columns are visable they all have the viewing pane enabled. Is there a way of changing one folder so that all folders look identicle (Deleted, Drafts, Inbox, Junk E-Mail, Outbox etc etc etc). I'm sure there must be a way but I'd be damned if I can find it...and it's driving me nuts!! Cheers Glenn gloriousglenn@hotmail.com <gloriousglenn@hotmail.com> wrote: > Is there a way of changing one folder so that a...

Current Views drop down menu cannot be rearranged ? (pic included)
http://img219.imageshack.us/img219/1090/currentviewdropdownmenuld4.jpg I've created some custom views but it seems I cannot rearrange the menu so the ones that I use are on top part of the menu. Anybody know any reg hacks for this menu ? It would be nicer if i could just drag the buttons to a toolbar and not require the drop down menu at all. I'm not sure why this isn't a feature since it exist in a bunch of other software that I use. AFAIK, it is not possible to put custom views above the defaults. you can put the view list in the navigation pane and avoid the menu, but that...

Outlook 2003 Journal display or views issue
I use the Journal to not only track my work on office documents, but also to keep a record of how much time I spend doing things that aren't specifically entered as tasks. For some reason, the "entry type", start, end, and contacts fields don't display in the journal view. The columns are there, but field are blank or have the value none. The values were entered when I made the entry, and if I click on a particular entry, the values displayed on the form for that particular entry are correct, but they don't show up under their column in the journal view, and accordingl...

Queue Views
I am using CRM 3.0 and want to add the column "regarding" for the task in our queue. How can I customize this view. ...

Programmatically set "automatically generate microsoft exchange views" ?
Hi all I'm trying to find out how to set "automatically generate microsoft exchange views" on various folders in Outlook 2003 programmatically. I can't find much documentation on this. I don't really care which programming environment I have to do this from. The most promising was CDO folder property CdoPR_GENERATE_EXCHANGE_VIEWS (&H36E9000B) as documented on CDOLIVE. Unfortunately this seems to be no longer supported :-( Can someone point me at some more in depth or up to date documentation, or help me out here ? Many thanks Neal That's the only ...

Mutiple Entity Views
Looking for a way to view fields from multiple entities in a single view. Does anyone know if this is possible? Thank you. Hi AAA, You can filter over multiple entities, but not view across multiple entities. You can't do this using a single call in a .NET application either. Depending on what you want to do, either a .NET application or report might be the answer. Kris Rhodes ---- MS CRM Certified (Installation & Configuration, Customisation) CRM 3.0/VB.NET 1.1 Developer Quantum Business Technology Perth West Australia Blog: http://rhodeskc.wordpress.com This information ...

Modifying Activity Views
We have customived our Activity view using the undocumented back door means ( http://crm/tools/viewEditor/viewManager.aspx?id={00000000-0000-0000-00AA-000010001903} ) and now have been asked to modify associated activity views, like Contact Activities to match. Does anyone have the information on this? Thank you in advance, Scott From an earlier post: Just type for the "My Activities view" the following URL in your Internet > Explorer and replace "crmserver" with the name of your server. > http://crmserver/tools/viewEditor/viewManager.aspx?id={00000000-0000-0000-00...

Is there a way to total in the views?
Hello, Does anyone know if there is there a way to total in the views – so that we can see totals (in opportunities especially)– without having to run reports? Thanks in advance for your help :-) Sinead -- Sinead O''''Shaughnessy afraid not -- Matt Parks MVP - Microsoft CRM "Sinead" <Sinead@discussions.microsoft.com> wrote in message news:C6197892-8B48-4487-BD15-F7249804BA8E@microsoft.com... Hello, Does anyone know if there is there a way to total in the views - so that we can see totals (in opportunities especially)- without having to run reports? T...

Task Views in Navigation Pane
I have created a number of custom task views that reside in the navigation pane. However there appear to be no rhyme or reason as to the order they are in. If I create a new view sometimes it inserts it at the bottom, sometimes in the middle and so on. How do I change the order so they are in some meaningful order for me? I have tried dragging and dropping, I have looked under View>Current View>Define Views but cannot find anything. I've tried searching Google too but unfortunately any search on task views brings back thousands of hits on views in the main pane, but not t...

Exchange 2003 GAL
On our 5.5 system I have an address book view configured to list users by department, so the GAL is something like: Global Address List Dept 1 Dept 2 And so on. I can see that in 2003 I can build a query to show all users in "Dept 1", but I can't see a way of easily achieving the same sort of view other than by manually creating a query for every single department? cheers, Paul -- paul@spamcop.net I don't think there there is anything as intuitive as the views in Exchange 5.5 but the functionality is similar: http://support.microsoft.com/kb/319213/en-us Nue ...

Where are Outlook views stored?
In Outlook 2007 with Exchange 2007 can someone tell me where views are stored, such as the location of the reading pane and whether or not the To-do bar is showing....things like that? I know that categories and inbox rules and formatting rules are stored on the server and will automatically propagate to other machines and I'm thinking that views do too but would like to be sure. Thank you. Yes, views are stored within the mailbox as well. Show/hide the To-Do Bar is stored in the registry; HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Options\ToDoBar -- Robert Sparnaaij [MV...

Uniform views in Outlook XP?
I'd like to set all my mail folders so that they have the same columns, same column widths, and same preview pane height. Is this possible? Thanks. ...

Outlook 97 views
Does anyone know how I can combine elements of the mail view and calendar views? I would love to be able to see my inbox while using the calendar view. Can't be done. The views are hard coded. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jim Diehl <jim.diehl@kenblanchard.com> asked: | Does anyone know how I can combine elements of the mail | view and calendar views? I would love to be able to see my | inbox while using the calendar view. ...

Outlook 2007 Reading Pane Views
I cannot get the reading pane in Outlook 2007 to look and print the same as 2003. I use forms in Excel you send as email, not an attachement but you use the body as the email. This allows the email, in 2003, to print the same way it was set up in Excel. When I use 2007, it does not allow me to send it as other than an attachment. People still using 2003 can send it to me but it does not look like an HTML but more like rich text and loses all the formatting. What am I missing? You may have missed all the articles that came out when Outlook 2007 was released about the c...

how to set different window initial sizes for different views
Hello, In my MDI app I have one document and two different view classes attached: CModelView and CSimulationView. CSimulationView is derived from CFormView. The problem is that CSimulationView's window appears on the screen with a size that is not enough to give room to the controls inside, causing scrollbars to appear. So I am looking for a method to specify my CSimulationView initial size. I assume I cannot override PreCreateWindow() inside my view's frame window, for I have different views with different size needs. Thank you in advance, David You could put them in a sp...

Activity Views 10-12-05
We had a sales rep that quit on us . . . is there a way to create an activity view so we can see all her appointments even if we're not logged in as her? -- Brandon Smith Presentations Direct - "Document Finishing Solutions" http://www.presentationsdirect.com Try Advance Find on Activities object, Assigned To field = sales rep that quit. c360 has better capabilities in tailoring Activites view in v1.2 v3.0 will allow for Actvities customization. Hope that helps. Frank Lee Workopia, Inc. www.workopia.com "Brandon" wrote: > We had a sales rep that quit on...

Different Views
I'm a little confused. How come I get different views depending on where I use CRM. If I use CRM from the server, I can get access to pretty much everything, settings etc, but when I use my desktop and goto http://server:5555 I only get My Workspace and a few other options, no settings. Also if i personalize my workspace, there are more options if I use the server than I do if I use my desktop? M This sounds like your user account doesn't have the roles assigned to it to give you access from the desktop. Each user can have unique access rights depending upon how you have set ...

Saving Queries from Address Book Views
I am working on a test machine with at least 40 custom search queries in Address Book View for the current profile; I would like to get a copy of 40 custom queries and apply to another system without having to re-write it again. Anyway to export queries from Address Book Views? OR Where is the location that keeps these queries? Prefs? Hoping if copy and paste query file on other machine would work. Thanks in advance. ...

MDI App,Worker Thread in CDocument, how to update the views
Hi All, I created a MDI Application with Document/View Architecture. The Data Objects in CDocument are updated from a Worker Thread which I am creating in OnNewDocument. How I can signal from WorkerThread the Document respectively all Views, when Datas are changed? Should work like this: WorkerThread---->(don't know what to do)---->CDocument.UpdateAllViews() I tried already the following code, but it is causing an assertion: static UINT WorkerThread(LPVOID Param) { CMyDoc* pDocument = (CMyDoc*)Param; pDocument->UpdateAllViews(); } BOOL CMyDoc::OnNewDocument() { AfxBegi...

editing custom views
Hello, I have a finance spreadsheet I created for myself to plan my household budget where I have a row for every day of the year. Sine some days nothing happens amongst the days I am planing for I created 2 views. One which displays all the rows and a "part view" which hides the rows with no activity. As the year goes on and I plan further down the sheet I'll want to edit the "part view" so that it includes further rows which will be hidden. How do I do this? When I go to View/Custom View it only gives me the option to Show, Close, Add or Delete a view? Wou...

Custom Views for Subfolders?
Outlook 2002 (Office XP) I have a set up a custom view for my Inbox. I also have many subfolders under my inbox. How to I apply my Inbox custom view to all my Inbox subfolders? I would think there would be an "apply to subfolders" option, but if there is, I can't find it. Thanks. ...

Address Book Views permissions
Is there a way to "Take Ownership" of the ABV? The admin account dissapears when triyng to rebuild lists, and nothing but a user with list permission remains, so I cant administer address lists Thanks Chris ...

Custom Views 05-12-06
I have created a view for Leads that shows up in the view list, but the added columns are not showing up and filters are not working. Is there something else I need to do to get these things to work? I published the customizations and did an iisreset just to play it safe, but it still show up in the view list, but without additional settings. -- Brandon Presentations Direct - "Document Finishing Solutions" http://www.presentationsdirect.com Hi, Which version are you using? 1.2 or 3.0? Gustaf "Brandon" wrote: > I have created a view for Leads that shows up ...

views of icons with names
I'm new to OneNote. Not sure what to say just yet. Trying to like the program, think the potential certainly exists. I'm finding the page view to be less than ideal. I've created a page to hold all the various documents in a project: word, excel, and powerpoint at the moment. I don't care for the view, the way it shows all the documents on the page. The information doesn't mean much to me. The icons are huge, the words are small and few. Almost none of the titles of the documents can be seen unless I right click on them. Hence I can't tell much looking at...

Outlook Views & Filters
We're using Exchange public folders & Outlook with various languages. Our views were setup in English and it seems Outlook views are language specific. Views setup with criteria (filtering views) in English fail to work properly on a German Windows machine. I have not located any posts on this issue. Can anyone provide some help or a solution? Thanks! Fred ...