I need help Copying a row that is returned by a search to another sheet...

[Mr Clickalot]

Below is the current macro that I have, This searches the whole sheet for key words. It is ideal in the fact that it does not have to be exact. When it finds the entry it simply selects it but heres the problem;

Instead of just highlighting the cell I would like it to copy that row to the next sheet (in my case thats called "Bundle")...

Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
For counter = 1 To sheetCount
Sheets(counter).Activate
If IsError(Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate) = False Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
Sheets(currentSheet).Activate
End If
End Sub

Please give suggestions, If you have a better search macro for excel that searches key words and pastes I will be happy to give it a try!

 

[VoG]

Try this

Sub Find_Data()
Dim datatoFind As Variant, Found As Range, LR As Long
With Sheets("Sheet1")
    datatoFind = .Range("A1").Value
    If datatoFind = "" Then Exit Sub
    Set Found = .UsedRange.Find(What:=datatoFind, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
    Exit Sub
Else
    Found.Interior.ColorIndex = 3
    With Sheets("Sheet2")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Found.EntireRow.Copy Destination:=.Range("A" & LR + 2)
    End With
    Application.CutCopyMode = False
End If
End Sub

 

[Mr Clickalot]

Sorry its all sorted now. Thanks for the help....

 

[herukano]

Hi,
I have been searching high and low for a code that would do pretty much what your first suggestion did, thanks !

Could anyone help me adjust it so that instead of just finding the first instance of the search criteria it finds all rows with relevant data and pastes them ?

Code currently using

Sub Find_Data()
Dim datatoFind As Variant, Found As Range, ws As Worksheet, LR As Long
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Search" Then
Set Found = ws.UsedRange.Find(What:=datatoFind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then Exit For
End If
Next ws
If Found Is Nothing Then
MsgBox "Not found", vbExclamation
Exit Sub
Else
With Sheets("Search")
LR = Range("A" & Rows.Count).End(xlUp).Row
Found.EntireRow.Copy Destination:=.Range("A" & LR + 1)
End With
End If
Application.CutCopyMode = False
End Sub


Thanks

 

[herukano]

Anyone ?