Move row to another worksheet base on a cell value

[Lilleyj]

Hi There,

In coloumn D i have a data validation list (N,Y,NA). If "N" is selected I want the entire row to move to the worksheet called "Declined Referrals".

How can i do this?

Many thanks
Jess

[Mumps1]

Hi Jess. Try:

Please Login or Register  to view this content.

[Lilleyj]

Hi there,

Thank you for your quick response. Doesn't seem to work though :/

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Lilleyj]

That worked great, thank you. Is there a way to remove it from the original worksheet once it has been copied to the 'Declined Referrals'.

Many thanks
Jess

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Lilleyj]

Brilliant, thank you

[Lilleyj]

Sorry spoke too soon. It is showing run-time error '13' - Type mismatch. When i press debug it is highlighting "If Target.Value = "N" Then

[JOHN H. DAVIS]

Change too: If Target.Text = "N"

[Lilleyj]

Hi,

What does the End(3)(2) do? Only I have copied the code to use in a new spreadsheet and it has come up with run-time error 9. This is teh new code



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(13)) Is Nothing Then
If Target.Text = "Blue" Then
Rows(Target.Row).Copy Sheets("Embedded as Business as Usual").Range("A" & Rows.Count).End(3)(2)
Rows(Target.Row).Delete
End If
End If
End Sub

[JOHN H. DAVIS]

It places the copied row on the destination sheet to the first in ununsed cell in Column A. Check your destination Sheets name. Spelling, spaces ect.

[Lilleyj]

*blushes* whooops you were right.

sorry and thank you

[Lilleyj]

Sorry me again, I've noticed an actual probem this time. When it is transferring to the next worksheet it is deleting the row that was there before instead of adding it underneath

[JOHN H. DAVIS]

Can you attach a sample where that is occuring? It shouldn't overwrite anything.

[Lilleyj]

EAU3_Patient_Status_2014.xlsm

Thank you

[JOHN H. DAVIS]

Umm. You have merged cells in your sheets, which can cause problems. Can something like this help?

Please Login or Register  to view this content.

[Lilleyj]

Sorry, it's still deleting rows

[JOHN H. DAVIS]

This one is working for me on the sample provided?

Please Login or Register  to view this content.

I also changed this:

Please Login or Register  to view this content.

[Lilleyj]

It's very strange as it was working perfectly before. I'll attach my one again.

[Lilleyj]

EAU3_Patient_Status_2014.xlsm

Cheers

[JOHN H. DAVIS]

Try it now.

[Lilleyj]

Sorry it's still doing it but this time with run-time error '1004': method 'intersect of object_global'failed. Could it be my computer because it used to work fine?

Thank you

[JOHN H. DAVIS]

I dunno. It works on mine?

[Lilleyj]

I've worked it out, it's happening when A1 is blank on the previously copied across row, so it thinks teh whole row is blank and pastes over it. Can we ask it to check if column B is empty?

Many thanks
Jess

[JOHN H. DAVIS]

I'm not sure what you mean? The code provided is relative too what's in Column D not A or B, because on your spreadsheets Columns A,B and C are merged.