VBA Code : Find value and select his row and copy paste it

[Silecya]

Hello everyone,

After researchs I'm stuck on a VBA code and I'm looking for your help.

You will find the process that I want to do :
1. Excel make a research based on a value that is located in a cell and found it (That value is set in a previous userform).
2. Then, he select the row that match to the cell where value is located
3. Once, he select it, he copy past it in an another sheet.

Step 1 is OK and the research works . I have suggestions on the step 2 but it doesn't work. (See VBA code)

Thanks in advance for your help. If you need further explaination don't hesistate to notice me. =)

Silecya =)

Sub Selection_recherche_dossier()

'Declare variables
    Dim ws As Worksheet
    Dim SelectCells As Range
    Dim SelectresearchRow As Variant
    Dim xcell As Object
    Dim Rng As Range
    Set ws = Worksheets("Suivi")
    Set Rng = ws.Range("B16:B36")

'check each cell in a specific range if the criteria is matching
    For Each xcell In Rng
    If xcell.Value = Range("E6") Then
    If SelectCells Is Nothing Then
    Set SelectCells = Range(xcell.Address)
        Else
    Set SelectCells = Union(SelectCells, Range(xcell.Address))
    End If
    End If
 
    Next
'select the cells with specified value
    'SelectCells.Select

[B]'Everthing works until that point

'Select Row and Copy to another worksheet : Suggestion[/B]
    Dim sourceRange As Range
    Dim destrange As Range
    Dim Lr As Long
    Lr = LastRow(Sheets("Affichage Recherche")) + 1
    Set sourceRange = Sheets("Suivi").Cells( _
    ActiveCell.Row, 1).Range("B16:AH36")
    With sourceRange
        Set destrange = Sheets("Affichage Recherche").Range("B" _
        & Lr).Resize(.Rows.Count, .Columns.Count)
    End With
    destrange.Value = sourceRange.Value
    
End Sub

 

[DanteAmor]

Try this

Sub Selection_recherche_dossier()
  Application.ScreenUpdating = False
  With Sheets("Suivi")
    .Range("B15:AH36").AutoFilter 1, .[E6]
    .AutoFilter.Range.Offset(1).Copy
    Sheets("Affichage Recherche").Range("B" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
    .ShowAllData
  End With
  Application.ScreenUpdating = True
End Sub

I have the doubt, in which sheet is the value to be compared of the cell: Range("E6").
If it is on sheet "Suivi", the above code is correct, but if it is on sheet "Affichage Recherche" then change to this:

.Range("B15:AH36").AutoFilter 1, Sheets("Affichage Recherche").[E6]

 

[Silecya]

Hello,

Thanks a lot for your quick answer. That works perfectly for me.

The sheet that you have selected in your 1rst VBA code was the good one

Regards

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.