[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data

by


Create a list of all the vendors in a separate place and remove duplicates.
Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here’s the code for this, assuming the data you provided above is put in the cells G1:H6.

Sub Macro2()

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("K1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.RemoveDuplicates Columns:=1, Header:=xlNo

    Dim num_vendors
    num_vendors = Selection.SpecialCells(xlCellTypeConstants).Count - 1
    Range("G1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter

For i = 1 To num_vendors
    Range("G1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter Field:=1, Criteria1:=Cells(i + 1, 11).Value

    Range("G1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add After:=Sheets(ThisWorkbook.Sheets.Count)
    Sheets(i + 1).Select
    ActiveSheet.Paste
    Sheets(1).Select
Next i

Application.CutCopyMode = False

End Sub

The first half of the code copies the vendor list and pastes it in K1. Then removes duplicates, determines the number of vendors from the remaining list, then goes back to G1 and enables filtering.

The second half of the code uses a for loop to filter the table for each vendor one by one, copy the visible cells, insert a sheet at the end of all sheets, switch to that sheet, paste the copied data and then go back to the original sheet.

This code should work regardless of the number of vendors of the number of rows for each vendor.

1

solved Excel Vba Filter Range By Each Unique Value and Copy Data