PDA

View Full Version : [SOLVED] Pivot Table Filter Macro Error



kibbles
08-28-2014, 10:12 AM
In my spreadsheet, I have variable data; however, I have a specific set of attributes that I'd want to be filtered upon if/when they do appear in the data run for that week. If they do not appear, I'd like the filter to skip over it and continue to the next.
Unfortunately, I keep getting errors when I try to run my macro. Can anyone help?

I.E.
If "1G1N13S2" isn't in the variable [raw] data, it'd carry me straight to the Visual Basic to debug instead of skipping over it (whether false or true) to continue on to the subsequent ID#.


'PivotTable
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

' BCA_Other Macro
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTIVITY_ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("SUM_$"), "Count of SUM_$", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of SUM_$")
.Caption = "Sum of SUM_$"
.Function = xlSum
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("WK_END_DATE")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("WK_END_DATE")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ANALYSIS_TYPE")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("ANALYSIS_TYPE"). _
CurrentPage = "ACT"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUS_UNIT_GL_FROM")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("BUS_UNIT_GL_FROM"). _
CurrentPage = "AV"

'Activity ID Filter
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTIVITY_ID")
.PivotItems("1G1N13S2").Visible = False
.PivotItems("1G1N2SAN").Visible = False
.PivotItems("1G1N5ACC").Visible = False
.PivotItems("1G1N5SCI").Visible = False
.PivotItems("1G1N5SDV").Visible = False
.PivotItems("1G1N5SMS").Visible = False
.PivotItems("1G1N5SST").Visible = False
.PivotItems("1G1N9BAT").Visible = False
.PivotItems("1G1N9EBT").Visible = False
.PivotItems("1G1N9EPL").Visible = False
.PivotItems("E6T66ZJA").Visible = True
.PivotItems("EMAD0005").Visible = False
.PivotItems("EMADF006").Visible = False
.PivotItems("F8800YN5").Visible = True
.PivotItems("F8800YNU").Visible = True
.PivotItems("F8800YNW").Visible = True
.PivotItems("F8800YPM").Visible = True

End With

Any help would be GREATLY appreciated.

kibbles
08-28-2014, 12:13 PM
So, I've gotten an answer as to how to fix this.

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTIVITY_ID")
.PivotItems("1G1N13S2").Visible = False
.PivotItems("1G1N2SAN").Visible = False
.PivotItems("1G1N5ACC").Visible = False
.PivotItems("1G1N5SCI").Visible = False
.PivotItems("1G1N5SDV").Visible = False
.PivotItems("E6T66ZJA").Visible = True
End With
On Error Goto 0