Move entire row from Source to new worksheet if value found

[jiujen]

Hi there! I've been exploring Chandoo for an answer to my question but have not yet been able to piece together what I need from what is posted. Thank you for your guidance.


I have a large worksheet called 'Source' with Unit #s in column A. (And related data in columns B:Z)

I have a subset of those Unit #s in Column A of 'Sheet 1'.


I'd like to create a macro to look up the Unit #s in Column A of 'Sheet 1' in Column A of the 'Source' worksheet and return the entire row to a third worksheet. (There may be more than one instance of the Unit number in the 'Source' worksheet and I need all the relevant rows to come over.


Arigato, ninjas!

 

[sdsurzh]

Hi,


Can you please upload the same workbook.


Thanks,

SK

 

[jiujen]

https://docs.google.com/file/d/0B7TunNRjLCLHaFNVNEVfUXBUS1U/edit?usp=sharing


I hope this worked! Thank you so much for your time.

 

[sdsurzh]

Hi Jiujen,


Can you please insert a module and paste the below code and check.


Sub movedata()

Worksheets("sheet1").Select

Range("a2").Select

Do

temp = ActiveCell.Value

Worksheets("source").Select

Range("a2").Select

Selection.CurrentRegion.Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$K$1000").AutoFilter Field:=1, Criteria1:=temp, Operator:=xlAnd

Range("A1000").Select

Selection.End(xlUp).Select

Range(Selection, Range("k1")).Copy

Worksheets("Return rows for Sheet 1 units").Select

Range("A1000").Select

Selection.End(xlUp).Select

Selection.Offset(1, 0).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Worksheets("sheet1").Select

Selection.Offset(1, 0).Select

Loop Until ActiveCell.Value = ""

Worksheets("source").Select

Selection.AutoFilter

Range("A1").Select

Worksheets("Return rows for Sheet 1 units").Select

Rows("2:2").Select

Selection.AutoFilter

ActiveSheet.Range("$A$2:$L$1000").AutoFilter Field:=1, Criteria1:="Veh_No"

Range("A1000").Select

Selection.End(xlUp).Select

Range(Selection, Range("K2")).Select

Selection.EntireRow.Delete

Range("A1").Select

End Sub


Thanks,

SK

 

[DaveTurton]

Another option

[pre]

Sub LocateAndCopy()

Dim Search_Text As String
Dim Cell_Add As String
Dim i As Integer

Application.ScreenUpdating = False
i = 1
Sheets("Sheet1").Select
If Not IsEmpty(Cells(2, 1)) Then
Cells(2, 1).Select
Do
Search_Text = ActiveCell.Value
Cell_Add = ActiveCell.Address
Sheets("Source").Select
Cells(2, 1).Select
Do
If ActiveCell.Value = Search_Text Then
Sheets("Return rows for Sheet 1 units").Range(Cells(i, 1).Address, Cells(i, 26).Address).Value = Range(ActiveCell.Address, ActiveCell.Offset(0, 25).Address).Value
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
Sheets("Sheet1").Select
Range(Cell_Add).Select
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
Else
MsgBox "No search references in Sheet 1"
Application.ScreenUpdating = True
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

[/pre]

 

[sdsurzh]

Hi Jiujen,


You can use Dave code since i have forgot to skip the code if there is no match found.


Thanks Dave.


Thanks,

SK

 

[jiujen]

Dave, Suresh- I can't thank you enough for your generousity. You've saved me hours of work. If you ever need help with a presentation, where you need to convey a strategic, high-level message and create visual representations of your concepts, I would be happy to return the favor. I am a strategy and powerpoint ninja.


Namaste.