Automatically move rows to another worksheet

[el12345]

Hello,
I am trying to set up a new document to track clients, I have included a screenshot.
For each worksheet, I want to be able to enter the status in column N which will transfer the specific row to whichever worksheet I have specified in the status (New Enquiries, Consultations, Quotes To Do, To Book, Live, Booked, Finished). Please can anyone tell me how to do this? Also, it is a problem that I have used a merged cell and table?
Thank you so much!

 

[onlyadrafter]

Hello

This is how you would do it (can you give details on the merged cell and table details).

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 14 Then
        Rows(Target.Row).Copy
        Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Rows(Target.Row).Delete
    End If
    Application.EnableEvents = True
End Sub

This code needs to go in the THIS WORKKBOOK code window, not a standard module.

 

[el12345]

Hello

This is how you would do it (can you give details on the merged cell and table details).

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = 14 Then
        Rows(Target.Row).Copy
        Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Rows(Target.Row).Delete
    End If
    Application.EnableEvents = True
End Sub

This code needs to go in the THIS WORKKBOOK code window, not a standard module.

Thanks so much for your reply! Do I need to edit anything in this in order for it to work? I have gone to a tab in the workbook, selected view code and then changed on the drop down to Workbook_SheetChange, is that the correct way? I'm not too experienced with this! With regards to the merged cell and table, I had seen in a different thread that a merged cell may affect the VBA, so I was unsure if my merged cell A1-N1 would stop this from working.

Thanks again
Elena

 

[Joe4]

I had seen in a different thread that a merged cell may affect the VBA, so I was unsure if my merged cell A1-N1 would stop this from working.

Yes, merged cells are one of the most problematic features of Excel, and really should be avoided whenever possible. They wreak havoc with things like sorting and VBA.

Note then when merging columns across a single row (like A1-N1), using the "Center Across Selection" formatting option will give you the same visual effect as merged cells, but without all the problems merged cells cause.

See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[el12345]

Yes, merged cells are one of the most problematic features of Excel, and really should be avoided whenever possible. They wreak havoc with things like sorting and VBA.

Note then when merging columns across a single row (like A1-N1), using the "Center Across Selection" formatting option will give you the same visual effect as merged cells, but without all the problems merged cells cause.

See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Brilliant! Thank you - I removed the merged cell and the VBA worked it was also putting the data at the end of the table that I been putting my data in, so I have converted them back to normal.