How to get the PivotTable report info that feeds another PivotTable report.

A pivot table can get it's data from another pivot table as so:
Right click in the pivot table and select PivotTable Wizard
Click the < Back button
Click the next < Back button
Select the "Another PivotTable report or PivotChart report" radio button and
click Next >
Select the pivot table containing the same data and click Finish

What I want to get is the source pivot table for a pivot table.  I guess I'm
looking for the "parent" of the "child" pivot table :-)  How can I do this in
VBA?

Here's the macro recording of the above steps but it didn't help me for
reading the SourceData in other code I tried:
Sub Macro1()
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
"PivotTable4"
End Sub

It appears to me that PivotTableWizard cannot be used to read the above
SourceData property from what I've tried :-(  When I pull the SourceType it
returns xlExternal (2), not xlPivotTable (-4148) as I would expect.  Ideally,
what
I would like to return is the sheet Name and pivot table SourceData value of a
pivot table
(pivot cache?), that way I could find the main pivot table that the other
pivot tables feed from.

TIA,
-- 
   Toby Erkson
   Oregon, USA
   Excel 2003 in Windows XP


0
12/28/2004 10:18:24 PM
excel 39879 articles. 2 followers. Follow

2 Replies
586 Views

Similar Articles

[PageSpeed] 48

When you base a pivot table on another pivot table, it uses the same 
source data as the original table. You can find that source data, e.g.:

Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
   Debug.Print pt.PivotCache.SourceType
   Debug.Print pt.PivotCache.SourceData
Next pt

But, I don't think you can determine which pivot table was used as the 
source.

Toby Erkson wrote:
> A pivot table can get it's data from another pivot table as so:
> Right click in the pivot table and select PivotTable Wizard
> Click the < Back button
> Click the next < Back button
> Select the "Another PivotTable report or PivotChart report" radio button and
> click Next >
> Select the pivot table containing the same data and click Finish
> 
> What I want to get is the source pivot table for a pivot table.  I guess I'm
> looking for the "parent" of the "child" pivot table :-)  How can I do this in
> VBA?
> 
> Here's the macro recording of the above steps but it didn't help me for
> reading the SourceData in other code I tried:
> Sub Macro1()
>     ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
> "PivotTable4"
> End Sub
> 
> It appears to me that PivotTableWizard cannot be used to read the above
> SourceData property from what I've tried :-(  When I pull the SourceType it
> returns xlExternal (2), not xlPivotTable (-4148) as I would expect.  Ideally,
> what
> I would like to return is the sheet Name and pivot table SourceData value of a
> pivot table
> (pivot cache?), that way I could find the main pivot table that the other
> pivot tables feed from.
> 
> TIA,


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/29/2004 12:48:52 AM
Yeah, I got that far (your example) but the source pivot table is what I'm
after.  Rats.  Interesting that with all the flexibility of Excel the info can
be set and used by the application but not by the user.

Thanks Debra,
-- 
   Toby Erkson

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:41D1FEF4.7080403@contexturesXSPAM.com...
> When you base a pivot table on another pivot table, it uses the same
> source data as the original table. You can find that source data, e.g.:
>
> Dim pt As PivotTable
> For Each pt In ActiveSheet.PivotTables
>    Debug.Print pt.PivotCache.SourceType
>    Debug.Print pt.PivotCache.SourceData
> Next pt
>
> But, I don't think you can determine which pivot table was used as the
> source.


0
12/29/2004 3:03:30 PM
Reply:

Similar Artilces:

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

Formula In Crystal Reports
Hi All, I'm just starting to learn Crystal right now but it's all very new to me so I was just curious if anyone knows if you can do formulas in it. I'd like to take a custom CRM field called setup cost on a product and add all the values of this field up in a quote. Ie if there are 3 products all with seperate setup costs I want to add those together. Or will this have to be done a different way? Thanks all Tom Hi, Crystal supports both VB and Crystal Reports formulas. the help files are quite good regarding formulas, and the 9.2.2 CR with enhanced edition has an excel...

Get CtrlID from Msg info at WM_LBUTTONUP
Hi all, We can get CtrlID from msg info at WM_LBUTTONUP event? In Msg info, we can get Handle of Ctrl, but i dn't know how to get CtrlID. any one know, please me know, thanks very much. Used function below, it works int GetCtrlID(HWND hWnd) { CWnd* pChild = CWnd::FromHandle( hWnd ); pChild->FromHandle(hWnd); return pChild->GetDlgCtrlID(); } "James Duy Trinh" <vietdoor@gmail.com> wrote in message news:ORP3U3MLIHA.1168@TK2MSFTNGP02.phx.gbl... > Hi all, > > We can get CtrlID from msg info at WM_LBUTTONUP event? In Msg info, we can > get Handle...

