Copy complete row to corresponding tab depending on data validation

[SabrinaVBA]

I have been having some issues with this. I have data set up like the image below. I need a VBA to copy the whole row and place it in the correct tab.
So for below I need row 2 to be copied to the "Yes" tab, row 3 to the "No" tab and row 4 to be copied to the "Maybe Tab.

The data will also grow and change.
New data will be added on rows 5-??
And the data validation in column D can change. So once row 4 changes from maybe to yes I would need that row to be removed from the "Maybe" tab and copied to the last row in the "yes" tab.

Can this be done in VBA?
I am a newbie and have limited skills, I have found a few codes that almost worked, the closest one would copy the information once information in column D changed but it kept copying ALL the data so it would repeat row 2 over and over and over.

 

[SabrinaVBA]

The following seems to be working to move the items to the right tabs BUT if it runs again it is copying ALL the data again? I just need it to UPDATE if column D changes not copy all over again.

Sub Copy_Rows()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row

For i = 1 To Lastrow
Select Case Cells(i, "D").Value

Case "Yes"
Lastrow = Sheets(Cells(i, "D").Value).Cells(Rows.Count, "D").End(xlUp).Row + 1
Rows(i).Copy Sheets(Cells(i, "D").Value).Rows(Lastrow)

Case "No"
Lastrow = Sheets(Cells(i, "D").Value).Cells(Rows.Count, "D").End(xlUp).Row + 1
Rows(i).Copy Sheets(Cells(i, "D").Value).Rows(Lastrow)

Case "Maybe"
Lastrow = Sheets(Cells(i, "D").Value).Cells(Rows.Count, "D").End(xlUp).Row + 1
Rows(i).Copy Sheets(Cells(i, "D").Value).Rows(Lastrow)

End Select
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

So your wanting this to happen automatically when you enter a value in column D of the sheet named Data Sheet. Is that correct? And that row on the Data Sheet will stay where it is but will be copied to the proper sheet according to sheet name in column D

But then you also want a script to run on the other sheets when any value in column D changes.
But if the sheet is named Maybe and you enter Yes in column D of the maybe sheet you want this row copied to sheet named Yes and deleted from sheet named Maybe is this correct?

 

[SabrinaVBA]

My Aswer Is This.....that almost is all correct....

Everything except "But if the sheet is named Maybe and you enter Yes in column D of the maybe sheet you want this row copied to sheet named Yes and deleted from sheet named Maybe is this correct? "
All the data updates will be done on the DataSheet tab Only. So the user will go to the data sheet and change the maybe to a yes in column D for row 4....this would cause row 4 to be removed from the Maybe Tab and copied to the Yes Tab

 

[My Aswer Is This]

So on the Data sheet if column D says Maybe and you change Maybe to Yes.
Then the script needs to delete the row from the maybe sheet and add it to the Yes sheet.
So will all these always be on the same row

So on the Data sheet if you change Maybe to yes on row 4 it will always want to copy Row 4 from the Maybe sheet and copy it to row 4 of the Yes sheet.

If not how will the script know what row to copy the row to.

 

[SabrinaVBA]

Could it keep it in the original order of the data sheet?
So it would be row 1 of the Maybe sheet originally but after the change it would become the 2nd row on the Yes sheet

BUT if the No became a Yes, it would become the 2nd row on the Yes sheet and the Maybe changed to a Yes would become the 3rd row on the Yes sheet.

 

[My Aswer Is This]

I'm a little confused.

First its on row 1 of the Data Sheet
Then its copied to row 1 of the Maybe sheet then if its changed to yes on the data sheet you want it deleted from the Maybe sheet and copied to the Yes sheet.

I'm not following the logic of knowing how to know where on these other sheets to copy it to and from.
And if you say to row 2 does that mean we have to insert the row. Because maybe there is something already on row 2

 

[SabrinaVBA]

Ok it would be fine if they stayed on their original row number.

So once the maybe changed to yes it would remove from row 4 of the Maybe Sheet and move to the row 4 of the Yes Sheet.

I will be using filters on the other tabs so they can copy into their original row number.

 

[My Aswer Is This]

This may be something I'm not able to do.
It sounds like you want the script to run immediately upon your changing a sheet name in column D of the DataSheet.

Which means the script has to capture the value in column D before the script runs.
Then the script has to capture the new sheet name you enter in the cell in column D
Then the script has to delete the row in that sheet and add the new row to the sheet name you entered in the DataSheet.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.