Updating rows in another sheet based on 2 criteria

[Thecraftycarrot]

Hi what i am trying to do is move data captured in one sheet, into a secondary sheet once the form has been completed.

But the sticker is it needs to be triggered only when 2 criterion are met, and then it needs to find and override information based on one of the criterion.

So lets say the following applies:

Sheet 1 = Data capture form
Sheet 2 = Data table capturing responses.

The critireon that needs to be met to move one form the other is:
B1 = "YES"
A1 <> ""

A1 is a cell where the vendor name is populated.

Therefore if both criterion is TRUE, i need to find the name given in a1 within column A in sheet 2.

Once this has been found i need to do the following
Copy cells c3 (sheet 1) into column d in sheet 2(row to be defined by the row the vendor name was found)
Copy cells j24 (Sheet 1) into column F in sheet 2 (As per above brackets)

not sure if it makes a difference but the data in sheet 2 will be recorded within a table.

If anyone could help would be appreciated, as i have been stuck and going round in circles for days.

 

[JustynaMK]

Hi,
In order to achieve your goal, you need to add a Change Event (VBA).

A few assumptions:
- the names of your worksheets are "Sheet 1" and "Sheet 2"
- the name of your table in Sheet 2 is "tblSheet2"
- a list of Vendors is presented in "tblSheet2", 1st column (this is where we're going to match the Vendor name with Sheet 1, cell A1)
- column D is your 4th table column ("tblSheet2")
- column F is your 6th table column ("tblSheet2")

Open VBA window (Alt+F11), double-click on Sheet 1 and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngVendor           As Long
    Dim lobTable            As ListObject
    
    Set lobTable = Worksheets[COLOR=#ff0000]("Sheet 2")[/COLOR].ListObjects([COLOR=#0000ff]"tblSheet2"[/COLOR])
    
    With Worksheets[COLOR=#ff0000]("Sheet 1")[/COLOR]
        If Not Intersect(Target, .Range("A1:B1")) Is Nothing And .Range("B1").Value = "YES" Then
            On Error Resume Next
            lngVendor = Application.Match(.Range("A1"), lobTable.ListColumns[COLOR=#00ff00](1)[/COLOR].DataBodyRange, 0)
            On Error GoTo 0
            If lngVendor > 0 Then
                Worksheets([COLOR=#ff0000]"Sheet 2"[/COLOR]).Activate
                lobTable.DataBodyRange(lngVendor, [COLOR=#ff8c00]4[/COLOR]).Value = .Range("C3").Value
                lobTable.DataBodyRange(lngVendor, [COLOR=#800080]6[/COLOR]).Value = .Range("J24").Value
            End If
        End If
    End With
    
End Sub

I have highlighted all main components in case you need to change the assumptions.
Let me know if that works for you.