VBA code to copy from another workbook using multiple cell values

Hi all,

I have this code which searches for the value in F6 in another workbook and returns the relevant row from that workbook.

This might be an elementary question, but say I want to add another criteria , ie, I want the user to enter a value into cell F6 and, for example, F7, what changes would I need to make for this to happen?

It needs to be done as just searching by F6 doesn't differentiate between duplicates, and I need the user to also enter a name, again for example, in F7 to locate the exact row required.

Thanks.

Sub LookforText()
Dim bk1 As Workbook, sh1 As Worksheet, cell As Range
Dim bk2 As Workbook, sh2 As Worksheet, r As Range

Set bk1 = Workbooks("Credit Hire Review Assistant 4.02.xlsm")
Set sh1 = bk1.Worksheets("Settlement")
Set cell = sh1.Range("F6")    ' this remembers the cell where the search string is
  ' and can be used to produce the value on demand

Set bk2 = Workbooks.Open("S:\Claims\Credit Hire\Credit Hire Spreadsheet\C Hire MI Sept 2011-.xlsx")
Set sh2 = bk2.Worksheets("Sheet1")
Set r = sh2.Range("D:D").Find(What:=cell.Value, _
  After:=Range("D3"), _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)
If r Is Nothing Then
 MsgBox "Not found"
Else
 Application.Goto Reference:=r
End If

ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select

Application.DisplayAlerts = False

Workbooks("Credit Hire Review Assistant 4.02.xlsm").Worksheets("Settlement").Activate
Range("A40").Select
    Range("A40").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Range("F11").Select
        

Workbooks("C Hire MI Sept 2011-.xlsx").Activate
ActiveWorkbook.Close SaveChanges:=True






End Sub

Hi,

Knew I'd forgotten something important...

F7 would match with Column F

Thanks,

That works perfectly, thankyou.

The one thing that I may need to change, it now comes up with the message box about there being a large amount of data on the clipboard and do I want to keep it (after pasting into sh1).

How do I set this to automatically say no?