Consulting

Results 1 to 6 of 6

Thread: Advance Filter Copy to Range

  1. #1

    Question Advance Filter Copy to Range

    Hello. I am using the following code and it works the first time. The problem is, after it has copied the data to Sheet1, and I run the macro again with a different filter variable it won't repalce the existing data on Sheet1. If I delete the information on Sheet1 and then run the macro, everything is fine. I am puzzled. Any help is appreciated.


    Sheets("Database").Select
    Range("data").Select
    Range("A1:F14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    "K1:K2"), CopyToRange:=Sheets("Sheet1").Range("a1"), Unique:=False

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Brorick,
    You could always put a line like

    Sheets("Sheet1").Cells.Delete
    Above your AdvancedFilter line. Would clear the sheet beforehand
    Matt

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    hi Brorick.
    It looks/works fine to me.
    I would omit the two first lines though and give the criteria-area a name too. (e.g. Crit1)


    Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Crit1"), _
        CopyToRange:=Sheets("Sheet1").Range("a1"), Unique:=False

    br
    Tommy Bak

  4. #4
    Thank you Matt and Tommy. Both of your ideas worked like a charm. I am most greatful. Have a great day.

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by tommy bak
    I would omit the two first lines though and give the criteria-area a name too. (e.g. Crit1)

    Hi Tommy,

    As I'm not too 'up to snuff' on my Filter code, I was wondering if you could explain this a little more. What are the advantages to that? Also, what requirements go along with naming the criteria? Additional steps?

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Hi Firefytr

    The advantage here is that you can omit the 2 selects (Selects are slowing down the code and can course c?nfusion.)

    What I would normally do, is to give the criteria-area (and the database) dynamic names, so that the criteria area automatic expands/contracts, when I input more criteries.

    I will gladly post an example file if you're interested. ( just have to translate it first unless you can read Danish) :-)

    br
    Tommy Bak

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •