Advancedfiltering To Another Worksheet

Om Avataar · Apr 9th 2007

Hi all,

I just need my VBA code to uniquely filter a column of values (Cells E7:E65536) in Sheet1, and to paste the filtered results into column B (starting at cell B3) in worksheet Sheet4 in the same Excel workbook. Will the code below work?

More importantly, does the AdvancedFilter method allow for the filtered results to be deposited into another worksheet within the same workbook?

Many thanks in advance for any help/advice

Code

Worksheets("Sheet1").Range("E7:E65536").AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Worksheets("Sheet4").Range("B3"), _
    Unique:=True

**Reafidy** · Apr 9th 2007

Re: Advancedfiltering To Another Worksheet

I dont see a problem with that code, when you say will it work does that mean you havnt tried it. Thats the best way to see if it works!

You could perhaps define the range better using end(xlup)

Wigi · Apr 9th 2007

Re: Advancedfiltering To Another Worksheet

Om Avataar

You can't do this at once. You will need to copy the retained row(s) after you performed the Advanced Filter. You could perhaps use the FilterInPlace as Action. After that, copy to the other sheet.

Wigi

**Reafidy** · Apr 9th 2007

Re: Advancedfiltering To Another Worksheet

I misread,

Code

Sub HTH()
    Dim Rng1 As Range
    Set Rng1 = Worksheets("Sheet1").Range("E7", Range("E" & Rows.Count).End(xlUp))
    Rng1.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:="", Unique:=True
    Rng1.Copy Destination:=Worksheets("Sheet4").Range("B4")
End Sub

Advancedfiltering To Another Worksheet

Participate now!

Share