Hi
I want changing this code or new code by using user form instead of using C2,E2 . I want using textbox1,textbox2 to fill dates and instead of using G3 I want using combobox1 contains sheets names .
so I want filling dates in textbox1 ,textbox2 and select sheet name from combobox1 , then should be brings date, invoice and total from the lastrow contains TOTAL word is existed in column A with insert TOTAL row to sum the whole amounts are existed in TOTAL column as in listbox1 .
if the cells textbox1 ,textbox2 , combobox1 are empty then should doesn't show anything in listbox1 , but if the combobox1 is just selected sheet name and textbox1,textbox2 are empty then should brings the all of data with insert TOTAL row to sum the whole amounts are existed in TOTAL in listbox1 .
to understand how populate data in listbox on userform then just check this link to understand how data shows inside sheet.
sort data based on two dates and sheet name from sheet to another
the code
and as I said the data could be 7000 rows for each sheet . that's why use sorting with array way to doesn't make code slowness.
if this is not clear I will post pictures by userform.
thanks.
I want changing this code or new code by using user form instead of using C2,E2 . I want using textbox1,textbox2 to fill dates and instead of using G3 I want using combobox1 contains sheets names .
so I want filling dates in textbox1 ,textbox2 and select sheet name from combobox1 , then should be brings date, invoice and total from the lastrow contains TOTAL word is existed in column A with insert TOTAL row to sum the whole amounts are existed in TOTAL column as in listbox1 .
if the cells textbox1 ,textbox2 , combobox1 are empty then should doesn't show anything in listbox1 , but if the combobox1 is just selected sheet name and textbox1,textbox2 are empty then should brings the all of data with insert TOTAL row to sum the whole amounts are existed in TOTAL in listbox1 .
to understand how populate data in listbox on userform then just check this link to understand how data shows inside sheet.
sort data based on two dates and sheet name from sheet to another
the code
VBA Code:
Sub test_2()
Dim ws As Worksheet, ShName$, DateFrom&, DateTo&
Set ws = Sheets("SH2")
ShName = ws.[G2]
DateFrom = ws.[C2]
DateTo = ws.[E2]
If ShName = "" And DateFrom = 0 And DateTo = 0 Then
ws.[A4].CurrentRegion.Offset(1).Clear
Exit Sub
End If
With Sheets(ShName).[A1].CurrentRegion.Columns(1)
If DateFrom = 0 Or DateTo = 0 Then
a = Filter(.Parent.Evaluate(Replace("transpose(if(@=""Total"",row(@),0))", "@", .Address)), 0, 0)
Else
a = Filter(.Parent.Evaluate(Replace("transpose(if(((@)=""Total"")*(offset(@,,1)>=" & DateFrom & ")*(offset(@,,1)<=" & DateTo & "),row(@),0))", "@", .Address)), 0, 0)
End If
b = .Parent.[A1].CurrentRegion.Value
End With
With ws
.[A4].CurrentRegion.Offset(1).Clear
With .Range("A" & Rows.Count).End(3)(2)
.Offset(, 1).Resize(UBound(a) + 1, 3) = Application.Index(b, Application.Transpose(a), [{2,4,10}])
.Resize(UBound(a) + 1) = Evaluate("row(1:" & UBound(a) + 1 & ")")
End With
With .Range("A" & Rows.Count).End(3)(2)
.Value = "Total"
.Offset(, 3) = "=sum(d5:d" & .Row - 1 & ")"
With .CurrentRegion
.Borders.LineStyle = 1
.HorizontalAlignment = xlCenter
.Columns(4).NumberFormat = "#,0.00"
End With
End With
End With
End Sub
if this is not clear I will post pictures by userform.
thanks.