Filtering the values and removing the duplicates

[sindhuja]

Hi All,

I have used the array for the values which i need to filter. They are "Reason - A", "Type - B"

my requirement is first i need check for both the values in column 42 and if found copy paste into sheet DTA.

Then check again in the column 43 for the same criteria and copy paste the values to DTA sheet provided no duplicates found in DTA sheet.

I have tried with the below code but the results are not as expected.


[vba]

myArr = Array("Reason - A", "Type - B")
Set WSNew = Worksheets.Add(After:=Sheets(ActiveSheet.Index))
WSNew.Name = "DTA"
Worksheets("DTA Details").Activate
For i = LBound(myArr) To UBound(myArr)
With ActiveSheet
.AutoFilterMode = False
.Range("A1:BZ" & .Rows.Count).AutoFilter Field:=42, Criteria1:=myArr(i)
Set rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
CCount = 0
On Error Resume Next
CCount = rng.Columns(1).SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count
On Error GoTo 0
If CCount = 0 Then
MsgBox "There are more than 8192 areas:" _
& vbNewLine & "It is not possible to copy the visible data." _
& vbNewLine & "Tip: Sort your data before you use this macro.", _
vbOKOnly, "Copy to worksheet"
Else
Worksheets("DTA Details").Activate
rng.Parent.AutoFilter.Range.Copy
lastrw = Sheets("DTA").Range("a" & Rows.Count).End(xlUp).Row
MsgBox lastrw
With Sheets("DTA Details").Range("A2:BX" & lastrw)
.Copy
Worksheets("DTA").Cells(Rows.Count, 1).End(xlUp).Offset(lastrw, 0).PasteSpecial xlPasteValues
End With
Application.DisplayAlerts = True

End If
End If
End With
End With
Worksheets("DTA Details").Activate
Next i
[/vba]

Kindly help me out in completing my task by edidting the above code.

-Sindhuja

[patel]

a sample of your file will be helpful

[sindhuja]

As requested, Please find the template attached.

[patel]

are you sure it's the right file ?

[sindhuja]

Sorry for the inconvenience. Pls find the updated sample template.

sindhuja

[patel]

On the attached file is the sheet DTA the aspected result ? if not can You attach it ?

[sindhuja]

From the DTA details we need to filter for the values and the results to be in the DTA sheet. And no duplicates.
Ex in the attached template(previous thread) highlighted in yellow are the dulicates. Because it was already taken into consideration when filtering AP and again on filtering the column AQ it should be duplicated.
-sindhuja