Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+r
'
Sheets("HCR Match Audit Report").Select
Range("M:M").Cut Range("N:N")
Range("M1").Select
ActiveCell.FormulaR1C1 = "Match/Mismatch"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(EXACT(RC[-12],RC[-3]),""Match"",""Mismatch"")"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("M1").Select
Selection.AutoFilter
Range("M1").Select
ActiveSheet.Range("A:N").AutoFilter Field:=13, Criteria1:= _
"Mismatch"
Range("A:N").Select
Range("M1").Activate
Selection.Copy
Sheets("Incorrect Req Title").Select
Range("A1").Select
ActiveCell.Select
ActiveSheet.Paste
Sheets("HCR Match Audit Report").Select
Range("M1").Select
ActiveSheet.ShowAllData
Worksheets("Sch. Appt 4 Report").Range("N2:N25000").Formula = "=IF(OR(ISNUMBER(SEARCH(""Pending"",J2)),ISNUMBER(SEARCH(""Pending"",L2))),""Invalid"",""Valid"")"
Worksheets("Sch. Appt 4 Report").Range("O2:O25000").Formula = "=IF(OR(ISNUMBER(SEARCH(""Pending"",K2)),ISNUMBER(SEARCH(""Pending"",M2))),""Invalid"",""Valid"")"
Worksheets("Sch. Appt 4 Report").Range("P2:P25000").Formula = "=IF(OR(ISNUMBER(SEARCH(""Complete"",K2)),ISNUMBER(SEARCH(""Complete"",M2)),ISBLANK(K2),ISBLANK(M2)),""Valid"",""Invalid"")"
Worksheets("Sch. Appt 4 Report").Range("Q2:Q25000").Formula = "=IF(AND(N2=""valid"",O2=""Valid"",P2=""Valid""),""Schedule"",""Do Not Schedule"")"
Worksheets("Sch. Appt 4 Report").Range("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Worksheets("Sch. Appt 4 Report").Select
ActiveSheet.Range("A:Z").AutoFilter Field:=17, Criteria1:="Do Not Schedule"
Rows("2:25000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Sheets("Sch. Appt 4 Report").Select
Range("J1").Select
ActiveCell.FormulaR1C1 = "Timeslot"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Timeslot ID"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Timespan ID"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Status"
Sheets("Timeslotspan Report").Select
Range("I1").Select
Selection.AutoFilter
ActiveSheet.Range("A:J").AutoFilter Field:=9, Criteria1:="="
Range("A:J").Select
Selection.Copy
Sheets("Timeslots - Missing Alias").Select
Range("A1").Select
ActiveCell.Select
ActiveSheet.Paste
Sheets("Timeslotspan Report").Select
Application.CutCopyMode = False
ActiveSheet.Range("A:J").AutoFilter Field:=9
Selection.AutoFilter
Sheets("Sch. Appt 4 Report").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'HCR Match Audit Report'!C[-8]:C[-1],8,FALSE)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Timeslotspan Report'!C[-10]:C[-9],2,FALSE)"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-21
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Timeslotspan Report'!C[-11]:C[-8],4,FALSE)"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'Timeslotspan Report'!C[-12]:C[-7],6,FALSE)"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("M1").Select
Selection.AutoFilter
ActiveSheet.Range("A:M").AutoFilter Field:=13, Criteria1:="Open"
Range("H:I").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("File to be uploaded").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sch. Appt 4 Report").Select
Application.CutCopyMode = False
Range("K:L").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("File to be uploaded").Select
Range("C1").Select
ActiveSheet.Paste
End Sub