Copy and Paste code works, but needs tweaked

[Msears]

Hello All, new to VBA and needs some help tweaking this code. What I would like to happen is that it would copy and paste all rows that have a unique value in Col E. For instance, each time "Blue" is mentioned, it c/p into its corresponding sheet named Blue. In the first sheet 1 "Client" the row starts in Col B, when c/p'd into sheet 2"Blue", it will need to start in Col A, and not pull over Col A, from sheet 1. I hope this makes sense? The code below works, but it is just c/p everything, not a a specific value in Col E? I will potentially have data in range B:N on sheet one in the future. Thanks in advance! MS

Dim a As Range, c As Long, MyRanges As Range
Set MyRanges = Range("B:N")

On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each a In ThisWorkbook.Sheets("Clients").MyRanges.Areas
If Not Intersect(Target, a) Is Nothing Then
Intersect(Target.Offset(, 1).Resize(, 3), a).ClearContents
End If
Next a

If Target.Value = "Blue" Then
With ThisWorkbook.Sheets("Clients").Range("E1", Range("E" & Rows.Count).End(xlUp))
.Offset(1).Resize(.Rows.Count).EntireRow.Copy
ThisWorkbook.Sheets("Blue").Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
End With
End If

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True

 

[Snakehips]

@Msears The references to Target and Events in your code suggest that you are looking to utilise the Change Event ?
So that the line B:N just completed with entry of vehicle colour in E will be copied to A:M of the next available row in sheet of same name?

What is the purpose re the clear contents?

 

[Msears]

@snakechips Actually it was a code that I had used in the past and was trying to make it work with this scenario? It may need to be rewritten to fit this scenario. I have a user form in Col A that isn’t in the picture and I have this code attached to the exit button, so it will transfer to sheet Blue upon exit of the form. I believe the clear contents was there so it doesn’t “duplicate” values on the the second page?