abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,429
- Office Version
- 2019
- 2010
- Platform
- Windows
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
INPUT
OUTPUT
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
VBA 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
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 |