Copy/paste data using validation box

[redspanna]

Hi all

Can somebody please help with some code for the following.

Search for the name value selected in data validation box of sheet1 cell G10 through column A of sheet 2

Each time the same name is found copy the value in the subsequent columns B,C and D and into next available row of column E,F,G of sheet1

Example

Validation box contains name Mr Smith

Sheet2 contains data:

Mr Jones Blue 4
Mr Smith Blue 5
Mr Smith Green 7
Miss Hue Yellow 9
Mr Smith Yellow 8

Results will show in first available columns E,F,G sheet 1 as:

Mr Smith Blue 5
Mr Smith Green 7
Mr Smith Yellow 8

Any help appreciated and thanks in advance

 

[Trevor G]

All you need is a Vlookup formula in the columns and perhaps surrond it with an IF Statement to keep it blank until you select a name

=if(A1>0,Vlookup,Sheet2Table,2,false,"")

 

[redspanna]

Hi Trevor

Unless I'm misunderstanding your Vlookup suggestion, this will only copy the first instance of the name in cell G10 being found and no others.

So the VB code would loop through all the data held in column A of sheet2 and each time the name is found as chosen in cell G10 of sheet 1 the results are pasted into next available rows of columns EFG of sheet1

Does this make sense?

thanks again

 

[Trevor G]

Perhaps try using a filter based on the cell content then so you can then copy the cells and then paste into the first sheet

Sub Macro2()
'
' This will filter the values
' Run this on Sheet1 making sure you have something in G10
' Extend the filter as necessary
Sheets("Sheet2").Select

Selection.AutoFilter
ActiveSheet.Range("$E$3:$F$21").AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("G10")
Range("E3").Select
ActiveCell.CurrentRegion.Copy
Sheets("Sheet1").Select
Range("E3").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.PasteSpecial xlPasteAll

End Sub

 

[redspanna]

Thanks very much Trevor for your help !