Put in a Module.
Sub PullFS()
Dim WS As Worksheet
Dim WB_M As Workbook
Dim WS_M As Worksheet
Dim Unique As New Collection
Dim A As Long
Dim LR_s As Long
Dim LR_m As Long
Dim FN As Variant
Dim Ctr As Long
Ctr = 2
Set WS = Workbooks("Pull_FS ACTION.xlsb").Worksheets("ODR")
'Browse for file
FN = Application.GetOpenFilename("Excel Files (*.xls?), *.xls?")
'Open workbook
Set WB_M = Workbooks.Open(FN)
'Set WB_M = Workbooks("Master_AFS.xlsx")
'Clear range on FS worksheet
WS.Range("L:L").Clear
'Define masterworkbook sheet1
Set WS_M = WB_M.Worksheets(1)
With WS_M
'Last row of master
LR_m = .Cells(.Rows.Count, "G").End(xlUp).Row
'Iterate Column G and pick unique values.
For A = 2 To LR_m
If .Range("G" & A) <> "" Then
On Error Resume Next
Unique.Add CStr(.Range("G" & A)), CStr(.Range("G" & A))
On Error GoTo 0
End If
Next
'Iterate unique list to search from.
For A = 1 To Unique.Count
With .Range("G2:G" & LR_m)
'Debug.Print Unique(A)
Set c = .Find(Unique(A), LookIn:=xlValues)
If Not c Is Nothing Then
Do
'Test for F5 value
If c.Offset(, Range("AV1").Column - Range("G1").Column) = "Awaiting for FS action" Then
'Output to F5 sheet.
WS.Range("L" & Ctr) = c
'Increment row counter
Ctr = Ctr + 1
'Stop looking for this item.
Exit Do
Set c = .FindNext(c)
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next
End With
'Close workbook.
WB_M.Close False
End Sub
Bookmarks