VBA and Tables and Copy Data

[willgv]

Hello,

I am very new to VBA and am looking for some help with something I'm trying to figure something out. Here are some details:

I have "Table1" in a sheet called "Interviews"
I have "Table2" in a sheet called "Main"
"Table1" Stats: Columns A-I. Headers on Row 4 and first Data row is 5.
"Table2" Stats: Columns A-J. Headers on Row 12 and first data row is 13.

What I'm trying to accomplish is whenever entry in Table1, Column H (or 8) says "Yes", I want the data from Column A (or 1) in that same row to go to Column A in "Table2" and data from Column I (or 9) in that same row to go to Column B in "Table2", in the next available line.

Is it possible to create a macro that constantly works in the background? So when I add an entry in "Table1", an entry will automatically appear in "Table2" in the next available line when it meets the condition.
If there isn't, I plan on creating a button on the "Main" worksheet so a user can just press it to update "Table2".

Thanks for your help!

 

[mumps]

Hi willgv. Do the following: right click the sheet tab for worksheet "Interviews". Click 'View Code'. Paste the followng code in the empty code window that opens up. Close the window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Sheets("Main").Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    If Target = "Yes" Then
        lRow = lRow + 1
        Cells(Target.Row, "A").Copy
        Sheets("Main").Cells(lRow, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Cells(Target.Row, "I").Copy
        Sheets("Main").Cells(lRow, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[willgv]

Hey mumps!

Thank you so much for the code! I would never have been able to figure that out on my own.

I thought it wasn't working because i was putting in information after I selected the "Yes" but I realized that I have to put in all the information first before choosing Yes in the H column. I will make a note of that so other users can take full advantage of the feature.

Thanks again!

 

[mumps]

It was my pleasure. You figured it out. That's terrific. The macro is a worksheet change event which means that it is triggered by a change in one of the cells, in this case the cells in column H.