Try this:
Sub Macro2()
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Main")
.Range("A3").AutoFilter field:=4, Criteria1:="Y"
.AutoFilter.Range.Offset(1).Copy Sheets("New Cust").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData
For i = 7 To 9
.Range("A3").AutoFilter field:=i, Criteria1:="X"
.AutoFilter.Range.Offset(1).Copy Sheets(.Cells(3, i).Text).Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData
Next i
For i = 4 To .Range("A" & Rows.Count).End(xlUp).Row
Select Case .Cells(i, 6).Value
Case "BS", "PK", "PB", "PD"
.Cells(i, 1).Resize(, 13).Copy Sheets("Lisa").Range("A" & Rows.Count).End(xlUp)(2)
End Select
Next i
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
Bookmarks