Change Pivot Data Source automatically when pulling from same page

[bglick]

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.