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 =)
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 =)
VBA Code:
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