Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wsd As Worksheet
Dim wsa As Worksheet
Dim wsc As Worksheet
Application.ScreenUpdating = False
Set wsd = Sheets("Data")
Set wsa = Sheets("Active")
Set wsc = Sheets("Closed")
If Not Intersect(Target, Range("D2:D65536")) Is Nothing Then
If Target.Value = "Active" Or Target.Value = "active" Then
erow = Target.Row
MsgBox erow
numberofrows = wsa.Range("A65536").End(xlUp).Row
For i = 1 To numberofrows
If wsd.Cells(erow, 1) = wsa.Cells(i, 1) Then
Exit Sub
End If
Next
wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsa.Range("A" & numberofrows + 1)
wsa.Activate
ActiveSheet.Range("A2:D" & numberofrows + 1).Select
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("C2:C" & numberofrows + 1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Active").Sort
.SetRange Range("A2:D" & numberofrows + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
wsd.Activate
Else
If Target.Value = "Closed" Or Target.Value = "closed" Then
erow = Target.Row
numberofrow = wsc.Range("A65536").End(xlUp).Row
For i = 1 To numberofrow
If wsd.Cells(erow, 1) = wsc.Cells(i, 1) Then
Exit Sub
End If
Next
wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsc.Range("A" & numberofrow + 1)
wsc.Activate
ActiveSheet.Range("A2:D" & numberofrow + 1).Select
ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Add Key:=Range("C2:C" & numberofrow + 1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Closed").Sort
.SetRange Range("A2:D" & numberofrow + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
wsd.Activate
End If
End If
End If
End Sub