pivot table multiple criteria

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi,

I have a pivot table in which I can select a weeknumber to get the number of
visitors in a pivot table and diagram.
I'm looking for a possibility to select multiple weeknumbers and get the
combined result in a pivot table and diagram. I was thinking of a kind of
selection buttons or something like that.

Any help or suggestion is more than welcome.

Kind regards,
Chris
 
Hi Chris

I am assuming you are working with XL2003 or earlier.
Is your field a Page field?
If so, drag it to the Row area, make your Multiple selections and drag back
to the Page area where the selection will show as "Multiple Items"
 
Hi Roger,

this does indeed work, but the problem is that the pivot table is generated
after selecting a button with a macro. The people using the file are simply
using it without being able to intervene in the pivot table. So what I am
really looking for is a possibility to select different week numbers in the
dropdown list where you can select the week and thus generating a pivot
table based on the selected week numbers.

I will include hereby my macro script, maybe that will help.

Sub Pivot()
'
' Aanmaak draaitabel voor bezoekersaantallen per bezoekerstype
'
' Controle of cel A2 op werkblad Totaal ingevuld is
Sheets("Totaal").Select
If IsEmpty(Range("A2")) Then
Range("A2").Select
MsgBox "Er zijn geen gegevens beschikbaar!", vbInformation,
"OPGELET"
Else
Dim laatsterij As Integer

Sheets("Pivot").Select
Cells.Select
Selection.Clear
Range("A1").Select
Sheets("Totaal").Select
' bepalen wat de laatste rij is
Range("A65536").Select
Selection.End(xlUp).Select
laatsterij = ActiveCell.Row
'MsgBox laatsterij

Rows("1:" & laatsterij).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Totaal!R1C1:R" & laatsterij & "C14").CreatePivotTable
TableDestination:= _
"'Pivot'!R1C1", TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Pivot").Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("WEEK")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("VERKOPER")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").AddDataField
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("CM"), "Count of CM", xlCount
ActiveSheet.PivotTables("PivotTable5").AddDataField
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("P. AANN."), "Count of P. AANN.", xlCount
ActiveSheet.PivotTables("PivotTable5").AddDataField
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("P. ARCH."), "Count of P. ARCH.", xlCount
ActiveSheet.PivotTables("PivotTable5").AddDataField
ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("PART."), "Count of PART.", xlCount
MaakGrafiek
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
End If
End Sub

As I am dutch speaking, there may be some strange words for you in the vba
script.

Regards,
Chris
 
Back
Top