copy and paste rows to sheets based on multiple selections

[Jason Frazee]

Hey guys I currently have an excel sheet where column J has a dropdown arrow with 42 selections.

I have made 42 sheets matching those selections. I need a way to have excel copy and move the rows to their sheet based on the selection. For example everytime I select "AMI" that row i made the selection in is then copied to a sheet named "AMI"

I have code that does it now but only for one selection. Any help will be appreciated. I would also like to add something to have the script move the selection over instantaneously. I know its a big task which is why I am in need of help

Sub CopySchoolAMI()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet


Set Source = ActiveWorkbook.Worksheets("all p.o.")
Set Target = ActiveWorkbook.Worksheets("AMI")

j = 1
For Each c In Source.Range("J1:J1000")
If c = "AMI" Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c
End Sub

 

[p45cal]

Supply a workbook with the necessary minimum; it will stop us guessing wrongly.

 

[Jason Frazee]

I attached the workbook. Once you select a selection from column J dropdown it will add the row to its destined sheet however if I select the same selection twice it will not continually add rows to the sheets.

 

[p45cal]

however if I select the same selection twice it will not continually add rows to the sheets.

Since where the row is copied to depends on:
.Cells(Rows.Count, "A").End(xlUp)
there has to be something in column A when something is copied over.