VBA Help with Copying cells to new sheet when Column A has an X in it. (workbook attached)

[jjward101]

Hi. I would like to create a catalog which will house all of the possible materials that I use in doing my job….but would like a scenario where when I place an “X” in column A, all of the materials will be copied to a new sheet, which I called “BOM” in the attached workbook. I am not looking to copy the entire row though….only certain cells. On the BOM sheet is where quantities and the math to calculate selling price will go.
The cells I am trying to move to the new sheet (labels on both sheets correspond) are Part Number, Description, and List price. (respectively in columns C,D,E on the Catalog sheet).

The column labeled 'config code' will be used to filter for when a certain project has the same parts needed and is a commonly quoted project.

Once I have this working, I will be able to quickly and easily put together quotes for customers. New business due to being laid off, so I cannot afford to spend money on a larger scale solution that I know exists.

I would be truly grateful if someone can help me with this. Trying to keep my family fed.

[cytop]

Some basic code to go in the class module for the Catalog sheet:

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim r As Range
   Dim BOMRow As Long
   
   For Each r In Intersect(Target, Range("A:A"))
      
      If r.Value = "x" Then
         BOMRow = shtBOM.Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
         shtBOM.Cells(BOMRow, 2).Value = r.Offset(, 2).Value
         shtBOM.Cells(BOMRow, 4).Value = r.Offset(, 3).Value
         shtBOM.Cells(BOMRow, 10).Value = r.Offset(, 4).Value
      ElseIf r.Value = vbNullString Then
         Set r = shtBOM.Range("B:B").Find(What:=r.Offset(, 2).Value)
         If Not r Is Nothing Then
            r.EntireRow.Delete
         End If
      End If
   Next
   
End Sub

Even though an updated workbook is attached, if you copy/paste that code make sure you include the 2 'Option' statements at the top of the code window as VBA is case sensitive by default.

Also, the code name for the BOM sheet has been changed from the VBA default. Comments in the code in the workbook.