Automatically copying rows from one sheet to another based upon criteria in column B

I have a worksheet with all of my entries, called "all" (sheet 2):
Column A: date
Column B: Pymt (such as "Amex", "Visa" or a number for a check, etc)
Column C: Amount
Column D: Vendor
Column E: Notes
Column F: Job
Column G: Category (such as "Commissions", "Materials" etc)

I would like sheet 4, called "Amex" to automatically populate rows A:G with column B payments that are Amex, as I add them to "all" throughout the year. (As well as the other pymt types on their respective sheets)

I have used a database in the past to accomplish this, but I have a bad "delete" trigger finger and want to avoid databases. You can only protect-lock the delete/insert rows and columns, not cells. I found this VB script somewhere else, but I must not understand it because it didn't work The sample was for someone else's worksheet, but I only changed my sheet and range names. Could someone help me out?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

If Target.Value = "Amex" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Amex").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)


End If

End Sub

Hello SLBell,

Try the following adjustment to your code:-

Please Login or Register  to view this content.

Your sheet names need to be spelt exactly as per the entries in Column B for the code to work. Perhaps using a drop down in each cell in Column B would be a good idea to prevent spelling errors.

I hope that this helps.

Cheerio,
vcoolio.

Thank you so much for replying! For some reason, nothing happened when I entered a test entry. I changed "sheet" to "Amex" and nothing happened either. I have attached a very pared down spreadsheet, so you can see what I am trying to accomplish. [The dashboard (sheet 1) is obviously messed up now, I'm not worried about it because my full workbook is OK.] I really appreciate your help!
Suzanne
https://www.dropbox.com/sh/notaqk42m...lV4GfTcxa?dl=0

Hello again SLBell,

There appears to be some problem with opening the attachment (on my end at least) so below is a DropBox link to the sample file just in case:-

https://www.dropbox.com/s/kfeeqoiu3c...l%29.xlsm?dl=0

Cheerio,
vcoolio.