insert total row with advanced filter based on two date and name's client

[abdelfattah]

hi
i have this code it brings data from sheet1 to sheet 2 based on two date and name's client as in sheet2 but i would add line code insert row total under the filling lastrow sum the values

Sub Test1()

        Dim CustID As String: cn = sheet2.[g3].Value
        Dim FromDt As Long: FromDt = sheet2.[C3].Value
        Dim ToDt As Long: ToDt = sheet2.[e3].Value

Application.ScreenUpdating = False
      
        sheet2.[A7].CurrentRegion.Offset(1).Clear
      
        With sheet1.[A3].CurrentRegion
                .AutoFilter 4, cn
                .AutoFilter 1, ">=" & FromDt, xlAnd, "<=" & ToDt
                .Offset(1).EntireRow.Copy sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
      
       ' sheet2.[C3:g3].Clear
      
Application.ScreenUpdating = True

End Sub

INPUT

فلترة بين تاريخين واسم عميل.xlsm
	A	B	C	D	E	F	G	H	I
1	statement of customer
2	date	CUSTOMER NO	INVOICE NO	CUSTOMER NAME	BALANCE OF FIRST DURITION	CREDIT	DEBIT	BALANCE	WAY OF PAID
3	01/01/2019	isd123	bsj1200	ALI	10	250	900	640	cash
4	01/02/2019	isd124	bsj1201	OMAR	20	300	800	480	cash
5	30/05/2019	isd125	bsj1202	MOHAMMED	30	50987	2341678	2290661	bank
6	15/06/2019	isd126	bsj1203	MAHMOUD	40	432	6543	546	cash
7	16/06/2019	isd127	bsj1204	AHMED	80	4321	5678	1277	bank
8	17/06/2019	isd128	bsj1205	SARAH	20	778	564	-234	cash
9	18/06/2019	isd129	bsj1206	NORIE	10	99	321	212	cash
10	01/02/2019	isd123	bsj1207	ALI	10	250	900	640	cash
11
12
13
sheet1

Cell Formulas
Range	Formula
H7:H10,H3:H5	H3	=G3-(E3+F3)

OUTPUT

فلترة بين تاريخين واسم عميل.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3		from date	01/01/2019	to date	01/02/2019	CN	ALI
4
5
6
7	date	CUSTOMER NO	INVOICE NO	CUSTOMER NAME	BALANCE OF FIRST DURITION	CREDIT	DEBIT	BALANCE	WAY OF PAID
8	01/01/2019	isd123	bsj1200	ALI	10	250	900	640	cash
9	01/02/2019	isd123	bsj1207	ALI	10	250	900	640	cash
10	TOTAL				20	500	1800	1280
sheet2

 

[abdelfattah]

any help?

 

[aaleem]

Hi abdelfattah, please try the below code

Sub Test1()

Dim CustID As String: cn = Sheet2.[g3].Value
Dim FromDt As Long: FromDt = Sheet2.[C3].Value
Dim ToDt As Long: ToDt = Sheet2.[e3].Value
Dim lRow As Long

Range("A8:I1000").Clear

Application.ScreenUpdating = False

Sheet2.[A7].CurrentRegion.Offset(1).Clear

With Sheet1.[A3].CurrentRegion
.AutoFilter 4, cn
.AutoFilter 1, ">=" & FromDt, xlAnd, "<=" & ToDt
.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
.AutoFilter
End With

' sheet2.[C3:g3].Clear

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(lRow + 1, 1) = "Total"

For o = 5 To 8
Cells(lRow + 1, o) = Application.WorksheetFunction.Sum(Cells(lRow, o))
Next o


Application.ScreenUpdating = True

End Sub

 

[abdelfattah]

thanks but it should do that in sheet2 not sheet1 and it doesn't sum the values it copy the last value each column
it should sum the values