Occasional Error with Macro

P

peachycream

New Member
Joined
Apr 7, 2011
Messages
44
I created a macro in excel 2010 that filters through a daily report to grab the data I am looking for then create several pivot table for that data. The issue I am having is occasionally some of the data I am trying to filter out is not present and it is causing me an error with my pivot tables. I believe I need an IF Then statement but I'm not sure how to write it. The code is listed below. I get an error when either N/A or blank isn't present with my pivot tables. Any assistance would be appreciated.

Code: 
Sub Pending_TC_Nodes()
'
' Pending_TC_Nodes Macro
'
' Keyboard Shortcut: Ctrl+t
'
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$14962").AutoFilter Field:=4, Criteria1:=Array( _
        "5B:FAILED CDV SIK", "5C:FAILED CHSI SIK", "5D:FAILED CDVCHSI SIK", "5E:FAILED TP SIK", _
        "5F:FAILED VIDEO SIK", "95:SAMEDAY GBACK INST", "AF:MDU CLEANUP", "BD:BURY DROP", _
        "BS:UP BOX SWAP", "BZ:COM DTA", "CL:CLI", "DD:DRP DN SRV OK", "EM:EMTA REP BATTERY", "EQ:EQUIPMENT PICKUP", _
        "ER:DAMAGE COMPLAINT", "FB:XDV TP NEW CONNECT", "FD:XDV TP RECONNECT", _
        "FF:XDV/XI NEW CONNECT", "FG:XDV/XI RECONNECT", "FP:XDV/XTV NEW CONNEC", _
        "FQ:XDV/XTV RECONNECT", "GA:XDV/XI CONNECT", "HF:HF PROACTIVE SROTC", _
        "HH:HOT HOUSE", "IA:XDV NEW CONNECT", "IB:XI NEW CONNECT", "IC:XTV NEW CONNECT", "ID:XDV RECONNECT", "IE:XI RECONNECT", _
        "IF:XTV RECONNECT", "IJ:XDV CONNECT", "IK:XI CONNECT", "IL:XTV COS", "IM:XI COS", _
        "IN:XDV COS", "IO:XTV CONNECT", "IW:XTV DISCONNECT", "IX:XI DISCONNECT", _
        "IZ:XDV DISCONNECT", "MC:COAX CUTOVER", "MD:MDU REFERRAL", "MR:MAINTENANCE REPAIR", "MT:MENTOR", _
        "M0:BC-METRO EDI INSTL", "NA:NA/NH TECH TIME", "NH:NODE HEALTH", "NP:NONPAY DISCONNECT", "PL:PED/LOCKBOX", _
        "RB:ROAD BORE", "RD:NONSUB REM DROP", "SA:TECH ASSIST", "SN:SIK RECONNECT", "SV:SERVICIBLITY", _
        "TE:TAP DISC/EQP PKUP", "TP:TAP DISCONNECT", "TL:TRUNK - TP", "TR:TRUNK INSTALL", "TS:TRUNK SURVEY", _
        "U2:TRK O/FIBER INSTL", "UD:RE AUDIT", "WA:WP VIDEO NEW CNCT", "WB:WP VIDEO RECONNECT", "WC:WP CHSI NEW CONN", _
        "WD:WP CHSI DISC", "WE:WP VIDEO CHG OF SR", "WF:WP VIDEO CONNECT", "WG:WP VIDEO DISCO", _
        "WH:WP CHSI CONNECT", "WI:BC VD/HSI NEW CNCT", "WJ:BC VID/CHSI RECONN", _
        "WK:BCDV/CHSI NEW CNCT", "WL:BCDV_CHSI RECONNCT", "WN:Q2Q NEW CNCT", "WP:WDV COS", _
        "WQ:WDV RECONNECT", "WR:WP CHSI RECONNECT", "WS:WDV NEW CONNECT", "WU:WP CHSI COS", "WV:WDV DISCO", _
        "WW:WDV CONNECT", "X4:XH QUAD RECONNECT", "XA:XH CONNECT", "XC:XH COS", "XE:XH CONSULT SRO", "XF:XH 3 DAY CANCEL", _
        "XF:XH 30-DAY EQP RTRN", "YA:BCDV3.0NEWCNCT", "YB:BCDV3.0 COS", "YC:BCDV3.0 CONNECT", "YD:BCDV3.0 RECO", "YF:COMM PRE SURVEY", _
        "YG:COMM PRE SURVEY", "ZE:XI/XTV RECONNECT", "ZP:XI/XTV NEW CONNECT"), Operator _
        :=xlFilterValues
    Range(Rows("2:3"), Rows("2:3").End(xlDown)).Delete Shift:=xlUp
    ActiveSheet.Range("A1:W1679").AutoFilter Field:=4
    Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G1").Value = "MA"
    With Range("G2:G1679")
        .FormulaR1C1 = "=VLOOKUP(RC[-1],'C:\Documents and Settings\TCaine001\My Documents\Vlookup\[Chicago MA.xlsx]Route Criteria'!R2C1:R81C2,2,FALSE)"
        .Value = .Value  'Replace formulas with values
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveSheet.Range("$A$1:$X$1679").AutoFilter Field:=13, Criteria1:="=CN", _
        Operator:=xlOr, Criteria2:="=CP"
    Rows("10:12").Select
    Range("G10").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$X$1303").AutoFilter Field:=13
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.TextToColumns Destination:=Range("R1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(4, 1)), TrailingMinusNumbers:=True
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Node"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Node Leg"
    Cells.Select
    Range("M1").Activate
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Job Search!R1C1:R1048576C25", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Units"), "Count of Units", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable5").PivotCache. _
        CreatePivotTable TableDestination:="Sheet2!R3C4", TableName:="PivotTable6" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Cells(3, 4).Select
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Job Search").Select
    Sheets.Add
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet3!R3C1", TableName:="PivotTable7" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
        "PivotTable7").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet3!R3C4", TableName:="PivotTable8" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(3, 4).Select
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Range("D3").Select
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Scheduled On"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Scheduled On").CurrentPage _
        = Format(Date, "mm/dd/yyyy")
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet3!R10C1", TableName:="PivotTable9" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(10, 1).Select
    ActiveSheet.PivotTables("PivotTable9").AddDataField ActiveSheet.PivotTables( _
        "PivotTable9").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet3!R10C4", TableName:= _
        "PivotTable10", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(10, 4).Select
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Node")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet3!R10C7", TableName:= _
        "PivotTable11", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet3").Select
    Cells(10, 7).Select
    ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
        "PivotTable11").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("MA")
        .PivotItems("6F").Visible = True
        .PivotItems("6G").Visible = True
        .PivotItems("6H").Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("MA")
        .PivotItems("6F").Visible = True
        .PivotItems("6G").Visible = True
        .PivotItems("6H").Visible = True
    End With
    Sheets("Job Search").Select
    Sheets.Add
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable12" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet4!R3C4", TableName:="PivotTable13" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 4).Select
    ActiveSheet.PivotTables("PivotTable13").AddDataField ActiveSheet.PivotTables( _
        "PivotTable13").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable13").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable13").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable13").PivotFields("MA")
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable12").PivotFields("MA")
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Scheduled On"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Scheduled On"). _
        CurrentPage = Format(Date, "mm/dd/yyyy")
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet4!R10C1", TableName:= _
        "PivotTable14", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(10, 1).Select
    ActiveSheet.PivotTables("PivotTable14").AddDataField ActiveSheet.PivotTables( _
        "PivotTable14").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable14").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable14").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Job Search").Select
    Sheets.Add
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable15" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable15").AddDataField ActiveSheet.PivotTables( _
        "PivotTable15").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R3C4", TableName:="PivotTable16" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 4).Select
    ActiveSheet.PivotTables("PivotTable16").AddDataField ActiveSheet.PivotTables( _
        "PivotTable16").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable16").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable16").PivotFields("Scheduled On")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable16").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable15").PivotFields("Scheduled On"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable15").PivotFields("Scheduled On"). _
        CurrentPage = Format(Date, "mm/dd/yyyy")
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R12C1", TableName:= _
        "PivotTable17", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(12, 1).Select
    ActiveSheet.PivotTables("PivotTable17").AddDataField ActiveSheet.PivotTables( _
        "PivotTable17").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable17").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable17").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R12C4", TableName:= _
        "PivotTable18", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(12, 4).Select
    ActiveSheet.PivotTables("PivotTable18").AddDataField ActiveSheet.PivotTables( _
        "PivotTable18").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable18").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable18").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    Sheets("Job Search").Select
    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable6").PivotCache. _
        CreatePivotTable TableDestination:="Sheet5!R12C7", TableName:= _
        "PivotTable19", DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(12, 7).Select
    With ActiveSheet.PivotTables("PivotTable19").PivotFields("MA")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable19").AddDataField ActiveSheet.PivotTables( _
        "PivotTable19").PivotFields("Type"), "Count of Type", xlCount
    With ActiveSheet.PivotTables("PivotTable19").PivotFields("Node")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable19").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("H14").Select
    ActiveSheet.PivotTables("PivotTable19").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable19"). _
        PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("PivotTable18").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("E14").Select
    ActiveSheet.PivotTables("PivotTable18").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable18"). _
        PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("PivotTable17").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("B14").Select
    ActiveSheet.PivotTables("PivotTable17").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable17"). _
        PivotColumnAxis.PivotLines(1), 1
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "112"
    Sheets("Sheet4").Select
    With ActiveSheet.PivotTables("PivotTable14").PivotFields("MA")
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("B12").Select
    ActiveSheet.PivotTables("PivotTable14").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable14"). _
        PivotColumnAxis.PivotLines(1), 1
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Cortland"
    Sheets("Sheet3").Select
    With ActiveSheet.PivotTables("PivotTable11").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("H12").Select
    ActiveSheet.PivotTables("PivotTable11").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable11"). _
        PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("E12").Select
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable10"). _
        PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("PivotTable9").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("B12").Select
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Node").AutoSort _
        xlDescending, "Count of Type", ActiveSheet.PivotTables("PivotTable9"). _
        PivotColumnAxis.PivotLines(1), 1
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "NAV"
    Sheets("Sheet2").Select
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("MA")
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("MA")
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Scheduled On"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Scheduled On").CurrentPage _
        = Format(Date, "mm/dd/yyyy")
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Pending TC"
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 
Sort by date Sort by votes
Try putting a:

