hi. Right now, I’m working on a code that allows me to manipulate a pivot table (especially filters). But my problem is that when I run the code it doesn’t visualize the values inside the pivot table and sometimes the filters don’t work either . Does somebody knows a code to manipulate a filter using a listbox????. I’m using the following code:
Code:
Private Sub PRODUCCION_Click()
Application.ScreenUpdating = False
Sheets("PRODUCCION").Select
Dim intASO As Integer
Dim dStart As Date
Dim dEnd As Date
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
On Error Resume Next
dStart = fecha1.Value
dEnd = fecha2.Value
Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Fecha")
pt.ManualUpdate = True
pf.EnableMultiplePageItems = True
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
For Each pi In pf.PivotItems
If pi.Value < dStart Or pi.Value > dEnd Then
pi.Visible = False
End If
Next pi
Application.ScreenUpdating = False
pt.ManualUpdate = False
If CheckBox1.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("B").Visible = False
.PivotItems("A").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If TURNOB.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("A").Visible = False
.PivotItems("B").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If TURNOB.Value = True And CheckBox1.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Turno")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("A").Visible = True
.PivotItems("B").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If CheckBox1.Value = False And TURNOB.Value = False Then
MsgBox ("Selecione al menos un Turno")
Exit Sub
End If
If SALIDA1.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("SALIDA 2").Visible = False
.PivotItems("SALIDA 1").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If SALIDA2.Value = True And SALIDA1.Value = False Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("SALIDA 2").Visible = True
.PivotItems("SALIDA 1").Visible = False
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If SALIDA2.Value = True And SALIDA1.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Salida")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("SALIDA 2").Visible = True
.PivotItems("SALIDA 1").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If SALIDA1.Value = False And SALIDA2.Value = False Then
MsgBox ("Selecione al menos una Saldia")
Exit Sub
End If
If CUADRILLA1.Value = True And CUADRILLA2.Value = False And CUADRILLA3.Value = False Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("2").Visible = False
.PivotItems("1").Visible = True
.PivotItems("3").Visible = False
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If CUADRILLA1.Value = False And CUADRILLA2.Value = True And CUADRILLA3.Value = False Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("2").Visible = True
.PivotItems("1").Visible = False
.PivotItems("3").Visible = False
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If CUADRILLA1.Value = False And CUADRILLA2.Value = False And CUADRILLA3.Value = True Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("2").Visible = False
.PivotItems("1").Visible = False
.PivotItems("3").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If CUADRILLA1.Value = True And CUADRILLA2.Value = True And CUADRILLA3.Value = True Then
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla").EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cuadrilla")
intASO = .AutoSortOrder
.AutoSort xlManual, .SourceName
.PivotItems("2").Visible = True
.PivotItems("1").Visible = True
.PivotItems("3").Visible = True
.AutoSort intASO, .SourceName
.Orientation = xlPageField
End With
End If
If CUADRILLA1.Value = False And CUADRILLA2.Value = False And CUADRILLA3.Value = False Then
MsgBox ("Selecione al menos una Cuadrilla")
Exit Sub
End If
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Unload REPORTE
End Sub