Passing Criteria to a Pivot Table from combo boxes
 

Hi,

I have done something similar in the past but not quite the same as this.

I have a worksheet which has 8 different criteria I need to pivot on.

I have a front sheet with 8 combo boxes on it (for the end user to select
criteria) and need to pass these values to a pivot table, which in turn
outputs various graphs etc from the data returned by the pivot table. The
pivot table and graphs work fine but I don't know how to pass the combobox
values to the pivot table.

Any advice or pointers would be greatly appreciated.

Many thanks

Passing Criteria to a Pivot Table from combo boxes
 

In one of my models I use a ListBox to do this; pretty much the same thing.
My data is in sheet named 'Summary' in Range A8:A31. I have this in C5:
=INDEX($A$8:$A$59,$B$7)

The Input Range on my ListBox is: $A$8:$A$59
The Cell Link is: B7 (notice the B7 in the Index function above).

Finally, I have some code like this (yours WILL be a bit different depending
on sheet names and cell references, but pretty much the same):

With
ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With


Here's most of the code; again yours WILL be a bit different:
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select


With
ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Summary").Range ("C5").Value),
"Count of ", xlCount

With
ActiveSheet.PivotTables("PivotTable1").PivotFields (Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Steve Muir" wrote:

Hi,

I have done something similar in the past but not quite the same as this.

I have a worksheet which has 8 different criteria I need to pivot on.

I have a front sheet with 8 combo boxes on it (for the end user to select
criteria) and need to pass these values to a pivot table, which in turn
outputs various graphs etc from the data returned by the pivot table. The
pivot table and graphs work fine but I don't know how to pass the combobox
values to the pivot table.

Any advice or pointers would be greatly appreciated.

Many thanks

Passing Criteria to a Pivot Table from combo boxes
 

Here's a sample of some code that should put you in the right direction:
'/---------------------------------------------------------
Public Sub aa_Test()
Dim iCount As Integer, i As Integer

On Error GoTo Exit_Sub

'identify the pivot table and field name in combo box
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
'get # of items in the pivotfield
iCount = .PivotItems.Count
're-set all items to visible
For i = 1 To iCount
.PivotItems(i).Visible = True
Next i
'turn off all items EXCEPT the one in the combobox
For i = 1 To iCount
If .PivotItems(i).Value < _
Worksheets("Sheet1").ComboBox1.Value Then
.PivotItems(i).Visible = False
End If
Next i

End With
Exit_Sub:
End Sub
'/---------------------------------------------------------

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Steve Muir" wrote:

Hi,

I have done something similar in the past but not quite the same as this.

I have a worksheet which has 8 different criteria I need to pivot on.

I have a front sheet with 8 combo boxes on it (for the end user to select
criteria) and need to pass these values to a pivot table, which in turn
outputs various graphs etc from the data returned by the pivot table. The
pivot table and graphs work fine but I don't know how to pass the combobox
values to the pivot table.

Any advice or pointers would be greatly appreciated.

Many thanks

Passing Criteria to a Pivot Table from combo boxes
 

Got this working now, many thanks all

"Steve Muir" wrote:

Hi,

I have done something similar in the past but not quite the same as this.

I have a worksheet which has 8 different criteria I need to pivot on.

I have a front sheet with 8 combo boxes on it (for the end user to select
criteria) and need to pass these values to a pivot table, which in turn
outputs various graphs etc from the data returned by the pivot table. The
pivot table and graphs work fine but I don't know how to pass the combobox
values to the pivot table.

Any advice or pointers would be greatly appreciated.

Many thanks