SourceData pointing to another PivotTable

**ivanov_nv** · 07-01-2010, 03:00 PM

I have multiple pivot tables in one worksheet, however when I set the SourceData for each one of them to a range the size of the file grows drastically. What I tried is setting the SourceData on one of them to the range and then in a loop set the SourceData for each one of the other PivotTables to point to the first one, but I keep getting an error. Any ideas what I am doing wrong?

Please Login or Register  to view this content.

However this is giving me an error on the follolwing line

pt.PivotTableWizard SourceType:=xlPivotTable, SourceData:="MainPivot"

Run-time error '1004':
Application-defined or object-defined error

**ivanov_nv** · 07-02-2010, 08:03 AM

I am sorry about this. I have modified it and placed the [code] tags around it.

**romperstomper** · 07-02-2010, 08:07 AM

Change each pivottable's cacheindex to be the cacheindex of the main pivot table.

**ivanov_nv** · 07-02-2010, 08:10 AM

I have tried quite a few things, but nothing I tried, worked. Would you mind providing me with a code suggestion. Thank you for your assistance.

**romperstomper** · 07-02-2010, 08:16 AM

Please Login or Register  to view this content.

**ivanov_nv** · 07-02-2010, 08:22 AM

romperstomper, you are a genius. Thank you from the bottom of my heart.

**romperstomper** · 07-02-2010, 08:33 AM

I know.

You're welcome.

**ivanov_nv** · 07-02-2010, 08:45 AM

romperstomper, while I have you and since you have been so helpful, can you point me in the right direction for the following:

I have a List in one of my WorkSheets called DivisionList. It lists various names of Divisions in a Company. I am displaying this list on a page called Home and allowing the user to select a Division for which the report and graphs will be created. Based on the selection from Home, in the Data worksheet (this is where my data is), I have created a column that refers to client's selection from the list and if a name of a division is selected, it calculates the value of the cell in the column weather the data should be used: Yes or No. The formula used in tis column is:

Please Login or Register  to view this content.

Home!$D$7 is where the client has selected the individual name
AE2 also has the names of the divisions in the data sheet. making sure that client's selection from the list matches this.
Total Company is a custom item in the list that will prepare reports for all Divisions.

Is there code, that would allow the client to select multiple Divisions and based on this multiple selection, the calculation in my cell to still work?