Autofilrer copying cells/column to another worksheet in VBA

[Jovillanueva]

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.

Please Login or Register  to view this content.

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-----

[Jovillanueva]

Here is my codes and so far it is working. my concern is i got an error in this portion of codes Rng.AutoFilter Field:=12, Criteria1:=Array("#N/A") .

Error message : auto filter Method of range class failed

May I ask assistance on how to solved this issue. thanks.

Please Login or Register  to view this content.

[AB33]

Your range is limited to column A while you are trying to filter column m

Please Login or Register  to view this content.

[Jovillanueva]

Thank you very much. I did not notice. Its working now.