Is there a way of copying data from one worksheet to another automatically?

[konskrypt]

Hi Guys.
I have a worksheet on which I keep my sales accounts.
It looks something like this:-
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Created[/TD]
[TD]Project[/TD]
[TD]Type[/TD]
[TD]Date for Action[/TD]
[TD]Current Action[/TD]
[TD]Status[/TD]
[TD]Status Changed[/TD]
[TD]Value[/TD]
[TD]Commission[/TD]
[TD]Turnaround[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]4 April 2018[/TD]
[TD]*******[/TD]
[TD]Retail[/TD]
[TD]4 April 2018[/TD]
[TD]Call[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see in Column G i have a status cell, the states are Active, Sold, Closed and On Hold.
What I would like to do is copy the row from the main sheet (named Leads) to a worksheet based on the status cell. Each status having their own worksheet.
I don't want the infomation removing from the Leads sheet just copying. I would like this to be done automatically as the status cell is changed to whatever it is currently. Also if the status changes from active to closed for instance I would like the infomation removing from the Active worksheet and put in the Closed worksheet.

I hope I have explained myself.
If not please feel free to berate me

Thanks in Advance

 

[JLGWhiz]

For this code to work properly, you need to be sure that your status entries are identical to the corresponding sheet names, since sheet names are case sensitive.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        On Error Resume Next
        Target.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, 1).End(xlUp)(2)
        On Error GoTo 0
    End If
End Sub

Copy this event code into the sheet code module where status entries are made. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. Make sure your workbook is saved as a macro enabled workbook to preserve the code. Close the vb editor and the code will run when you make can changes to the worksheet. It will only execute the copy action if the change is in column G.

 

[konskrypt]

Thats great thank you. However is there a way of removing the row from one sheet when the status is changed? Lets say from Active to Closed. What I mean is that when the account is opened on the main worksheet it is active so will appear on the secondary active worksheet, but when i change the account status on the main worksheet to lets say closed, it removes it from the secondary active worksheet to the appropriate worksheet?

 

[JLGWhiz]

Thats great thank you. However is there a way of removing the row from one sheet when the status is changed? Lets say from Active to Closed. What I mean is that when the account is opened on the main worksheet it is active so will appear on the secondary active worksheet, but when i change the account status on the main worksheet to lets say closed, it removes it from the secondary active worksheet to the appropriate worksheet?

It can be done, but I am not sure what to look for on the secondary sheet. I can identify the sheet for the last status change by using the Undo feature, but then finding that exact entry is a challenge since Smith could have multiple entries for the same date on the same project. You would need a unique transaction code of some type to identify the correct row to remove from the secondary sheet.