Macro to copy data from sheet1 to sheet2 based on a criteria.

[Kaiser Khan]

I have two sheets.
First sheet contain this information.

BANK ACCOUNTS

COMPANY	ACCOUNT	BANK	STATUS
0001	12584563	BARC	LIVE
0002	12865845	HSBC	LIVE
0002	45864856	HSBC	CLOSED

<tbody>
</tbody>

I want the second sheet to bring row automatically when status in the above table is changed to closed.

CLOSED ACCOUNTS

COMPANY	ACCOUNT	BANK	STATUS
0001	45668555	BARC	CLOSED
0002	45684569	HSBC	CLOSED
0002	45867125	HSBC	CLOSED

<tbody>
</tbody>

So whenever an account is given status CLOSED in the table (Sheet 1) excel should create a new row in the table in Sheet 2 and copy the row from sheet1.

Hopefully it makes sense. Please help me on this guys.
Thanks

 

[Momentman]

On sheet1 or the sheet tha has the raw data from where it would be copied, right click the Tab and select view code, paste the following code in there and see if it does what you want

1. I assumed the data starts at column A
2. the sheet that it is copied to is named sheet2, change as necessary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wks2 As Worksheet
    Set wks2 = Worksheets("Sheet2") 'change as necessary
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    If Not Intersect(Target, Range("D2: D" & lastrow)) Is Nothing Then
        If Target.Value = "CLOSED" Then
            Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy
            wks2.Activate
            lastrow2 = wks2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
            wks2.Range("A" & lastrow2).Select
            ActiveSheet.Paste
        End If
    End If
            
    
End Sub

 

[Kaiser Khan]

perfect.
thanks

 

[Momentman]

You are welcome