On Error Resume Next

up near the top, assumming the error isn't critical to your data.

If it's an If then else statement.

If (logical statement) then (what happens if it's true)
Else (what happens if it's not)
end if
 
Upvote 0
Would the If Then look like this?

If .PivotItems("6M").Visible then =false
Else ignore
end if

Code: 
 With ActiveSheet.PivotTables("PivotTable19").PivotFields("MA")
        .PivotItems("6A").Visible = False
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
 
Upvote 0
If .PivotItems("6M").Visible=True then

With ActiveSheet.PivotTables("PivotTable19").PivotFields("MA") .PivotItems("6A").Visible = False .PivotItems("6F").Visible = False .PivotItems("6G").Visible = False .PivotItems("6H").Visible = False .PivotItems("6L").Visible = False .PivotItems("6M").Visible = False .PivotItems("#N/A").Visible = False .PivotItems("(blank)").Visible = False End With</PRE>

Else (no need to ignore, just leave blank)
end if


This says that IF PivotItem("6M") is visible it will do the with statement, otherwise it wont.
 
Upvote 0
Here's one method
Code: 
With ActiveSheet.PivotTables("PivotTable12").PivotFields("MA")
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
                                On Error Resume Next
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
                              Ir Err.Number<>0 then Err.Clear
    End With
 
Upvote 0
Code: 
With ActiveSheet.PivotTables("PivotTable12").PivotFields("MA")
        .PivotItems("6F").Visible = False
        .PivotItems("6G").Visible = False
        .PivotItems("6H").Visible = False
        .PivotItems("6L").Visible = False
        .PivotItems("6M").Visible = False
        .PivotItems("6N").Visible = False
                                On Error Resume Next
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
                              If Err.Number<> 0 then Err.Clear
                              On Error GoTo 0
    End With

correction....
 
Upvote 0
Thanks for the suggestions. Now I just need to wait for the error to occur again and plug one of these in to see if that rectifies it. I'll update you when that occurs.
 
Upvote 0
I received the error this morning and your suggestion worked perfectly pcc. Thanks to dermie_72 also for the suggestions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
753
RoryA
RoryA
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
524
darmst24
D
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
237
Anthony47
A
S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
188
SCPbrito
S
S
  • Question Question
Macro for
Replies
2
Views
176
Sanderb
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top