code copy & paste filtered data doesn't work well

[abdelfattah]

hello
i have this code issuing by mr. mumps and i amended what i need but it copy incorrectly when i choose specific client it doesn't all data relating the client
main data:

xlfilter.xlsm
	A	B	C	D	E	F	G	H
1		data
2	it	date	client	inv no	total	old	new	net
3	1	12/5/2020 12:00 ص	hossan	110	500	250	700	5,642
4	2	13/5/2020 12:00 ص	mohammed	111	600	230	500	2,134
5	3	14/5/2020 12:00 ص	yosef	112	700	500	500	1,654
6	4	15/5/2020 12:00 ص	hossan	113	500	400	300	2,145
7	5	16/5/2020 12:00 ص	hossan	114	600	525	200	151
8	6	17/5/2020 12:00 ص	mohammed	115	610	597	80	25,142
9	7	18/5/2020 12:00 ص	yosef	116	620	669	12	452
10	8	19/5/2020 12:00 ص	hossan	117	630	741	2	5,412,415
11	9	20/5/2020 12:00 ص	hossan	118	640	813	5,412	154,124
12	10	21/5/2020 12:00 ص	mohammed	119	650	885	51,215	125
13	11	22/5/2020 12:00 ص	yosef	120	660	957	45,412	2
14	12	23/5/2020 12:00 ص	hossan	121	670	1,029	452	1,245,124
15	13	24/5/2020 12:00 ص	hossan	122	680	1,101	54	42,154
sheet1

the wrong result

xlfilter.xlsm
	A	B	C	D	E	F	G	H	I	J
2	client		it	date	client	inv no	total	old	new	net
3			1	12/5/2020 12:00 ص	hossan	110	500	250	700	5,642
4	hossan		2	13/5/2020 12:00 ص	mohammed	111	600	230	500	2,134
5
sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim fnd As Range, LastRow As Long, lRow As Long, bottomC As Long
    bottomC = Sheets("sheet1").Range("C" & Rows.Count).End(xlUp).Row
    LastRow = Sheets("sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow = Sheets("sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("c3:j" & lRow).ClearContents
    Set fnd = Sheets("sheet1").Range("C:C").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        If fnd.Row = bottomC Then
            lRow = LastRow
        Else
            lRow = Sheets("sheet1").Range("C" & fnd.Row + 1 & ":C" & LastRow).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row - 1
        End If
        With Sheets("sheet1")
         .Range("a" & fnd.Row & ":a" & lRow).Copy Cells(Rows.Count, "c").End(xlUp).Offset(1)
        .Range("b" & fnd.Row & ":b" & lRow).Copy Cells(Rows.Count, "D").End(xlUp).Offset(1)
        .Range("c" & fnd.Row & ":c" & lRow).Copy Cells(Rows.Count, "e").End(xlUp).Offset(1)
        .Range("d" & fnd.Row & ":d" & lRow).Copy Cells(Rows.Count, "f").End(xlUp).Offset(1)
        .Range("e" & fnd.Row & ":e" & lRow).Copy Cells(Rows.Count, "g").End(xlUp).Offset(1)
        .Range("f" & fnd.Row & ":f" & lRow).Copy Cells(Rows.Count, "h").End(xlUp).Offset(1)
        .Range("g" & fnd.Row & ":g" & lRow).Copy Cells(Rows.Count, "i").End(xlUp).Offset(1)
        .Range("h" & fnd.Row & ":h" & lRow).Copy Cells(Rows.Count, "j").End(xlUp).Offset(1)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

What exactly are you trying to do?

 

[abdelfattah]

hi, fluff when i write in cell a4 in sheet 2 the name based on column c in sheet1 then copy data relating the name to sheet2

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A4" Then
      Range("C2:J10000").ClearContents
      With Sheets("Sheet1")
         .Range("A2:H2").AutoFilter 3, Target
         .AutoFilter.Range.Copy Me.Range("C2")
         .AutoFilterMode = False
      End With
   End If
End Sub

 

[abdelfattah]

very effective your code many thanks

 

[Fluff]

You're welcome & thanks for the feedback.