amending code filter & copy data based on dropdown and cells from sheet to another

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010
hello
first of all i have ever issueed this thread in this forum but nobody answer me so i hope here sombody help
bring all data of customers from sheet to another
here i have this code works very well (it brings the specific customer and specific dates ) in sheet "account" from sheet"data"custome = b1 choose customer and b2 from date b3 = to date first of all i would choose from b1 = "all" without choose dates then brings all customers and copy to sheet2 the second when i choose from dropdown "all" an specify dates so it gives me customers which are existed between two dates then copy to sheet2 the third when i choose from b1 the customer without specify dates so it copy all of process relating customer to sheet2
note: it should when i choose in sheet"account" from b1 show the result in sheet "account" before copy to sheet2 moreover ny condition in orginal code supposes staying as it
VBA Code:
Sub sama1()
Dim LastR As Long
Dim SText As String
Dim StDate As Date
Dim EndDate As Date
Dim LastR1 As Long
Application.ScreenUpdating = False
Sheets("account").Range("A5:h10000").ClearContents

SText = Sheets("account").Range("B1")
If SText = "" Then SText = "*"
StDate = Sheets("account").Range("B2")
If StDate = 0 Then StDate = WorksheetFunction.Min(Sheets("DATA").Columns(2))
EndDate = Sheets("account").Range("B3")
If EndDate = 0 Then EndDate = WorksheetFunction.Max(Sheets("DATA").Columns(2))
LastR1 = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=6, Criteria1:=SText
Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=2, Criteria1:=">=" & Format(StDate, "mm/dd/yy"), Operator:=xlAnd, _
Criteria2:="<=" & Format(EndDate, "mm/dd/yy")
LastR = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("DATA").Range("A3:g" & LastR).SpecialCells(xlCellTypeVisible).Copy
Sheets("account").Range("A5").PasteSpecial
Sheets("account").Range("A5").Select
Sheets("DATA").Range("A3:g3").AutoFilter
Application.ScreenUpdating = True
End Sub

thanks
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
965
Office Version
  1. 2010
sheet "data"
client1.xlsm
ABCDEFG
2
3ITEMDATEDEBITCREDITMAINMINORDESCRIBE
416/29/202018,780.00CLIENTALIINVOICE NO 12345
526/29/20207,500.000.00CLIENTAYMENINVOICE NO 12346
636/30/202018,800.000.00CLIENTAYMENINVOICE NO 12347
747/2/20201,500.00CLIENTIBRAHIMINVOICE NO 12348
857/4/2020480.00CLIENTMAHMOUDINVOICE NO 12349
967/4/202037,600.00CLIENTAYMENINVOICE NO 12350
1077/4/202015,000.00CASHAYMENCASH
1187/8/202046,710.00CLIENTWALIEDINVOICE 12351
1297/9/202020,000.00CASHHAMZAHCASH
13117/8/202028,080.00CLIENTHAMZAHINVOICE NO 12352
14127/9/202021,740.00CLIENTWALIEDINVOICE NO 12353
15137/9/20201,550.00CASHIBRAHIMCASH
16144-Jul-204,500.00CLIENTHASSANINVOICE NO 12354
171516-Jul-2063,750.00CLIENTWALIEDINVOICE NO 12355
181616-Jul-202,700.00CASHAYMENCASH
191718-Jul-2015,000.00CASHWALIEDCASH
data

sheet "account"
client1.xlsm
ABCDEFG
1NAME
2FROM28-Jun-20FIRST OF DURITIONDEBITCREDITBALANCE
3TO31-Aug-200.00249,440.0054,250.00195,190.00
4
5ITEMDATEDEBITCREDITMAINMINORDESCRIBE
616/29/202018,780.00CLIENTALIINVOICE NO 12345
726/29/20207,500.000.00CLIENTAYMENINVOICE NO 12346
836/30/202018,800.000.00CLIENTAYMENINVOICE NO 12347
947/2/20201,500.00CLIENTIBRAHIMINVOICE NO 12348
1057/4/2020480.00CLIENTMAHMOUDINVOICE NO 12349
1167/4/202037,600.00CLIENTAYMENINVOICE NO 12350
1277/4/202015,000.00CASHAYMENCASH
1387/8/202046,710.00CLIENTWALIEDINVOICE 12351
1497/9/202020,000.00CASHHAMZAHCASH
15117/8/202028,080.00CLIENTHAMZAHINVOICE NO 12352
16127/9/202021,740.00CLIENTWALIEDINVOICE NO 12353
17137/9/20201,550.00CASHIBRAHIMCASH
18144-Jul-204,500.00CLIENTHASSANINVOICE NO 12354
191516-Jul-2063,750.00CLIENTWALIEDINVOICE NO 12355
201616-Jul-202,700.00CASHAYMENCASH
211718-Jul-2015,000.00CASHWALIEDCASH
account
Cell Formulas
RangeFormula
C3C3=IFERROR(VLOOKUP(B1,'C:\Users\OSE\Downloads\[CLIENT.xlsm]Sheet1'!B2:C27,2,0),0)
D3D3=SUM(C5:C10000)
E3E3=SUM(D6:D10000)
F3F3=D3-E3
Cells with Data Validation
CellAllowCriteria
B1List=sama1



