Generate a list on another tab, depending on criteria in excel

Question

I'm searching a way to get a table generated out of a table entries.

I already found a solution that almost fit my needs but I need it to be more "dynamic" I want that if I add a line on the first table it will be added automatically on the second page.

Here is a link to my OneDrive shared file maybe someone would help me figure it out ?

Christofer Weber · Accepted Answer · 2019-07-02T07:00:11.240

I'm not fond of functions. Here, take a macro instead (You tagged it VBA):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, inputRange As Range
    Set inputRange = ActiveSheet.Range("A:D")   'Code only executes when a change occurs within this range

    If Not Application.Intersect(Range(Target.Address), inputRange) Is Nothing Then
        Set ws1 = Worksheets(2) 'Can also be written Worksheets("Brand X")
        Set ws2 = Worksheets(3) 'Can also be written Worksheets("Brand Y")
        Set ws3 = Worksheets(4) 'Can also be written Worksheets("Brand Z")
        Call splitList("X", ws1)  'The string to search for always in UPPERCASE.
        Call splitList("Y", ws2)
        Call splitList("Z", ws3)
    End If

End Sub
Private Sub splitList(ByVal brand As String, outSheet As Worksheet)
    Dim entry As Range, oCN As Long, i As Long, brandCol As String, searchRange As Range

    brandCol = "C"                              'Column where brand name is located
    oCN = Columns(brandCol).Column
    Set searchRange = ActiveSheet.Range(brandCol & "2:" & brandCol & ActiveSheet.Cells(Rows.Count, oCN).End(xlUp).Row)    'Where to look for Brand name
    outSheet.Range(brandCol & "2:" & brandCol & outSheet.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).EntireRow.Value = ""    'Clear old list
    For Each entry In searchRange
        If UCase(entry.Value) = brand Then
            outSheet.Range(brandCol & outSheet.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).EntireRow.Value = entry.EntireRow.Value   'Write new list
        End If
    Next entry
End Sub

In excel, right click the first (Products) tab and "show code". Paste the code, and done.

In action:

Generate a list on another tab, depending on criteria in excel

1 Answers1