Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Vba code to apply multiple autofile

    Hi Experts,
    Is this possible to apply filter on 2 columns and den move the filter data into a new sheet. The column header is fixed on which the auto filter has to be applied.
    Sample file attached for your reference.
    Regards,
    JD
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jaggi,

    IMHO, this is more a case where you should use an Advanced Filter which is designed to to exactly this job.

    To use an advanced filter I find it best to define three range names:
    Database - Your raw data.
    Criteria - The selection criteria for the data to be extracted.
    Extract - The range where the data is copied to (this only includes the data headers!)

    JaggiRanges.PNG

    The reason for using these three names is that Excel will recognize them when you start the advanced filter and automatically use their
    defined cell ranges.

    Take note that I used a Dynamic Range name for Database so you can add rows to the data without making changes (up to row 10,000).

    The Criteria range must include at least two rows:
    Row 1 - The EXACT column names used in the Database (I prefer to use cell references to avoid any misspellings or extra spaces.)
    Row 2...n - Is where you enter the values you are matching on, you only need to fill in those you need. You can have more rows if you are going to have OR conditions.

    The Extract range has only column headings for the data you want to extract. And is only ONE Row!

    I like to start the Advanced Filter by placing my cursor on one of the values in the Criteria range then going to Data -> Advanced (in the Sort & Filter section).

    JaggiAdvFilter.PNG

    Results:
    JaggiResults.PNG

    Test File: Jaggi Sample_file.xlsm

    Almost forgot I included a macro to run the extract once the Criteria are specified although you could have the macro prompt for the criteria if you wish.
    Code:
    Sub MyAdvFilterAndCopy()
    
        Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
            ("Criteria"), CopyToRange:=Range("Extract"), Unique:=False
            
    End Sub
    HTH
    Last edited by RetiredGeek; 2016-02-16 at 08:25.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Jaggi (2016-02-16)

  4. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi RG,

    Really nice approach!

    Regards,
    JD

Tags for this Thread

Posting Permissions

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