Find and paste data into seperate sheet
Closed
Jim Jones
-
Nov 2, 2009 at 08:56 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 3, 2009 at 09:14 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 3, 2009 at 09:14 PM
Related:
- Find and paste data into seperate sheet
- Little alchemy cheat sheet - Guide
- Google sheet right to left - Guide
- Tmobile data check - Guide
- How to transfer data from one sheet to another in excel using formula - Guide
- Cmd network commands cheat sheet - Guide
2 responses
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 2, 2009 at 07:57 PM
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?
Nov 3, 2009 at 09:14 PM
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.