Advancedfiltering To Another Worksheet

  • 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

    Worksheets("Sheet1").Range("E7:E65536").AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Worksheets("Sheet4").Range("B3"), _
  • 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)

  • 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.




    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Advancedfiltering To Another Worksheet

    I misread,

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!