Run-time error '5' (excel 2010)

[peachycream]

I recorded a macro yesterday that worked fine but today is producing errors that I can't resolve. My intention was to use my macro to create a pivot table report from a data dump report that is produced daily. The macro works properly until it becomes time to create the pivot table and then I get a run-time error '5'. This is the code up until the time of the error (which is in red). Any help would be appreciated.

Sub Pending_TC()
'
' Pending_TC Macro
'
' Keyboard Shortcut: Ctrl+n
'
 
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$20000").AutoFilter Field:=4, Criteria1:=Array( _
        "5C:FAILED CHSI SIK", "5F:FAILED VIDEO SIK", "95:SAMEDAY GBACK INST", _
        "BD:BURY DROP", "CL:CLI", "DD:DRP DN SRV OK", "ER:DAMAGE COMPLAINT", _
        "FB:XDV TP NEW CONNECT", "FD:XDV TP RECONNECT", "FF:XDV/XI NEW CONNECT", _
        "FP:XDV/XTV NEW CONNEC", "FQ:XDV/XTV RECONNECT", "GA:XDV/XI CONNECT", _
        "HF:HF PROACTIVE SROTC", "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", "NA:NA/NH TECH TIME", "NH:NODE HEALTH", "NP:NONPAY DISCONNECT", _
        "SN:SIK RECONNECT", "SV:SERVICIBLITY", "TE:TAP DISC/EQP PKUP", "TS:TRUNK SURVEY", _
        "WA:WP VIDEO NEW CNCT", "WC:WP CHSI NEW CONN", "WE:WP VIDEO CHG OF SR", _
        "WF:WP VIDEO CONNECT", "WG:WP VIDEO DISCO", "WK:BCDV/CHSI NEW CNCT", _
        "WL:BCDV_CHSI RECONNCT", "WU:WP CHSI COS", "WV:WDV DISCO", "XA:XH CONNECT", _
        "YB:BCDV3.0 COS", "YF:COMM PRE SURVEY", "ZE:XI/XTV RECONNECT", _
        "ZP:XI/XTV NEW CONNECT") _
        , Operator:=xlFilterValues
    Rows("5:5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$W$20000").AutoFilter Field:=4
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "MA"
    ActiveSheet.Range("$A$1:$X$20000").AutoFilter Field:=13, Criteria1:="CN"
    Rows("27:40").Select
    Range("G27").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-66
    ActiveWindow.ScrollRow = 1044356
    
'   Some of the unnecessary code lines removed by Moderator

    ActiveWindow.ScrollRow = 1
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$X$20000").AutoFilter Field:=13
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    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"
    ChDir "C:\Documents and Settings\TCaine001\My Documents\Vlookup"
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\TCaine001\My Documents\Vlookup\Chicago MA.xlsx"
    Windows("Pending TC - Nodes Report.xlsx").Activate
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Chicago MA.xlsx]Route Criteria'!R2C1:R81C2,2,FALSE)"
    Selection.AutoFill Destination:=Range("G2:G1426")
    Range("G2:G1426").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Job Search!R1C1:R1048576C25", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable5" _
        , DefaultVersion:=xlPivotTableVersion14

 

[Peter_SSs]

Note that I have added Code Tags to your code. This preserves indentation and makes your code much easier to read/debug. presenting code like this will get many more potential helpers to look at your problem. See my signature block for how to use Code Tags.

I also removed a lot of the unnecessary "ScrollRow" lines from the middle of your code. Unfortunately the macro recorder often includes a lot of "junk" that is best cleaned up.