Pivot table with data validation

[vinwin06]

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

 

[Jerry Sullivan]

Thank you. Sorry have forgotten to ask two other important questions:

1. Is the Pivot Field that will be filtered based on the drop down list named: "Location"

2. In what area of the PivotTable Report is the "Location" field placed (Report Filters, Row labels, or Column labels)?

Also, stepping back to the big picture, have you tried using Slicers instead of a drop down Data Validation list?
Through the use of slicers, you would not require any VBA.

 

[excel uncertain]

1. Is the Pivot Field that will be filtered based on the drop down list named: "Location", --Actually named "Location - Name"

2. In what area of the PivotTable Report is the "Location" field placed (Report Filters, Row labels, or Column labels)? --"Location - Name" is in the Report Filter

Also, stepping back to the big picture, have you tried using Slicers instead of a drop down Data Validation list?
Through the use of slicers, you would not require any VBA.

This is a good question. As you can see with my handle "excel uncertain", I did not. I briefly read about it but was tunnel visioned by what I currently set up. I will read more about it. Thank you.

 

[Jerry Sullivan]

Yes, do try out slicers. You and the users of your dashboard may find that you prefer the look and feel of those better than a data validation drop down.

Here are some instructions to set up the VBA to support a drop down.

There are two parts:
A worksheet event function that will trigger the code whenever you change your Cell that has the Data Validation list,
and a supporting subroutine.

1. Right Click on the Tab of your Sheet that has the data validation cell
2. Select View Code...
3. Copy and Paste the Sub Worksheet_Change Code below into the Sheet Code module
4. If needed, edit the Names in Blue Font to match your Master PivotTable and Field Name

Private Sub Worksheet_Change(ByVal Target As Range)
 '--if changed cell has specfied address, set the currentPage
 '  of pivotTable to match value of changed cell.
 
 Dim pvt As PivotTable
 Dim sErrMsg As String
  
 '--customize these values to match your names and cell address
 Const sDV_Address As String = "[COLOR="#0000CD"]$B$2[/COLOR]" 'Cell with DV dropdown to select filter item.
 Const sField As String = "[COLOR="#0000CD"]Location - Name[/COLOR]"  'Report Filter Field Name

 On Error GoTo ErrProc
 Set pvt = Sheets("[COLOR="#0000CD"]Worksheet Voluntary[/COLOR]").PivotTables("[COLOR="#0000CD"]PivotTable2[/COLOR]")
 
 If Intersect(Target, Me.Range(sDV_Address)) Is Nothing Or _
    Target.Cells.Count > 1 Then GoTo ExitProc

 Application.EnableEvents = False
 
 Call SetCurrentPage( _
   pvf:=pvt.PivotFields(sField), _
   sCurrentPage:=Target.Value)
   
 Application.EnableEvents = True
   
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

Then insert a Sub procedure into a Standard Code Module
5. Press the keys ALT + I to activate the Insert menu
6. Press M to insert a Standard Module
7. Copy and Paste the Sub below into the Standard module

Public Sub SetCurrentPage(pvf As PivotField, _
        sCurrentPage As String)

'--sets Report Filter field's CurrentPage to specified value
'  provides error message and clears all filters if value not found.

 Dim sErrMsg As String
 
 On Error GoTo ErrProc
 With pvf
   .ClearAllFilters
   .CurrentPage = sCurrentPage
 End With
 
ExitProc:
 On Error Resume Next
 If Len(sErrMsg) Then MsgBox sErrMsg
 
 Exit Sub

ErrProc:
 Select Case Err.Description
   '--most common error is when item doesn't exist
   Case "Application-defined or object-defined error"
      sErrMsg = "Item: " & sCurrentPage & " not found in " & pvf.Parent.Name
   Case Else
      sErrMsg = Err.Number & ": " & Err.Description
 End Select

 Resume ExitProc
End Sub

Once you've completed that, your PivotTable and PivotChart should update each time the value of the cell with the dropdown list is changed.

 

[excel uncertain]

Works perfectly - thank you very much. Only needed to change it to a macro enabled. Great work!!! Again thank you.

 

[excel uncertain]

I've added another PivotTable sheet "ELT PivotTable" and PivotChart on same sheet as another Data Validation List, Cell B2 on sheet "ELT Turnover". Is there a way to make this work in the way as the first VBA? I tried creating the same code with variation in names, added it to the code in ELT Turnover but that added a Module2 and copied the same info to Module1.

 

[Jerry Sullivan]

You should be able to just copy the Worksheet_Change code to the Sheet Code module of the sheet with the DV cell ("ELT Turnover"), then edit the names for the PivotTable sheet, the PivotTable name and the PivotTable field.

There's no need to make a second copy of Sub SetCurrentPage. That Sub can just exist in Module1 and be called by multiple Worksheet_Change procedures.

 

[excel uncertain]

I've tried that and I get this panel: 1004: Unable to get the PivotTables property of the Worksheet class

Private Sub Worksheet_Change(ByVal Target As Range)
'--if changed cell has specfied address, set the currentPage
' of pivotTable to match value of changed cell.

Dim pvt As PivotTable
Dim sErrMsg As String

'--customize these values to match your names and cell address
Const sDV_Address As String = "$B$2" 'Cell with DV dropdown to select filter item.
Const sField As String = "ELT" 'Report Filter Field Name

On Error GoTo ErrProc
Set pvt = Sheets("ELT Turnover").PivotTables("PivotTable1")

If Intersect(Target, Me.Range(sDV_Address)) Is Nothing Or _
Target.Cells.Count > 1 Then GoTo ExitProc

Application.EnableEvents = False

Call SetCurrentPage( _
pvf:=pvt.PivotFields(sField), _
sCurrentPage:=Target.Value)

Application.EnableEvents = True

ExitProc:
On Error Resume Next
If Len(sErrMsg) Then MsgBox sErrMsg

Exit Sub

 

[Jerry Sullivan]

One possibility is a slight mismatch between the names in the code and the actual names. For example if the actual field name is " ELT" (with a leading space character), that would cause that error.

Double-check the names and if you can't find a mismatch, then use the macro recorder to record the process of your manually changing that filter. Post that code and it will help in finding the problem.

 

[excel uncertain]

Once again thank you. I started over with a new Chart, moving the Pivot table and resetting the values. Works great.

 

[baldmosher]

Hi, bringing up an old thread, is it possible to have Target.Value (in the Worksheet_Change event) pick up multiple items to be selected in the pivot table(s)?

For example, rather than my data validation dropdown being 1 item to select in all pivots, it would be a reference to another list of multiple items relating to that first item. So I could select "all Ice Cream" in a validation list of desserts, and I would have a list of "all Ice Creams" on another sheet, containing "Vanilla", "Chocolate", "Strawberry". The macro would need to select those three individual ice creams in the "Desserts" PivotField.

I've got my code and lookups ready, all I need to know now is whether this will even work... and if it will work, what do I need to pass to the subroutine, instead of Target.Value?
- I tried selecting multiple cells as Target, no luck, it seems to pass and recognise Target as an array, recognises LBound = 1 and UBound = 3, but doesn't seem to recognise the items in the array?
- I tried changing from Target.Value to Target, again no luck
- I tried using VisibleItemsList, no luck

I should add that playing with Arrays built from sheets is something I've been trying to do for some time and so far I've failed to understand it completely!