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