VBA based on cell value search for it in the table and then copy a row (help with existing code)

[DariusUK]

I managed to have to make it partially work; however, it copied and pasted a whole range from Data sheet - I want only the row that matches C6 from Entry sheet .

Here is the code:

Sub Find_Order()
Dim entrySheet As Worksheet
Dim dataSheet As Worksheet
Dim checkSheet As Worksheet
Dim searchValue As Variant
Dim lastDataRow As Long

' Define the worksheets
Set entrySheet = ThisWorkbook.Sheets("Entry")
Set dataSheet = ThisWorkbook.Sheets("Data")
Set checkSheet = ThisWorkbook.Sheets("Check")

' Get the search value from Entry C6
searchValue = entrySheet.Range("C6").Value

' Find the last row in the Data sheet's column A
lastDataRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row

' Check if the search value is found in Data sheet's column A
If Not IsError(Application.Match(searchValue, dataSheet.Range("A1:A" & lastDataRow), 0)) Then
' If found, copy the entire column to the Check sheet
dataSheet.Range("A" & lastDataRow).EntireRow.Copy
checkSheet.Rows(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Else
MsgBox "Value not found in Data sheet.", vbInformation
End If

Application.CutCopyMode = False ' Clear the clipboard
End Sub

Any help will be appreciated

 

[DanteAmor]

Hi @DariusUK and Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Replace your code with the following:

Sub Find_Order()
  Dim entrySheet As Worksheet
  Dim dataSheet As Worksheet
  Dim checkSheet As Worksheet
  Dim searchValue As Variant
  Dim f As Range
 
  ' Define the worksheets
  Set entrySheet = ThisWorkbook.Sheets("Entry")
  Set dataSheet = ThisWorkbook.Sheets("Data")
  Set checkSheet = ThisWorkbook.Sheets("Check")
 
  searchValue = entrySheet.Range("C6").Value        ' Get the search value from Entry C6
  ' Check if the search value is found in Data sheet's column A
  Set f = dataSheet.Range("A:A").Find(searchValue, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    ' If found, copy the entire row to the Check sheet
    f.EntireRow.Copy
    checkSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
  Else
    MsgBox "Value not found in Data sheet.", vbInformation
  End If
 
  Application.CutCopyMode = False ' Clear the clipboard
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --