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

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

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

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