How to add another user's Contacts folder to my Address Book?
Background: Exchange 2003 Server w/ Outlook 2000/2003 clients. I can add any Contacts folders in my mailbox to my Address Book by going into the Properties of the folder and checking "Show this folder as an e-mail Address Book" on the "Outlook Address Book" tab. However, if I have another user's mailbox (to which I have full permission) in my Folder List, I do not have this option in any of their Contacts folders. How do I add another user's Contacts folder, displayed in my Folder List, to my Address Book? TIA, roshan After a bit of research on SlipStick.com...

Turn Off SOP Transfer Log Report
Is there any way to not have the SOP Transfer Log show up? We usually just hit the Cancel button on the Report Destination window but it gets annoying when you transfer 100-150 orders a day. I know you can go into the posting setup to cancel posting reports but I can't find anyway to stop the Transfer Log. -- Scott Scott, No, there isn't a method built in to do this. You'd need a customization using Dexterity or I think you could use VBA to do this as well. patrick mbs developer support -- This posting is provided "AS IS" with no warranties, and confers no ri...

sending to group gets "sending reported error 0x80040610"
Task 'Microsoft Exchange Server - Sending' reported error (0x80040610) : 'Unknown Error 0x80040610' This person sent an email to a distribution group we have set up. It seems that everyone got it but the error came to her like that. Any ideas? Bryce. Does this help? http://support.microsoft.com/kb/885917 -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Bryce" <fukuzz@hotmail.com> wrote in message news:46119f2c$0$503$815e3792@news.qwest.net... > Task 'Microsoft Exchange Server - Sending' reported e...

Creating a list of info in A1 cell in multiple worksheets
How can I create a list of the info/contents in the A1 cell in multiple worksheets? I am trying to create a summary worksheet of the data from 90 worksheets and would like to be able to list the headers in the A1 cell vertically. 'Right click on the summary sheet tab, and paste in this macro. 'Edit where appropriate: Sub CreateSummary() 'Starting row for summary: i = 1 For Each Sheet In ThisWorkbook.Sheets If Sheet.Name <> Me.Name Then 'Control What column to place data in Me.Cells(i, "A").Value = Sheet.Range("A1").Value i = i + 1...

Report width will not shrink
I have a report that was originally designed as legal size, but by eliminating a few columns is now landscape letter size. However I am unable to shrink the width from 12" to 9" - there seems to be something invisible to me in one of the headers, footers or detail sections. I have tried everything to try to select whatever it is that is 12" wide but am unable to find it. I have tried CTRL A for select all, tried highlighting from the top or the side, etc but nothing shows up. It has to be a line or something, but when I preview the report the second page is blank. I ...

Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for top 10 customers, about 400 rows, and another for all other customers, about 4000 rows. Every month I have to update it for any new customer/product combinations. I have optimized the formulas to the best of my abilities and gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My biggest problem now is that every time I need to add a row (or delete one for that matter) it takes about 15 seconds (on the 4k row report, adding to the top 10 customers report takes 3-5 seconds), per row....

Parmeters in Reports
Hello, I have a report generated from a query. The field names are add1, add2 and add3, productname. The data in the fields: add1-add3 are the same. I would like to be prompted when I open the report for the data in fields add1, add2 and add3. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Open the query in design view. Add this as criteria for add1 --- [Enter add1 info] If you want the query to return records that have all if the criteria you enter then put criteria for the rest on the same criteria row. Otherwi...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

How to get looping VBA code to respond to events on worksheet
Hello, I have some code of the form do while not g_Paused bunch of calls ... loop where I'd like the variable g_Paused to be toggled by a button on the worksheet. The only way I can seem to get the code to respond to the button click is to use do while not g_Paused bunch of calls ... ThisWorkbook.Worksheets("Name").Activate DoEvents loop But this is pretty slow and causes the sheet to flicker...is there a better method? PS I'm using buttons from the Control Toolbox Thank you. ...

