+ Reply to Thread
Results 1 to 4 of 4

Using Advance Filter to create multiple Searches under New Worksheets

  1. #1
    Registered User
    Join Date
    05-24-2022
    Location
    Calgary Alberta
    MS-Off Ver
    Office365
    Posts
    2

    Using Advance Filter to create multiple Searches under New Worksheets

    Hi All,

    I am new to VBA and I am looking for some help to complete the following tasks in Excel 365. As you can see my template has 3 tabs which will work together to create the information that I require. The tabs include: 1. InputScreen, 2. Search Template, 3. Raw Data Dump.

    Once I have input all of the required information into the InputScreen Tab I am looking to automate the following tasks in VBA:
    1. Generate an advance filter search in the Search Template based on a raw data search from the Raw Data Tab
    2. Copy the search results into a new tab at the end of the sheets
    3. Rename the new tab to the name of the cell referenced from the InputScreen tab
    4. Update the formula reference to the next cell on the Input Screen within the Search Template and generate a new Advance filter search
    5. Create a do loop or If loop to repeat this process over and over again based on search data within the InputScreen until the cell is empty.

    Please see the file attached for an indication of what I am trying to do. I have attached some of the VBA code that I am prepared to set the stage but it is pretty basic.

    Any guidance that you can provide would be great.

    ExceloratorAdam
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Using Advance Filter to create multiple Searches under New Worksheets

    Your data, search criteria column headings need to match. Suggest you run tests using the advanced filter, and once it works to your satisfaction, repeat the process while recording a macro.

  3. #3
    Registered User
    Join Date
    05-24-2022
    Location
    Calgary Alberta
    MS-Off Ver
    Office365
    Posts
    2

    Re: Using Advance Filter to create multiple Searches under New Worksheets

    As an update I had to delete a number of columns on the attachment in order to fit within the max file size of 1000kb.

    Based on my requirements can anyone recommend how I would set up my Dim function to address all of the variables in the code? Are Worksheets, Sheets or Sheet all considered the same classification or are they different?

    Here is the code that I have set up so far:
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Sheets("Search template").Select
    ws("Search template").Copy after: ws (sheet.Counts)
    Sheets("InputScreen").Range("A5").Copy
    Sheets("Search template (2)").Name PasteSpecial
    Range("D1").Select
    Sheets("Search template").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(""="",""*"",InputScreen!R[2]C[-1],""*"")"
    Range("B3").Select
    Application.CutCopyMode = False
    Sheets("PALMS download").Range("B5:BH34100").AdvancedFilter Action:= _
    xlFilterCopy, CriteriaRange:=Range("A2:H3"), CopyToRange:=Range("A10:M10") _
    , Unique:=False
    Sheets("Search template").Select
    Sheets("Search template").Copy Before:=Sheets(3)
    Sheets("Search template (2)").Select
    Sheets("Search template (2)").Move Before:=Sheets(5)
    Sheets("Search template (2)").Select
    Sheets("Search template (2)").Name = "MOC 529"

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Using Advance Filter to create multiple Searches under New Worksheets

    Possibly something like this...
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advance filter multiple not query
    By faodavid in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-26-2019, 11:07 AM
  2. [SOLVED] VBA code that searches multiple worksheets in a workbk and returns specific data to sheet
    By SKooLZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2015, 03:30 AM
  3. How to Create Advance Filter
    By Patnaik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-02-2015, 08:43 PM
  4. Macro for advance filter to create separate worksheet and workbook based on criteria
    By bossrockzz in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-18-2014, 02:36 PM
  5. Advance Filter with Multiple Criteria
    By Qppg in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-18-2012, 05:46 PM
  6. Create combobox list from autofiltered range without advance filter.
    By GravityInvert in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2008, 04:22 AM
  7. How do I advance filter to multiple locations at once?
    By imills in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-01-2006, 02:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1