With advanced filter in Excel using menu while trying to find out Unique, the limitation is that the destination range should be on the same sheet as the source. However, using VBA there is no such limitation. Using VBA the source and destination ranges in Advanced filter need not be on the same sheet.

In VBA, to Copy Values from range A1:A5 to B1 you will generally write

Sheet1.Range("A1:A5").Copy

Sheet1.Range("B1").PasteSpecial xlPasteValues

Another method:

write the following function in the code window 

Sub CopyValues(rngSource As Range, rngTarget As Range)

    rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value

End Sub