Macro To Sort Data From 2 Columns and Copy to New Worksheet

[kbmitch]

I am new here and have only limited experience with Excel. I have an Excel worksheet that lists cables with the equipment name at the "A" end of the cables in column 2 and the equipment name at the "B" end of the cables in column 7. I am looking to build a macro to search through column 2 then column 7 for a specified equipment name and then copy the rows of data to a new worksheet. I have been successful in building a macro that searches through one column and copies the data to the next sheet but I have not been able to get the macro to go through both columns to extract the data. I have tried everything that I have been able to find on line thus far with no luck. I am using Excel 2010 and the following is the macro that I am presently using.

Private Sub Worksheet Change(ByVal Target As Range)
Dim rng1 As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Set rng1 = Intersect([A:A], Target)
If rng1 Is Nothing Then Exit Sub
Set ws1 = Sheets(2)
Set ws2 = Sheets(3)
Set ws3 = Sheets(4)
ws1.[A:H].ClearContents
ws2.[A:H].ClearContents
ws3.[A:H].ClearContents
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1:H500")
.AutoFilter Field:=7, Criteria1:="Rack #1"
.Copy ws1.[a1]
.AutoFilter Field:=7, Criteria1:="Rack #2"
.Copy ws2.[a1]
.AutoFilter Field:=7, Criteria1:="Rack #3"
.Copy ws3.[a1]
End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub