VBA Copy Unique Values from Column with Blanks

[GingerMermaid]

Hello Amazing Excel Gurus,

I have been struggling with this one for far too long and I still can't figure out what I'm doing wrong. I will try to be as clear as possible:

My spreadsheet has a list of user numbers in Column I. The length of data varies each month and there are duplicate user numbers listed.
Column M is filled with a "Y" or "N" in each row, indicating whether the user number should be counted or not.
Column N has a formula dragged down to populate it with the user number (minus the last digit) if the user should be counted: =IF(M2="Y",LEFT(I2,LEN(I2)-1),"")
This means that Column N appears to have many blanks, since not all users should be counted, but it also has some duplicates, since some users should be counted more than once.
Example:

I	J	K	L	M	N	O
USER	PST DATE	Debit	Credit	Count User?	Count	Don't Count
1235	5/24/2016	D		N		123
1235	5/2/2016		C	Y	123
3215	5/2/2016		C	N		321
5555	5/2/2016		C	N		555
7095	5/2/2016	D		N		709
4565	5/2/2016		C	Y	456
1235	5/2/2016	D		Y	123
16165	5/2/2016		C	N		1616

<tbody>
</tbody>

I am trying to find a way to select all the Counted users and copy the unique ones over to Column P. I have searched for answers and I came up with 2 methods that seemed to make sense to me, but the first is giving me a Runtime Error '1004' and says that it requires at least two rows of data and the second says the extract range has a missing or illegal field name:

TRIAL METHOD 1:
With ActiveSheet

.Range("N1", Range("N1").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True
End With

TRIAL METHOD 2:
With ActiveSheet

.Range("N1:N" & Range("N" & Rows.Count).End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True

End With

Any assistance would be much appreciated and thank you in advance for even taking the time to consider my problem!

 

[oldbrewer]

user	YES_NO	count
1234	N
1235	N
1236	Y	123
1237	N
1238	N
1294	Y	129
1234	N
1235	N
1236	Y	123					Count of count
1237	N						count	Total
1238	N						123	2
1279	Y	127					127	1
1234	N						128	1
1235	N						129	1
1236	Y						Grand Total	5
1237	N
1238	N
1289	Y	128
							a pivot table lists in col 1 only the unique numbers
							easy to get total of 4 with a formula
		problem statement				but is this what you want ?
		count only 123,127,128,129

<colgroup><col span="7"><col><col><col span="5"></colgroup><tbody>
</tbody>