Why don't you create a template for that. A workbook with 3 sheets: Master, Purchased and Normal. Apply all the formats (columns width, header, etc.) you want.
When you get your next BOM, paste it in the Master sheet and save the workbook under a different name. Then run the code below to copy/paste each row to it's according sheet (Only works for pre-created sheets):
For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
cell.EntireRow.Copy Sheets(cell.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Is this what you were looking for?
As a novice you might find this helpfull:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.