Copy and Paste the Unique Values from Filtered Column

Question

I'm trying to get the Unique values from the Filtered Range and trying to paste the same into specific worksheet. But I'm facing a Run-Time Error 1004 (Database or Table Range is not Valid).

Set DataSet = MainSht.Range(Cells(1, 1), Cells(Lrows, Lcols))

With DataSet
    .AutoFilter field:=3, Criteria1:=Array("Corporate Treasury - US", "F&A"), Operator:=xlFilterValues
    Set DataRng = .Offset(1, 10).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
    .AutoFilter
    With DataRng
    .AdvancedFilter Action:=xlFilterCopy, copytorange:=Wb.Sheets("Corporate Treasury - US").Range("A2"), Unique:=True 'Getting Error Here
    End With
End With

Appreciate your help in advance!!

Answer 1

I believe the error is because it cannot past a range of non-contiguous cells within a column.

I got round this by simply using the .copy command, but this will paste your unique list with the underlying formatting. See my solution below -

> Set DataSet = MainSht.Range(Cells(1, 1), Cells(Lrows, Lcols))
> 
> With DataSet
>     .AutoFilter field:=3, Criteria1:=Array("Corporate Treasury - US", "F&A"), Operator:=xlFilterValues
>     Set DataRng = .Offset(1, 10).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
>     DataRng.Copy Destination:=Wb.Sheets("Corporate Treasury - US").Range("A2:A" & (DataRng.Rows.Count + 2))
> 
> End With

If you do not want to bring across cell properties/formatting from the original worksheet, you could combine the .copy command with a .pastespecial to only paste in values, formulas or whatever details you need.