across like 20 sheets in same work book i need to sort 2 columns in ascending order and then remove duplicates based on 1,2 and 7th columns..i have written something like this..but i am getting Autofilter method of range class failed error..can someone check the code pls:
Sub Macro1()
'
' Macro1 Macro
Dim lastrow As Long
Dim ws As Worksheet
For Each ws In Sheets
ws.Activate
If ws.Name Like "ML-*" = False Then
With Range("A2").CurrentRegion
.AutoFilter
.Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("B2"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
'ThisWorkbook.Worksheets("Sheet1")
With ws
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
.Range("A1:J" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 7), _
Header:=xlYes
End With
End If
Next
End Sub
Bookmarks