Changing the source data for pivots

[Vikash200418]

Dear All,

First of all let me thank you the entire forum for such an wonderful effort to help people.

I have a file where there are two tabs say name is "Actual Database" and "Forecast Database" and I have created number of different pivots using the data from the tab "Actual Database". I have created both the tabs using table options and named the tables as 'Actual' and 'Forecast'.

But What I am looking for is I will have a drop down with options "Actual" and "Forecast" which will be selected by the user. In case the user selects 'Actual', the source data for all the pivots should get from the 'Actual database' tab and in case the user selects 'Forecast' the source data for all the pivots should get from the 'Actual database' tab.

Can anyone help me sort out the above issue which I am unable to sort out.

Many Thanks in Advance

Vikash

 

[Vikash200418]

Just to let you all know I have been using Excel 2013. Thanks.

 

[Jerry Sullivan]

Hi Vikash,

One way to do this would be to have the VBA code directly change the value of the SourceData of each listed PivotTable each time that the dropdown is changed.

A simpler approach is to use a defined name (let's say "MyDataTable") as the SourceData for each of these Pivots. Then use VBA to redefine what "MyDataTable" RefersTo whenever the dropdown is changed.

To set that up..
Step 1: manually create a Named Range
Name: MyDataTable
Scope: Workbook
Refers to: Actual[#All]

Step 2: Remap each Pivot to have MyDataTable as its source.

Step 3: Use the code below to remap the range that MyDataTable refers to when the dropdown is changed.

Paste this code into the Sheet Code Module of the sheet that has the Data Validation dropdown cell.
To access that module, right-click on that Sheet's Tab > View Code

Edit the names and addresses in blue font to match your setup.

Private Sub Worksheet_Change(ByVal Target As Range)
 
 Dim pvc As PivotCache
 Dim sNewTableName As String, sNewRefersTo As String, sErrMsg As String
 
 '--modify to match address of data validation dropdown cell
 If Target.Address <> "[COLOR="#0000CD"]$B$2[/COLOR]" Then GoTo ExitProc
 
 sNewTableName = Target.Text
 
 On Error GoTo ErrProc
 
 With ActiveWorkbook.Names("[COLOR="#0000CD"]MyDataTable[/COLOR]")
   sNewRefersTo = "=" & sNewTableName & "[#All]"
   If .RefersTo <> sNewRefersTo Then
      Application.EnableEvents = False
      .RefersTo = sNewRefersTo
      
      '--Refresh all pivot caches using this source data
      For Each pvc In ActiveWorkbook.PivotCaches
         If pvc.SourceData = "[COLOR="#0000CD"]MyDataTable[/COLOR]" Then
            pvc.Refresh
         End If
      Next pvc
      Application.EnableEvents = True
   End If
 End With
      
ExitProc:
 On Error Resume Next
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 Application.EnableEvents = True
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc

End Sub

 

[Vikash200418]

Hi Jerry,

Thank you for your reply.

Just few clarification I could not understand what exactly have to be done in Step 1, how to proceed with Step 1

Step 1: manually create a Named Range
Name: MyDataTable
Scope: Workbook
Refers to: Actual[#All]


Thanks you in Advance.

Vikash

 

[Vikash200418]

Can anybody help, its very urgent.

 

[Jerry Sullivan]

Vikash, To create a Named Range:

From the Ribbon > Formulas tab > Defined Names group > click on Defined Name button

Complete the New Name dialog:
Name: MyDataTable
Scope: Workbook
Refers to: =Actual[#All]

 

[Vikash200418]

Thank you so much Jerry