M
monica
hi.
I am doing an autofiler on the main sheet and copying the
data into new sheets. My criteria's are:
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
I am doing an autofiler on the main sheet and copying the
data into new sheets. My criteria's are:
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