Macro Not working

[Rajeev_Raghavan]

Macro not working after the red line.

Sub Res2PR()
'
' Res2PR Macro
' Res for PR
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Workbooks.Open "C:\Users\rajeev.raghavan\Desktop\data\res.XLSX"
Cells.Select
With Selection
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Helper"
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=OR(LEFT(RC[1],1)=""6"",LEFT(RC[1],1)=""7"",LEFT(RC[1],1)=""9"")"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G1803")
Range("G2:G1803").Select
ActiveWindow.SmallScroll Down:=-30
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SEARCH(""Petty"",$AC2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=1
Range("AZ1").Select
ActiveWindow.SmallScroll Down:=-12
Range("G1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AY$1803").AutoFilter Field:=7, Criteria1:="TRUE"
ActiveSheet.Range("$A$1:$AY$1803").AutoFilter Field:=8, Operator:= _
xlFilterNoFill
ActiveWindow.SmallScroll Down:=-6
ActiveSheet.Range("$A$1:$AY$1803").AutoFilter Field:=6, Criteria1:="ND"
ActiveWindow.SmallScroll Down:=-48
ActiveSheet.Range("$A$1:$AY$1803").AutoFilter Field:=5, Criteria1:="="
ActiveWindow.SmallScroll Down:=-114
Range("A1:AY1803").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Columns("I:I").ColumnWidth = 19.14
Columns("I:I").ColumnWidth = 21.29
Columns("J:J").ColumnWidth = 26.57
Columns("J:J").ColumnWidth = 36
Columns("F:F").ColumnWidth = 4.57
Columns("D:D").ColumnWidth = 4.29
Columns("C:C").ColumnWidth = 5.86
Columns("B:B").ColumnWidth = 6.14
Range("A1").Select
Columns("H:H").ColumnWidth = 10.43
ActiveWindow.SmallScroll Down:=-24
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Base date"
Range("A1").Select
Selection.AutoFilter
End Sub

[#2 · Accepted solution]

@Rajeev_Raghavan Took your code and put it into a new workbook. Obviously, I could not run it, as I don't have your file "res.XLSX". Your code included many lines that came from the recording process (selecting cells, scrolling, reselecting cells, doing something and than re-doing it etc). I cleaned up the code and disabled the second row after "Selection.Autofilter". The row that ends with "Field:=8, Operator:=xlFilterNoFill" stopped the macro, so I put an apostrophe in front of it to make it a comment rather than an active piece of code. Now, the macro runs from start to finish, but as said, I can't test it or even imagine if this is doing what you have in mind.

Try the code below, and let me know how it goes.

Sub Res2PR()
'
' Res2PR Macro
' Res for PR
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    'Workbooks.Open "C:\Users\rajeev.raghavan\Desktop\data\res.XLSX"
    
    With Selection
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Helper"
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=OR(LEFT(RC[1],1)=""6"",LEFT(RC[1],1)=""7"",LEFT(RC[1],1)=""9"")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G1803")
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SEARCH(""Petty"",$AC2)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    
    Selection.FormatConditions(1).StopIfTrue = False
    
    Range("G1").Select
    Selection.AutoFilter
    
    ActiveSheet.Range("$A$1").AutoFilter Field:=7, Criteria1:="TRUE"
    'ActiveSheet.Range("$A$1").AutoFilter Field:=8, Operator:=xlFilterNoFill
    ActiveSheet.Range("$A$1").AutoFilter Field:=6, Criteria1:="ND"
    ActiveSheet.Range("$A$1").AutoFilter Field:=5, Criteria1:="="
    
    Range("A1:AY1803").Select
    
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    
    Columns("I:I").ColumnWidth = 21.29
    Columns("J:J").ColumnWidth = 36
    Columns("F:F").ColumnWidth = 4.57
    Columns("D:D").ColumnWidth = 4.29
    Columns("C:C").ColumnWidth = 5.86
    Columns("B:B").ColumnWidth = 6.14
    Columns("H:H").ColumnWidth = 10.43
    
    Range("A1").Select
    
    Sheets("Sheet1").Select
    Range("A1").Select
    
    Application.CutCopyMode = False
    
    ActiveCell.FormulaR1C1 = "Base date"
    Range("A1").Select
    
    Selection.AutoFilter
End Sub

[#3]

Dear @Riny_van_Eekelen ,

please check the screenshot.

Stopped here

[#4]

@Rajeev_Raghavan In my mock-up sheets the macro runs without interruption. It creates the second sheet with only items that have TRUE in col G, ND in col F and blank in col E. Column widths are set in accordance with the values in the macro. "Base date" gets entered in A1 on Sheet1 and the auto-filter is switched off.

But again, your sheet is much larger than the one I'm "playing" with. Please upload your schedule (confidential information removed!) so that I can have a look at it.

[#5]

Its working @Riny_van_Eekelen, thanks for your valuable response.