Change Pivot Data Source automatically when pulling from same page

Hi all,

I'm uploading a sample worksheet- on the tabs scenario A and B, the pivot table is pulling from the same relative data range that is between B3:c13. However, on the scenario B tab it is still pulling from the scenario A tab, even though I want it to be pulling from scenario B.

I do not want to use dynamic ranges because the pivot table is being used to make a pivot chart. How do I get it so that on the scenario B tab the pivot table automatically pulls from the right tab?

I have also have a macro/VBA (I don't know what it is considered, I'm completely clueless with regards to vba/macros, I just copied code from somewhere online) that automatically refreshes all pivot tables (there are other pivot tables being used in the spreadsheet). If I did need to use vba/macro to accomplish what I need to do, where would I copy/paste it in the code I currently have (not sure if the button I have in sample will work properly)?

Sub Refresh()

Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

End Sub

Thanks! If this is confusing, please let me know and I will try to clarify.

Sorry I should attached this version of the spreadsheet and added this. On the scenario B tab, the pivot table on the bottom and the pivot chart on the right is what I want the end result to be. The pivot table on the top and chart on the left is a result of copying the scenario A tab. I want the pivot table to automatically switch to the correct range, like the pivot table being used on the bottom.

Sorry to bump. Thanks in advance for any help.

Hi,

I must admit i dont really understand your problem. In sheet "Scenario B" you have two pivot tables. The bottom one and the chart to the right as a data source: 'Scenario B'!$B$3:$C$13 and the upper pivot table and the chart to the left has a data source 'Scenario A'!$B$3:$C$13. If you make a change in any of your two sources and run the macro it seems to be working fine and the tables and charts are updated. Im not getting what you mean by "I want the pivot table to automatically switch to the correct range, like the pivot table being used on the bottom." Do you want to have two pivot tables with two data sources? What is the "correct range"?

/Masun

Originally Posted by Masun

Hi,

I must admit i dont really understand your problem. In sheet "Scenario B" you have two pivot tables. The bottom one and the chart to the right as a data source: 'Scenario B'!$B$3:$C$13 and the upper pivot table and the chart to the left has a data source 'Scenario A'!$B$3:$C$13. If you make a change in any of your two sources and run the macro it seems to be working fine and the tables and charts are updated. Im not getting what you mean by "I want the pivot table to automatically switch to the correct range, like the pivot table being used on the bottom." Do you want to have two pivot tables with two data sources? What is the "correct range"?

/Masun

Hi Masun,

Thanks for the reply and sorry for the confusion. Let me see if I can clarify.

The pivot table between rows 22 and 24 and the corresponding pivot chart between columns E and M (lets call this pivot table/chart #1) is what I'm trying to manipulate. The pivot table between rows 26 and 28 and the corresponding pivot chart between columns o and V is what I want the end result to look like. I made pivot table/chart #2 by just manually setting the data source to be "ScenarioB!$B$3:$C$13.

However what I'm trying to do is get it so that pivot table #1 would automatically refer to ScenarioB!$B$3:$C$13 instead of ScenarioA!$B$3:$C$13. I created the tab "scenario B" but just duplicating the scenario a sheet. Thus, when I duplicate, the pivot table still incorrectly refers to data on the scenario A tab. I want it to pull from the scenario B tab.

The data range between $B$3:$C$13 automatically pulls from another sheet in my real spreadsheet.

Does this make sense? Let me know and I will try to explain further.

Thanks

Is your question how to change the data source in pt #1 so it refers to ScenarioB!$B$3:$C$13? If so, put the marker in pt#1, PivotTable Tools will be higlighted, under the Options tab you have "Change Data Source"...

Are we getting closer?

Yes we are getting closer haha. I know how to change the pivot data source manually as you described above. What I want to do is have it so that the pivot source changes automatically based on the scenario name, since in my real workbook I have multiple different scenarios. All the scenarios will refer to the same range, but each on their own respective page. Is there a macro that I can use for this?

Okey...maybe i understand.... Many scenarios, each on their "own" sheet. On each sheet its always range B3:D13 that contains the data, correct? Do you wish to have the pt and the chart on its own sheet? And there you need to be able to choose the different scenarios? If so...Havent thought about rearranging your data and put every scenario in a seperate column, after you done this you could place the scenarios in the report filter and there make your choice.

Or do you plan to have one pt and chart in respepective scenario sheet? If so you need to fill the data source in each sheet. Maybe you could use a vlookup or something so if you type "Scenario B" in B2 then the data for Scenario B appears in the range?

Or maybe its something completely diffrent... ;-)

Originally Posted by Masun

Okey...maybe i understand.... Many scenarios, each on their "own" sheet. On each sheet its always range B3:D13 that contains the data, correct? Do you wish to have the pt and the chart on its own sheet? And there you need to be able to choose the different scenarios? If so...Havent thought about rearranging your data and put every scenario in a seperate column, after you done this you could place the scenarios in the report filter and there make your choice.

Or do you plan to have one pt and chart in respepective scenario sheet? If so you need to fill the data source in each sheet. Maybe you could use a vlookup or something so if you type "Scenario B" in B2 then the data for Scenario B appears in the range?

Or maybe its something completely diffrent... ;-)

Yes I want the chart and pivot table to be on each scenario's own respective sheet. I want to use a pivotchart because then the data will update automatically because some scenarios don't have every category (a-f), and thus I don't want blank series on the charts. Pivotcharts automatically filters out blank series.

Hi,

Well, then you, in column C in the "data source area" in each scanario sheet (B3:D13), need some formula that counts the number of instances for a-f for that specific scanario in your other sheet.

If i understand you correctly that is more of an formula/function problem rather then a pivot problem and you probably do best to start a new thread in that section of the forum and provide info about how your other sheet is structured.

/Masun