sheet2: this the result in condition 1
client1.xlsm
ABCDEFG
1NAMEALI
2FIRST OF DURITIONDEBITCREDITBALANCE
30.001,195,510.00730,910.00464,600.00
4
5ITEMDATEDEBITCREDITMAINMINORDESCRIBE
616/29/202018,780.00CLIENTALIINVOICE NO 12345
7
8
9NAMEAYMEN
10FIRST OF DURITIONDEBITCREDITBALANCE
110.00730,910.00445,820.00285,090.00
12
13ITEMDATEDEBITCREDITMAINMINORDESCRIBE
1426/29/20207,500.000.00CLIENTAYMENINVOICE NO 12346
1536/30/202018,800.000.00CLIENTAYMENINVOICE NO 12347
1647/4/202037,600.00CLIENTAYMENINVOICE NO 12350
1757/4/202015,000.00CASHAYMENCASH
18616-Jul-202,700.00CASHAYMENCASH
19
20NAMEIBRAHIM
21FIRST OF DURITIONDEBITCREDITBALANCE
220.00428,120.00238,890.00189,230.00
23
24ITEMDATEDEBITCREDITMAINMINORDESCRIBE
2517/2/20201,500.00CLIENTIBRAHIMINVOICE NO 12348
2627/9/20201,550.00CASHIBRAHIMCASH
27
28
29NAMEMAHMOUD
30FIRST OF DURITIONDEBITCREDITBALANCE
310.00237,340.00189,280.0048,060.00
32
33ITEMDATEDEBITCREDITMAINMINORDESCRIBE
3417/4/2020480.00CLIENTMAHMOUDINVOICE NO 12349
35
36
37NAMEWALIED
38FIRST OF DURITIONDEBITCREDITBALANCE
390.00189,280.0047,580.00141,700.00
40
41ITEMDATEDEBITCREDITMAINMINORDESCRIBE
4217/8/202046,710.00CLIENTWALIEDINVOICE 12351
4327/9/202021,740.00CLIENTWALIEDINVOICE NO 12353
44316-Jul-2063,750.00CLIENTWALIEDINVOICE NO 12355
45418-Jul-2015,000.00CASHWALIEDCASH
46
47NAMEHAMZAH
48FIRST OF DURITIONDEBITCREDITBALANCE
490.0032,580.0024,500.008,080.00
50
51ITEMDATEDEBITCREDITMAINMINORDESCRIBE
5217/9/202020,000.00CASHHAMZAHCASH
5327/8/202028,080.00CLIENTHAMZAHINVOICE NO 12352
54
55NAMEHASSAN
56FIRST OF DURITIONDEBITCREDITBALANCE
570.004,500.000.004,500.00
58
59ITEMDATEDEBITCREDITMAINMINORDESCRIBE
6014-Jul-204,500.00CLIENTHASSANINVOICE NO 12354
sheet2
Cell Formulas
RangeFormula
A3,A57,A49,A39,A31,A22,A11A3=IFERROR(VLOOKUP(B1,'C:\Users\OSE\Downloads\[CLIENT.xlsm]Sheet1'!B2:C27,2,0),0)
B57:C57,B49:C49,B39:C39,B31:C31,B22:C22,B11:C11,B3:C3B3=SUM(C6:C10000)
D3,D57,D49,D39,D31,D22,D11D3=B3-C3
Cells with Data Validation
CellAllowCriteria
B1,B9,B20,B29,B37,B47,B55List=sama1
 

Watch MrExcel Video

Forum statistics

Threads
1,123,355
Messages
5,601,142
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top