Consulting

Results 1 to 2 of 2

Thread: Pivot Table Filter Macro Error

  1. #1
    VBAX Newbie
    Joined
    Aug 2014
    Posts
    2
    Location

    Pivot Table Filter Macro Error

    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.

  2. #2
    VBAX Newbie
    Joined
    Aug 2014
    Posts
    2
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •