Find and paste data into seperate sheet

Closed
Jim Jones - Nov 2, 2009 at 08:56 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 3, 2009 at 09:14 PM
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 3, 2009 at 09:14 PM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 2, 2009 at 07:57 PM
where are the list of names those have to be found or are all the unique names to be consdered?
0