Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to use advanced filter to extract data to respective sheets

    I have created a macro to the category (Col P) and Type (Col Q) based on the data on sheet1

    See my macro below as well as my sample workbook


    It would be appreciated if someone could kindly assist me in extracting data for all sheets created (See Col O) for each category and type


    Code:
     Sub Advanced_Filter_Sheets()
        Sheets(2).Select
        Sheets(1).Columns("A:F").AdvancedFilter Action:= _
            xlFilterCopy, CriteriaRange:=Range("'Sales Retail'!Criteria"), CopyToRange _
            :=Range("A1"), Unique:=False
        Range("D2").Select
    End Sub
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Howard,

    What is it that you are trying to do? Please be explicit. Extract data to where?

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud


    Thanks for the reply

    What I am trying to achieve it to extract each type and category to the applicable sheet

    For Eg Type Sales Cat Retail must be extracted to sheet names Retail Sales using advanced filter , Type Sales Cat Discount must be extracted to sheet Sales Discount etc

    My apologies -P1 should be Type and Q1 Cat on the workbook


    Hope this is clearer. If not I will attach workbook where I have manually used advanced filter

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Howard,

    Here is the code you need. Don't forget to add the headers for each sheet.

    Place in a standard module:
    Code:
    Public Sub MoveData()
    '--------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim I As Long, LastRow As Long, TypeCat As String
    Set ws1 = Worksheets("BR1TB Exc Zero Values & BS")
    '--------------------------
    'CYCLE THROUGH MAIN SHEET AND DETERMINE WHICH SHEET TO SEND DATA TO
    LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LastRow
        TypeCat = ws1.Cells(I, "E") & " " & ws1.Cells(I, "F")
        Set ws2 = Worksheets(TypeCat)
    '--------------------------
    'CYCLE THROUGH COLUMNS AND MOVE DATA
        NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
        For J = 1 To 6
            ws2.Cells(NextRow, J) = ws1.Cells(I, J)
        Next J
    Next I
    End Sub
    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2015-08-08)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks very much for all your effort

Posting Permissions

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