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

You could extract the remaining records with an Advanced Filter. There
are instructions he

http://www.contextures.com/xladvfilter01.html#ExtractWs

Set up a criteria area with five columns, each with the heading from
field 5, and in the row below, each of the criteria you listed, e.g.

Code Code Code Code Code
<03881DCACAUA <40006DCUV <03881DCEAUA <A2630DCACAUA <A2630DCEAUA

Then, record a macro as you filter the data to the new worksheet.

monica wrote:
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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html