Find and paste data into seperate sheet [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I need a macro that will search a specific range on one excell sheet--say G7:G56 and J7:J56 for a specific name--if it finds the name i want to copy and paste that into another excell worksheet--then look for another name and another pasting them in the order it finds them. Any help would be greatly appreciated.

Thanks

2 replies

Here is what i came up with:
Sub Copy()
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long
Dim dRow As Long
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("G75").End(xlUp).Row
If Cells(sRow, "G") Like "*Ehlenfeldt*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G")

End If
Next sRow
Sheets("Sheet1").Select
For sRow = 1 To Range("J75").End(xlUp).Row
If Cells(sRow, "J") Like "*Ehlenfeldt*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G")
End If
Next sRow
Sheets("Sheet1").Select
For sRow = 1 To Range("G24").End(xlUp).Row
If Cells(sRow, "G") Like "*Tom*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G")
End If
Next sRow
MsgBox sCount & " Least Senior copied", vbInformation, "Transfer Done"



End Sub

I need it to continue to look for (unique) names and enter them in order on another sheet. I am looking in G and J columns. I have 200+ names that i need to search for and then enter the top 20 into another worksheet. We need them to be in order of search--it seems to work ok but i will have to copy and write the macro to search for all names wanted 200+. Is there an easier way? Can i eliminate certain names if the are found on the worksheet I'm pasting too? Can i search a different workbook also for more filtering?--so if they are found on another workbook they are not pasted either? Final question--how would i set this macro to run when a button is pushed--basically link it to a button in the worksheet?
Thanks so much---i am new to VB.
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
your requirement is not clear to me. Do you want the unique names from a list
then use advance filter to get the unique name in the list. I am giving you a trivial example
the data is like thsi from A1 down
heading
a
s
d
f
d
s
a
now highlight these cells clclik data menu-fitler-advancaefilter
in the advance filter window
choose "copy t another location" (at the top)
lit range is already filled up as A1:a8
leave criteria range empty
copy to : some where down the data e.g A15
tick "unique data only " at the bottom
clcik ok

you will get A15down
heading
a
s
d
f

I do not know is this what you want.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
where are the list of names those have to be found or are all the unique names to be consdered?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!