Can't figure out this script Excel thing

[Webhead]

Anyone familiar with macros in Excel? Trying to figure out how to copy a row of data on a sheet in one workbook to a sheet in another workbook automatically.

For example, let's say you have the first workbook. It contains rows of data about various products. Let's say pencils, paper and erasers. Then you have 3 other Excel files... One for pencils, one for paper and one for erasers.

So on the main one, let's say you start at the beginning and start typing in information about a pencil. Then at the end of the row, you mark it as a "pencil" (maybe with a data validation menu or something?) But by choosing it as a pencil, it then copies the row and appends to the "pencil.xlsx" workbook. Then you go to the next row and start typing in information about paper. At the end, you select "paper" and then that row copies over and appends to "paper.xlsx".

Does that make sense? Is this too complicated? Or is it easy and I'm just slow? Because I'm having trouble making this work.

So far I found a script that comes close. It's applied to a button. When the button is pressed, it copies stuff marked as "Yes" to another sheet. But I want it to actually go to a sheet on another file.

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    
    On Error GoTo Err_Execute
    
    'Start search in row 4
    LSearchRow = 4
    
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2
    
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        
        'If value in column E = "Yes", copy entire row to Sheet2
        If Range("D" & CStr(LSearchRow)).Value = "Yes" Then
            
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            
            'Paste row into Sheet2 in next row
            Sheets("Sheet2").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            
            'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select
            
        End If
        
        LSearchRow = LSearchRow + 1
        
    Wend
    
    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
    
    MsgBox "All matching data has been copied."
    
    Exit Sub
    
Err_Execute:
    MsgBox "An error occurred."
    
End Sub

And if my words are confusing, then maybe this will clear it up. I'm thinking it would work something like this...