Advanced Filter with dynamic CopyToRange

    Hello Everyone,


    Using Advanced Filter

    How would you make the CopyToRange dynamic?

    Per the code below it works fine, but instead of a static "J7:CF7" I need CF to be able to expand to the furthest right column in Row 7 that holds a value.

    For instance sometimes it is J7:CF7, othertimes it is J7:FD7

    Code
    Sheet19.Range("F7").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet16.Range("J1:AJ2"), CopyToRange:=Sheet16.Range("J7:CF7"), Unique:=False

    Thanks for any help with this

    How about

    Code
    Sub sleeplol()
   Dim Rng As Range
   
   With Sheet16
      Set Rng = .Range("J7", .Cells(7, Columns.Count).End(xlToLeft))
   End With
   Sheet19.Range("F7").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet16.Range("J1:AJ2"), Rng, False
End Sub

    Darn, it does work lol


    I made a dummy workbook to send you; when I tested it, your macro works perfectly. Even when expanding the CopyToRange; awesome!


    But for some reason it's not working with my actual WB. I'll dig into why. Though I'd like to, I can't send the whole book over due to employee info.


    At least I know that your script works though; process of elimination


    As always, thanks again Fluff

Participate now!

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

Register Yourself Login