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.
Pecoflyer,
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 am trying to find a formula that will
1> read names from a tab
2> filter the data tab from that list using a wildcard (since names are in cell with other text)
3>Once found write the results of that row into a Results tab.
I have attached a simple example of the spreadsheet.
any help would be greatly appreciated.
I
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))
wsDest.Cells.Clear
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")
wsDest.UsedRange.Columns.AutoFit
End With
Set wsDest = Nothing
Next cell
wsData.AutoFilterMode = False
wsData.Activate
Application.ScreenUpdating = True
End Sub
sknteer,
I tried this code also and it worked great.
If I wanted to write all results to a single destination sheet how would I do that?
