Hi,

I have a macro autofilter records the column M where it has a value of #N/A.
after filtering the records i wanted to copy the cells/column to another worksheet as temporary
then remove the duplicate records. after removing duplicate records i have to copy the final result to mastersheet, find the last empty row of column Y then dump the records.

Here is my vba code and sample result.

Sub CheckNA()
    Dim wb As Workbook
    Dim ws As Worksheet, ws1 As Worksheet
    Dim Rng As Range
    Dim lrow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("1.reference")

    'Filter Item Type
    With ws
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set Rng = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        Rng.AutoFilter Field:=12, Criteria1:=Array("#N/A")

        Rng.Offset(0, 3).SpecialCells(xlCellTypeVisible).Copy
        'Rng.AutoFilterMode = False
    End With
         
    'Transfer records then remove duplicate Locations
    Set ws1 = wb.Sheets("Sheet3")
    With ws1
        .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).RemoveDuplicates 1, xlYes
    End With
End Sub
sample data

1. AutoFiltered records sample data

ITEMTYPE | ITEM |WHSE | LOCATION | REMARKS
----------------------------------------------
PART1------PARTS---IUS-----PH---------#N/A
PART1------PARTS---IUS-----PH---------#N/A
PART2------PARTS---IUS-----WS---------#N/A
PART2------PARTS---IUS-----WS---------#N/A


2. copy to another worksheet and remove duplicate records base on LOCATION

RESULT
ITEMTYPE | ITEM |WHSE | LOCATION
------------------------------------
PART1------PARTS---IUS-----PH-------
PART2------PARTS---IUS-----WS-------


3. copy those removed duplicate to the master worksheet

RESULT
DATE | ITEM | WHSE | LOCATION
-----------------------------------
8-Aug ------PARTS---IUS-----PH-----
8-Aug ------PARTS---IUS-----WS-----