hi.

I am doing an autofiler on the main sheet and copying the
data into new sheets. My criteria's a
1. 03881DCACAUA
2. 40006DCUV
3. 03881DCEAUA
4. A2630DCACAUA
5. A2630DCEAUA

This i work with the below pasted code; which is working
fine. Now my last sheet to be created should contain all
the left over values in the column. LIke apart from the 5
values in the column mentioned above their are some 12-15
more different values. How to copy the remaining ones and
paste in the sheet.


thanks a lot

Sub CopyFilter1()

Dim rng As Range
Dim rng2 As Range

Sheets.Add
ActiveSheet.Name = "AG DCE"
Worksheets(SalesSheetName).Activate
Selection.Autofilter Field:=5, Criteria1:="A2630DCEAUA"
With ActiveSheet.Autofilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Debug.Print rng2.Address
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("AG DCE").Cells.Clear
Set rng = ActiveSheet.Autofilter.Range
rng.Offset(0, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("AG DCE").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub