Autofilter in Pivot setzen

Sub Button1_Click() With Worksheets("Pivot").Range("B13").CurrentRegion myarray = Worksheets("Kunden").Range("C3:C20").Value ' ideal wäre, wenn die letzte Zeile (in dem Falle C20) selbst ermittelt werden würde Filterwert = Application.WorksheetFunction.Transpose(myarray) .AutoFilter Field:=1, Criteria1:=Filterwert End With End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub Kunden_setzen() Dim PI As PivotItem Dim Z Dim KdListe As String For Each Z In Worksheets("Kunden").Range("C3:C100") If Z = "" Then Exit For KdListe = KdListe & ";" & Z Next Worksheets("Pivot").PivotTables("PivotTable2").PivotFields("Kunde").ClearAllFilters For Each PI In Worksheets("Pivot").PivotTables("PivotTable2").PivotFields("Kunde"). _ PivotItems PI.Visible = InStr(1, KdListe, ";" & PI.Caption, vbTextCompare) > 0 Next End Sub

Hallo Yal,
super vielen Dank!!!
Ich habe den Code noch ein ewnig angepasst:

Sub CustomerSelect_Click()
On Error GoTo Error_Handler
Dim PI As PivotItem
Dim Z As Variant
Dim k As Long
Dim FirstCustomerCellLine As Single
Dim FirstCustomerCell As String
Dim LastCustomerCell As String
Dim CustomerSheet As Worksheet
Set CustomerSheet = ThisWorkbook.Worksheets("Kunden")
Dim PivotSheet As Worksheet
Set PivotSheet = ThisWorkbook.Worksheets("Pivot")
Dim CustomerList As String
FirstCustomerCell = "D6" ' Kundenspalte muss im Bereich "A...Z" sein
NumberOfCustomers = CustomerSheet.Range(FirstCustomerCell, CustomerSheet.Range( _
FirstCustomerCell).End(xlDown).End(xlDown).End(xlUp)).Rows.Count
FirstCustomerCellLine = CInt(Right(FirstCustomerCell, Len(FirstCustomerCell) - 1))
LastCustomerCell = Left(FirstCustomerCell, 1) & CStr(FirstCustomerCellLine +  _
NumberOfCustomers - 1)
rangeStr = FirstCustomerCell & ":" & LastCustomerCell
For Each Z In CustomerSheet.Range(FirstCustomerCell & ":" & LastCustomerCell)
If Z = "" Then Exit For
CustomerList = CustomerList & ";" & Z
Next
PivotSheet.PivotTables("PivotTable2").PivotFields("Kunde").ClearAllFilters
For Each PI In PivotSheet.PivotTables("PivotTable2").PivotFields("Kunde").PivotItems
PI.Visible = InStr(1, CustomerList, ";" & PI.Caption, vbTextCompare) > 0
Next
Exit_Handler:
On Error Resume Next
Exit Sub
Error_Handler:
strMsg = "Error " & Err.Number & ": " & vbCrLf & Err.Description
MsgBox strMsg, vbCritical
Resume Exit_Handler
End Sub

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

303 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Autofilter in Pivot setzen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

303 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread