Sub TASKSHEET()
Dim Usdrws As Long
Usdrws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Columns("V:V").Select
ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
"=*Asset*", Operator:=xlAnd
If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then GoTo Xit
[COLOR=#ff0000]Range("W1").Select[/COLOR]
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("My FAR Search Engine.xlsx").Activate
Sheets("ASSET # Search").Select
Range("B3").Select
ActiveSheet.Paste
'''''''''''''''''''''''''''''''''''''''''''
For Each w In Workbooks
If Left(w.Name, 2) = "11" Then
Workbooks(w.Name).Activate
Exit For
End If
Next w
Range("Z1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC23,'[My FAR Search Engine.xlsx]ASSET # Search'!R3C2:R2000C10,COLUMNS(RC23:RC[-3]),0)"
Selection.Copy
ActiveCell.Offset(0, -3).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Range("A1:I1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveWorkbook.BreakLink Name:= _
"C:\Users\Desktop\My FAR Search Engine.xlsx", _
Type:=xlExcelLinks
Range("X1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.FormulaR1C1 = "=RC[-7]=RC[8]"
Selection.Copy
Range("W1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Call Same(Usdrws)
MsgBox "Your Task Sheet is ready for your review..", vbOKOnly + vbInformation, "Task Sheet"
Xit:
MsgBox "Asset not found"
Call Same(Usdrws)
End Sub
Sub Same(Usdrws As Long)
Columns("V:V").Select
ActiveSheet.Range("A1:AA" & Usdrws).AutoFilter Field:=22, Criteria1:= _
"=*Same*", Operator:=xlAnd
If Range("V1:V" & Usdrws).SpecialCells(xlVisible).Cells.Count = 1 Then
MsgBox "Same not found"
Exit Sub
End If
Range("V1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "=INDEX(C[-21],MATCH(RC[-3],C[-17],0))"
Selection.Copy
ActiveCell.Offset(0, -3).End(xlDown).Select
ActiveCell.Offset(0, 3).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("V1").Select
ActiveSheet.Range("$A$1:$AI$76").AutoFilter Field:=22
End Sub