Unique Advanced Filter to new sheet using dynamic range

**pauldaddyadams** · 12-18-2015, 05:47 PM

Hi,

I have spent some time googling this with no success. I would like to alter this:

Sub FilterCopyToOtherSheet()
    Sheets("Raw Data").Range("B1:B12").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("raw data").Range("B1:B12"), _
        CopyToRange:=Sheets("Risk Summary").Range("D7"), _
        Unique:=True
 
End Sub

End Sub

So it automatically find the last row based on on column b. This will help with one part of my problem in the post:

HTML Code:

Can anyone help?

**MarvinP** · 12-18-2015, 05:51 PM

Perhaps,

Sub FilterCopyToOtherSheet()
    '
    Dim LastBRow As Double
    LastBRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Sheets("Raw Data").Range("B1:B" & LastBRow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("raw data").Range("B1:B2"), _
    CopyToRange:=Sheets("Risk Summary").Range("D7"), _
    Unique:=True

 End Sub

Also, make the moderators happy and edit your post above, select your code and click on the "#" which will put "code tags" around your post and look more like mine.

Also - sometimes you need to "ClearContents" of the range you are copying your data to.

**pauldaddyadams** · 12-18-2015, 05:56 PM

Hi,

Thanks - I corrected after I posted but thanks for the heads up.

I have tried your code but I get a run time error 1004. Is it something I am doing wrong?