Results 1 to 3 of 3

Advanced Filtering, variable criteria range

Threaded View

  1. #1
    Registered User
    Join Date
    05-24-2019
    Location
    Czech Republic
    MS-Off Ver
    2007
    Posts
    1

    Advanced Filtering, variable criteria range

    Hello everyone.

    I'm trying to filter a table based on a criteria and copy&paste the result to a different sheet.

    Basically I have a huge amount of data stored in one sheet("Department ERP") and I need to filter column("GLO_MASS_LINE") based on a criteria, then copy&paste each of the results to a different sheet.

    Since AutoFilter and subsequent copy&paste option is too slow, I decided to go for Advanced Filtering. I prepared a huge range of sheets (from sheets 11 to 38), where I want to put details for specific costs (for example, I want to filter the table stored in "Department ERP") for Employee education and copy&paste the result into sheet("EDUC") = sheet no. 11), then I want to filter "Events/Relationship marketing" and copy&paste the result to sheet("ERMA"), etc etc...)

    So far, I have this code:

    Sub GetData2()
    Dim wbData As Range
    Dim wbCriteria As Range
    Dim wbExtract As Range
    Dim i As Integer
    Dim GLO2 As Integer
    
    Application.ScreenUpdating = False
    
    GLO2 = 21
    i = 11
    
    Set wbData = Worksheets("Department ERP").Range("A:P")
    
    For GLO2 = 21 To 48
    Worksheets("Inputs").Activate
    Set wbCriteria = Worksheets("Inputs").Range(Cells(4, GLO2), Cells(5, GLO2))
    
    Worksheets(i).Activate
    
            wbData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=wbCriteria, CopyToRange:=Worksheets(i).Range("A2"), Unique:=False
    
        i = i + 1
      Next GLO2
    
    
    Application.ScreenUpdating = True
    End Sub
    The problem I am now facing is that the code loops through sheets and filters the data, but only for the first criteria (the criteria is still the first one "Employee education").

    Would you help me find the problem here? Any help would be highly appreciated.

    Thank you.
    Last edited by Handkerchief; 05-24-2019 at 10:03 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advanced Filtering where two or more criteria have to be met
    By BigStevie1973 in forum Access Tables & Databases
    Replies: 2
    Last Post: 04-25-2019, 06:09 PM
  2. Advanced filtering with a criteria list
    By jimmy13 in forum Excel General
    Replies: 4
    Last Post: 12-19-2013, 01:58 PM
  3. Replies: 3
    Last Post: 06-26-2010, 02:43 PM
  4. Excel 2007 : Advanced Filtering criteria
    By rocu in forum Excel General
    Replies: 1
    Last Post: 11-14-2008, 01:39 AM
  5. Advanced Filtering with multiple criteria
    By drumbody in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2007, 08:51 AM
  6. Advanced Filter VB Script for Variable Criteria Range
    By Jason in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2006, 02:15 AM
  7. Advanced Filtering - Computed Criteria
    By KIM in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-28-2005, 05:06 PM

Tags for this Thread

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