Posts by galland

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.


    thanks for the response.

    my intent was to filter the data list based on names from the criteria using a wildcard.

    I wanted to use the criteria Bob Smith and extract the row where Bob Smith was contained in the Inventory cell "Dell Laptop [email protected]" using a wildcard or some function.

    Then return the row Bob Smith and Accounting on the results tab.

    Is that possible?

    I looked at the code from Sktneer and it worked great but was wondering how to modify in order to write all filtered results to one sheet and not multiple?

    Option Explicit

    Sub FilterAndCopyData()

    Dim wsData As Worksheet, wsCriteria As Worksheet, wsDest As Worksheet

    Dim lr As Long

    Dim rng As Range, cell As Range

    Application.ScreenUpdating = False

    Set wsData = Sheets("Data")

    Set wsCriteria = Sheets("Criteria")

    'Assuming the criteria are listed in column A starting from Row2 on Criteria Sheet

    lr = wsCriteria.Cells(Rows.Count, "A").End(xlUp).Row

    Set rng = wsCriteria.Range("A2:A" & lr)

    If wsData.FilterMode Then wsData.ShowAllData

    For Each cell In rng

    With wsData.Range("A1").CurrentRegion

    .AutoFilter field:=3, Criteria1:=cell.Value

    On Error Resume Next

    Set wsDest = Sheets(CStr(cell.Value))


    On Error GoTo 0

    If wsDest Is Nothing Then

    Sheets.Add(after:=Sheets(Sheets.Count)).Name = cell.Value

    Set wsDest = ActiveSheet

    End If

    wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")


    End With

    Set wsDest = Nothing

    Next cell

    wsData.AutoFilterMode = False


    Application.ScreenUpdating = True

    End Sub