VB Code only writing one row to Results tab.

[Galland]

Desired results is to read from the criteria tab

Find the corresponding match in the Data tab

And write the results in the Dest Tab

My problem is my VB script is only writing one value and not all of them.

Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Set wsData = Sheets("Data")
Set wsCriteria = Sheets("Criteria")
Set wsDest = Sheets("Dest")


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:=1, Criteria1:=cell.Value

wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")
wsDest.UsedRange.Columns.AutoFit
End With

Next cell
wsData.AutoFilterMode = False
wsData.Activate
Application.ScreenUpdating = True
End Sub

Data Tab

name	age	city	state	zip	cell	desk
Bob	20​	Jackson	NY	12345​	123​	234​
ZZZXCarol	21​	Hazard	KY	12346​	123​	234​
Danielle	22​	London	TN	12347​	123​	234​
Steve	23​	Pineville	Mi	12348​	123​	234​
123Jen456	24​	Somerset	WY	12349​	123​	234​
Naresh	25​	Manchester	PA	12350​	123​	234​
Ron	26​	Monticello	VA	12351​	123​	234​
Sam XXX	27​	Albany	UT	12352​	123​	234​
Vinny	28​	Morehead	AL	12353​	123​	234​

Criteria TAB

name

Bob

Carol

Danielle

Steve

Jen

Naresh

Ron

Sam

Vinny

Dest TAB

name	age	city	state	zip	cell	desk
Vinny	28​	Morehead	AL	12353​	123​	234​

 

[JoeMo]

Looks like you are always pasting the results to the same cell on the destination tab, thereby overwriting previous entries. You need to advance the destination cell for the paste each time you loop. Try changing the With-End With block to something like below:

Dim NxtCell As Long
With wsData.Range("A1").CurrentRegion
    .AutoFilter field:=1, Criteria1:=cell.Value
    NxtCell = wsdest.Cells(Rows.Count, "A").End(xlUp).Row + 1
    wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsdest.Range("A" & NxtCell)
    wsdest.UsedRange.Columns.AutoFit
End With

 

[Galland]

thanks Joe that was very helpful!
Is there a way to address the wildcard filter so I would get the results for 123Jen456 as Jen?

 

[JoeMo]

thanks Joe that was very helpful!
Is there a way to address the wildcard filter so I would get the results for 123Jen456 as Jen?

You are welcome- thanks for the reply.

Sorry, I have no idea what your question is referring to. Did I miss something in your OP?

 

[Galland]

No you didnt miss anything I failed to mention that as part of the result.

I need to see if I can filter the Data list based on the criteria list and write to Dest tab.
What I ended up missing was the names that were embedded within other text like 123Jen456 didnt get written to the Dest tab.

 

[JoeMo]

No you didnt miss anything I failed to mention that as part of the result.

I need to see if I can filter the Data list based on the criteria list and write to Dest tab.
What I ended up missing was the names that were embedded within other text like 123Jen456 didnt get written to the Dest tab.

Try changing this:

With wsData.Range("A1").CurrentRegion
       .AutoFilter field:=1, Criteria1:=cell.Value

To this:

With wsData.Range("A1").CurrentRegion
       .AutoFilter field:=1, Criteria1:= "*" & cell.Value & "*"

 

[Galland]

Joe thanks this worked great!
one thing when it writes the results to Dest tab it always includes the column headers from the Data tab, anyway to just write the results from the filter and not the header too?

 

[JoeMo]

Joe thanks this worked great!
one thing when it writes the results to Dest tab it always includes the column headers from the Data tab, anyway to just write the results from the filter and not the header too?

Untested, but try changing this:

wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsdest.Range("A" & NxtCell)

to this:

wsData.Range("A1").CurrentRegion.Offset(1,0).SpecialCells(xlCellTypeVisible).Copy wsdest.Range("A" & NxtCell)

 

[Galland]

Joe this worked perfect thank you so much for your help!!

 

[Galland]

Joe this worked perfect thank you so much for your help!!

When finished, the Dest tab contains the name 123Jen456 from the Data tab since it matched Jen in the Criteria tab.
Is it possible to return the Jen from the Criteria tab when it finds the match of 123Jen456in the Data tab?
If not its no big deal since I will have all my matching rows.