Create separate tables for each category from a master table

[AceCo55]

I have a "master table" (worksheet called "MASTER TABLE") that records purchases made by three people (column B): P-A, P-B and P-C (selected from a drop-down list)
Each purchase is placed within one of 8 categories (column C), selected from a drop-down list

I want to have a separate worksheet for each category.
When an entry is made on the "MASTER TABLE" worksheet the information for THAT purchase is automatically added to the next row in the corresponding category worksheet.

I can do this manually by filtering the MASTER TABLE for each category, and then copy-paste the filter results into the appropriate category worksheet - this is what I have done in the attached sample file.

Is it possible to get this result automatically as a new purchase is entered.
Thank you very much for any help.

Hmmm ... trying to add the sample file

 

[AceCo55]

Been looking, but can't find a way to attach a file?

Here's a link to the file (34kb): https://www.dropbox.com/s/ykmvmnuufyolzn6/Sample.xlsx?dl=0

 

[Michael M]

Maybe this

Sub MM1()
 Dim lr As Long, r As Long, lr2 As Long
Application.ScreenUpdating = False
 lr = Sheets("MASTER TABLE").Cells(Rows.Count, "B").End(xlUp).Row
 For r = lr To 2 Step -1
    Select Case Range("D" & r).Value
        Case "Consumables"
        lr2 = Sheets("Consumables").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Consumables").Range("A" & lr2 + 1)
        Case "Books"
        lr2 = Sheets("Books").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Books").Range("A" & lr2 + 1)
        Case "Equipment"
        lr2 = Sheets("Equipment").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Equipment").Range("A" & lr2 + 1)
        Case "Fees"
        lr2 = Sheets("Fees").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Fees").Range("A" & lr2 + 1)
        Case "Software"
        lr2 = Sheets("Software").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Software").Range("A" & lr2 + 1)
        Case "Internet"
        lr2 = Sheets("Internet").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Internet").Range("A" & lr2 + 1)
        Case "Ink"
        lr2 = Sheets("Ink").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Ink").Range("A" & lr2 + 1)
        Case "Other"
        lr2 = Sheets("Other").Cells(Rows.Count, "A").End(xlUp).Row
        Rows(r).Copy Sheets("Other").Range("A" & lr2 + 1)
  End Select
Next r
Application.ScreenUpdating = True
End Sub

 

[AceCo55]

Thank you very much Michael
Could you please tell me how to implement this code ... do I need to research "VBA code"? (I have never used it before)
Please don't spend a lot of time teaching this poor old fool ... but if you could just point me in the right direction for me to learn, that would be terrific.
Once again thanks - very much appreciate your expertise and willingness to share

 

[Michael M]

OK....step by step..

1. Copy the posted code
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, on the RH window,paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

 

[AceCo55]

OK....step by step..

THANK - YOU Michael
Works perfectly and is something I could NOT have possibly done.

Very much appreciate your willingness to share your expertise.
Happy days for me - thanks to you.

 

[Michael M]

Glad to help...