Hi, I have the below code that filters data and then copies to another sheet. I need this code to be fine tuned, to allow flexibilty with the range with autofilter plus i would like the code to delete the filtered data once it has copied to another sheet
Code:
Sub Macro2()
Dim rng As Range
Dim rng2 As Range
Application.ScreenUpdating = False
Selection.AutoFilter
ActiveSheet.Range("$A$7:$G$745").AutoFilter Field:=2, Criteria1:="=*pi*", _
Operator:=xlAnd
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("NE").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("NE").Range("A8")
End If
ActiveSheet.ShowAllData
Application.ScreenUpdating = True
'
End Sub