Restoring another PST?
Hi everyone, I use the MS Outlook Backup tool, and it backs up daily. Early this week I imported the backed-up PST file ( over 1GB ) into my new PC and after a bit of fiddling to show the folders it did open up all the ones I wanted, yet seemed to be an old backup - maybe 12 weeks out of date. I had to use the machine so kept it like this, and have of course received many mails since then. I see another copy of the backup[ PST file and the date says it was created on Feb 19th, which would be right. My ( evantual! ) question is: how do I, in idiot-proof steps, go about restoring this other...

CRM, Reporting Services & Forms Authentication
Our company has an app with forms auth that is integrated with Reporting Services. We have forms auth working successfully with Reporting Services. We'd like to integrate CRM and still use forms auth. Is this possible with CRM? Would anyone be kind enough to point me to any documentation or examples of CRM w/ forms auth & Reporting Services? ...

Can't get automatic updates to work
I get this message: [Error number: 0x8DDD0018] The site cannot continue because one or more of these Windows services is not running: Automatic Updates (allows the site to find, download and install high-priority updates for your computer) Background Intelligent Transfer Service (BITS) (helps updates download more quickly and without problems if the download process is interrupted) Event Log (keeps a record of updating activities to help with troubleshooting, if needed) To make sure these services are running: 1. Click Start, and then click Run. 2. Type servic...

Crystal Report 9.2.2!!!
Hello All, I have install crystal report 9 then I download crystal 9.2.2 and start the setup. first it uninstall crystal report 9 and then it starts installing 9.2.2 and ask the CD KEY I enter Crystal Report 9.0 CD key but it does not accept that KEY. How can I update Crystal 9.0 to Crystal 9.2.2??? Thanks in Advance, Naveed. recheck the key...you only need to install 9.2.2 then enter the 9.0 key -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Naveed" <nrehman@marsonssoft.com> wrote in message news:e%23...

New Entity visit Report and Integration of Participants (MS CRM 3.
Hello Newsgroup, We have created a new Entity called visit reports. We have to add Participants in this new entity. It would nice, if we could have a field like the Participants in the appointment, where you can select the Participants from Contacts, Accounts, and Users. I'll hope , some of you have a idear. Regards Thomas You have to create a new relationship for your custom entity to the types of participants you want to be available. -- Matt Wittemann http://icu-mscrm.blogspot.com "Thomas Haller" wrote: > Hello Newsgroup, > > We have created a new Entity...

Newb question: How did my Form_.... Access Class Objects get crea
I have an access database that originally was created in office 2003 access. I am not in 2007 Access. When I go into the IDE, I can see, under Microsoft Office Access Class Objects, a set of what appears to be modules named: - Form_form abc - Form_form xyz etc Each of these corresponds to an actual form I have created, and includes subroutines like Private Sub RefreshAssetTable() ImportExcel End Sub Private Sub Command4_Click() ImportExcelAssets End Sub by default. Now, my question: How did this class "modules" (and I know that is the wrong term) get created? I &...

Report Columns
I am trying to add columns to my report but am having some difficulty. The report is basically a form letter. It is grouped on the employee’s unique ID. In that group header is the letter, Dear so and so etc. It is the details portion that I am trying to get into two columns. Is there something I am missing on how to do this. I would rather no put a sub report in to accomplish this. Thank you Jason, Well, the subform would be an easy way to go about that. However, I would think you could use the ID group header to display the first part of the letter, the detail section to disp...

weird sum on report
Hi everyone I'm building a report based on several crosstab qry and it seem ok with values. That report has group values. I want, at some point, a line with totals as well as a line with the difference between 2 lines, like expenses to sales. Quite simple, i guess. Result : not only it doesn't subtract in some grouping/levels, as well it doesn't sum the other 4 lines i need. Is this weird or not? (lol) Tks in advance for all your help. Pedro Pedro, I don't see anything wrong from where I sit. Of course, I can't see your crosstabs, data, grouping levels, expressions, ...

Recover missing Z-Report
One of my clerks received an error message during running a Z-Report....she did not note the error so I have no idea what it is...the report did not print yet manager says there is a Z-Report but it is blank...all the transactions are in manager and it says there is a Z-Report but it is totally blank....any way to regenerate this Z-report or make it so it's not blank? ...

HTML report in vb.net
How do I create an html report in vb.net instead of Crystal or ReportViewer? Hello, > How do I create an html report in vb.net instead of Crystal or > ReportViewer? Pretty vague. If the problem is to create html markup you could try the HtmlTextWriter class (http://msdn.microsoft.com/en-us/library/system.web.ui.htmltextwriter(VS.80).aspx to see if it's easier for you. Also most Reporting Packages (including RS, note sure about Crystal but very likely) are able to render a report as HTML markup so you could perhaps still use them. Not sure what is your scena...

Creating a report that tracks stock's life cycle (buy, transfer, s
Greetings and Salutations I purchase ABC stock, and it is held by my broker "Those Brothers". I purchase more ABC stock at different times. These transactions are recorded in Money. I move my investments from "Those Brothers" to the "Quiet Company" and I purchase more ABC stock at various times. The Transfer Out and the additional Buys are all recorded in Money. I decide to sell a lot of ABC stock (spanning multple purchases and multiple investment firms) and use the FIFO method. Somehow Money knows which shares it is selling, I would like to get a report...

how do I set up spread sheet for demographical data to get graph.
I need to set up a spread sheet to be able to get age, sex,service usage numbers, site utilisation, presenting issue e.g., drug and alcohol, region, percentage of use base on population e.g, 80 staff DoHi, Do you want to show these results graphically, or in the form of some sort of output table? Dave url:http://www.ureader.com/msg/10355820.aspx How are you intending to get the data or enter the data into this sheet? How many worksheets or tables will you need? What is the final display options: Examples, Reports, Charts, Pivot = table? And lastly who are the end users, and how do they...