Hi all,
I have the following macro that finds a contract number listed in Column C in one sheet to where it appears in the following 10 sheets (in column B) then it returns the message "Found in Data Base".
What I want is this to work with two separate workbooks - one containing a list of contract numbers to be found in the second workbook. And instead of returning "Found in Data Base" when a match is found, I would like it to copy the Name of the contract corresponding the the contract number as a check.
In the macro below I have opened up the second workbook but I am unsure how to tell the macro to switch between the two workbooks.
Any help would be appreciated.
Jac
I have the following macro that finds a contract number listed in Column C in one sheet to where it appears in the following 10 sheets (in column B) then it returns the message "Found in Data Base".
What I want is this to work with two separate workbooks - one containing a list of contract numbers to be found in the second workbook. And instead of returning "Found in Data Base" when a match is found, I would like it to copy the Name of the contract corresponding the the contract number as a check.
In the macro below I have opened up the second workbook but I am unsure how to tell the macro to switch between the two workbooks.
Code:
Sub FindMatch()
' Open Data Base file to facilitate matching process.
' Check for matching data
Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:= _
"G:\Registration\Data List amended for DOD.xls"
MsgBox ("Opening DataBase")
Windows("Claimed Matching Data.xls").Activate
Sheets("M - Matching Data").Select
[L3].Select
While ActiveCell.Offset(0, -9) <> Empty
For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
Set R = WS.Range("b2:b3000").Find(ActiveCell.Offset(0, -9))
If Not R Is Nothing Then
ActiveCell.Offset(0, 1).Value = "Found in Data Base"
Exit For
End If
Next
ActiveCell.Offset(1).Select
Wend
End Sub
Any help would be appreciated.
Jac