Pivot table with data validation

V

vinwin06

New Member
Joined
Dec 9, 2009
Messages
30
HI,

I required vba codings for pivot table but the value should be choose from the data validation i have created.

For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.

Regards,

VInwin
 
Sort by date Sort by votes
Hi again Jorgen,

You could try this modification (untested).

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
    Dim tField As String, tDV_Address As String
    Dim uField As String, uDV_Address As String
    Dim [COLOR="#0000CD"][B]vPivots As Variant[/B][/COLOR], i As Long

    
    sField = "From_Name" 'Field Name
    sDV_Address = "$A$2" 'Cell with DV dropdown to select filter item.
    tField = "To_Name" 'Field Name
    tDV_Address = "$B$2" 'Cell with DV dropdown to select filter item.
    uField = "STC" 'Field Name
    uDV_Address = "$C$2" 'Cell with DV dropdown to select filter item.

     
    If Intersect(Target, Range(sDV_Address & "," & tDV_Address & "," _
        & uDV_Address)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

     
    On Error GoTo CleanUp
    Application.EnableEvents = False

               
[B][COLOR="#0000CD"]    vPivots = Array( _
        Sheets("Sheet1").PivotTables("PivotTable1"), _
        Sheets("Sheet1").PivotTables("PivotTable2"), _
        Sheets("Sheet2").PivotTables("PivotTableA"), _
        Sheets("Sheet2").PivotTables("PivotTableB"), _
        '....
        '.... your list continues
        '....
        Sheets("Sheet5").PivotTables("PivotTable11"), _
        Sheets("Sheet5").PivotTables("PivotTable22") _
   ) 'end Array[/COLOR]   [/B]

    For i = LBound(vPT_Names) To UBound(vPT_Names)
        With vPivots(i)
            Call Filter_PivotField( _
                pvtField:=.PivotFields(sField), _
                vItems:=Range(sDV_Address).Value)
            Call Filter_PivotField( _
                pvtField:=.PivotFields(tField), _
                vItems:=Range(tDV_Address).Value)
            Call Filter_PivotField( _
                pvtField:=.PivotFields(uField), _
                vItems:=Range(uDV_Address).Value)
        End With
    Next i
     
CleanUp:
    Application.EnableEvents = True
End Sub

If your list of Pivots is going to change, you might find it more convenient to read the list of Pivots from a Table in your sheet instead of hard-coding the names into your VBA code.

This thread shows an example....
http://www.mrexcel.com/forum/excel-...orksheet-change-tweak-needed.html#post3202589
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
JS411 said:
Hi again Jerry,

Hmm. I get complie error Arrey part...

Code: 
[B][COLOR=#0000cd] [/COLOR][/B][COLOR=#0000cd]Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
    Dim tField As String, tDV_Address As String
    Dim uField As String, uDV_Address As String
    Dim vPivots As Variant, i As Long
   
   
    sField = "FROM" 'Field Name
    sDV_Address = "$A$2" 'Cell with DV dropdown to select filter item.
    tField = "TO" 'Field Name
    tDV_Address = "$B$2" 'Cell with DV dropdown to select filter item.
    uField = "YEAR" 'Field Name
    uDV_Address = "$C$2" 'Cell with DV dropdown to select filter item.
    
 On Error GoTo CleanUp
    Application.EnableEvents = False
               
[B]    vPivots = Array( _
        Sheets("Sheet1").PivotTables("PivotTable1"), _
        Sheets("Sheet1").PivotTables("PivotTable2"), _
        Sheets("Sheet2").PivotTables("PivotTable3"), _
        Sheets("Sheet2").PivotTables("PivotTable4"), _
) 'end Array

[/B]
     For i = LBound(vPT_Names) To UBound(vPT_Names)
        With vPivots(i)
     Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable1").PivotFields(sField), _
            vItems:=Range(sDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable1").PivotFields(uField), _
           vItems:=Range(uDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable1").PivotFields(tField), _
           vItems:=Range(tDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable2").PivotFields(sField), _
           vItems:=Range(tDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable2").PivotFields(uField), _
           vItems:=Range(uDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet1.PivotTables("PivotTable2").PivotFields(tField), _
           vItems:=Range(sDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable3").PivotFields(sField), _
            vItems:=Range(sDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable3").PivotFields(uField), _
           vItems:=Range(uDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable3").PivotFields(tField), _
           vItems:=Range(tDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable4").PivotFields(sField), _
           vItems:=Range(tDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable4").PivotFields(uField), _
           vItems:=Range(uDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=Sheet2.PivotTables("PivotTable4").PivotFields(tField), _
           vItems:=Range(sDV_Address).Value)
    
    End With
       
CleanUp:
    Application.EnableEvents = True
End Sub



[/COLOR][B][COLOR=#0000cd][/COLOR][/B]
Click to expand...
 
Upvote 0
You have an unneeded comma at the end of the list of array items.
Delete the comma shown in red font below.

Code: 
   vPivots = Array( _
        Sheets("Sheet1").PivotTables("PivotTable1"), _
        Sheets("Sheet1").PivotTables("PivotTable2"), _
        Sheets("Sheet2").PivotTables("PivotTable3"), _
        Sheets("Sheet2").PivotTables("PivotTable4")[COLOR="#FF0000"][B][SIZE=3],[/SIZE][/B][/COLOR] _
   ) 'end Array
 
Upvote 0
Hi all,

Sorry for bumping an old thread but searching for a solution has brought me to here where I have a similar problem.

I have a DV list for management use to filter a hidden pivot table based on the month selected. ((All), July 2014 - June 2014)
I've tried various solutions but the code in the first response from Jerry is closest (I've now updated mine to this exact code) however I still have some issues.

Is there something specific I need to do with a DATE in a DV list to have it update the pivot? When I select any of the months in the list, I receive the "None of the filter lists" pop up... only (All) works correctly. The format is identical to the data source feeding the pivot (1/07/2014 formatted to mmm-yyyy but changing to Number didn't have any impact).

I am using Excel 2010.
Pivot table info: sheet "Detail Pivot", name "DetailPivot", field "WorkMonth" and it is on the same sheet as the drop down list.


Any suggestions?
 
Upvote 0
MrExcel MVP,

I am new to this forum. I do not have any experience with VBA. I've been searching for a solution similar to this on.

I have a Data Validation List (country names, France, Germany, states in US etc...) that that updates some statistics. It also updates a line chart that uses SUMPRODUCT. I've added a PIVOT CHART and need a solution to have that same Data Validation List automatically update the PIVOT CHART.

My Data Validation List is on a sheet name "Turnover" CELL B2. The PIVOT TABLE is on "Worksheet Voluntary". The PIVOT CHART is located on the "Turnover" sheet.

I've never used VBA. I've looked at VIEW SOURCE but do not know what steps to take to make this work. Would it be too much trouble to help me out with this. The code, where to go and what to do step by step?
 
Upvote 0
excel uncertain said:
I have a Data Validation List (country names, France, Germany, states in US etc...) that that updates some statistics. It also updates a line chart that uses SUMPRODUCT. I've added a PIVOT CHART and need a solution to have that same Data Validation List automatically update the PIVOT CHART.

My Data Validation List is on a sheet name "Turnover" CELL B2. The PIVOT TABLE is on "Worksheet Voluntary". The PIVOT CHART is located on the "Turnover" sheet.

I've never used VBA. I've looked at VIEW SOURCE but do not know what steps to take to make this work. Would it be too much trouble to help me out with this. The code, where to go and what to do step by step?
Click to expand...

Welcome to MrExcel,

I can help with some step by step instructions. First please clarify:

1. What version of Excel are you using?

2. Is your sheet named: "Worksheet Voluntary" or just "Voluntary"?

3. Is there only one PivotTable on that sheet? If there's more than one, what is the name of the PivotTable (listed in the ribbon if you have Excel 2007 or later)?

4. Are the PivotTable and the PivotChart linked (using the same PivotCache data)? They are if you filter the PivotTable and that is also applied to the PivotChart.
 
Upvote 0
Perfect thank you.

1. What version of Excel are you using? 2010

2. Is your sheet named: "Worksheet Voluntary" or just "Voluntary"? Sheet name with PIVOT Table is "Worksheet Voluntary"

3. Is there only one PivotTable on that sheet? Only 1. PivotTable Name is PivotTable2

4. Are the PivotTable and the PivotChart linked (using the same PivotCache data)? They are if you filter the PivotTable and that is also applied to the PivotChart. Yes they are. The PivotTable is filtered with Location and Termination Reason. The Pivot Chart located on sheet "Turnover" has those available for selection.

As I select the Location from the Data Validation List I would like it to also update the Pivot Table/Chart Location with the same selection.

Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
why does the rows remove from pivot table after auto refresh
Replies
1
Views
110
shift-del
shift-del
D
  • Question
Data Validation Drop Down Question?
Replies
6
Views
131
davinroach
D
FrumpyJones
  • Solved
Table not showing up in data validation source
Replies
9
Views
133
Georgiboy
Georgiboy
B
  • Question
Update Pivot Tables with Refresh
Replies
1
Views
249
shift-del
shift-del
W
  • Question
Pivot table Refresh from a change to data validation list change
Replies
2
Views
233
wisemank
W

Forum statistics

Threads
1,216,231
Messages
6,129,631
Members
449,522
Latest member
natalia188

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top