RE: is there a shorter, more elegant way of doing this...
...this code works great but seems way too long and cumbersome. Would anyone know how to perform this autofilter variable search operation and copy the results to the REPORT sheet to display a userform with the results as shown in this code ?
Thanks for anyone's help.
cr
...this code works great but seems way too long and cumbersome. Would anyone know how to perform this autofilter variable search operation and copy the results to the REPORT sheet to display a userform with the results as shown in this code ?
Code:
Sub CATEGORYSPENDING()
Dim d1 As Date, d2 As Date, desval, catval As String, ws As Worksheet
d1 = NewReport.DTPicker1.Value
d2 = NewReport.DTPicker2.Value
catval = NewReport.ComboBox2.Value
Set ws = Worksheets("REPORT")
ws.Activate
With ws
Range("A1:K700").Select
Selection.Delete
End With
Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets("REGISTER").Range("A1:E1").AutoFilter
With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
.AutoFilter Field:=2, Criteria1:=catval
.AutoFilter Field:=3, Criteria1:=">=" & d1, Operator:=xlAnd, Criteria2:="<=" & d2
.SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
.AutoFilter
End With
With Sheets("report")
If ActiveSheet.Range("A1") <> "" Then
If WorksheetFunction.CountA(Range("F:F")) = 1 Then
Range("F1:F1").Name = "Total"
Range("E1:E1").Name = "TotalOut"
Range("Total").Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = "=SUM(Total)+SUM(TotalOut)"
FoodReport.Show 'IFilterReport.Show
FoodReport.TextBox1.Value = ActiveCell.Value
Sheets("BUDGET").Select
FoodReport.Caption = "Category spending & NEWREPORT.Combobox2.Value"
Else
Range("F1:F" & Range("F1").End(xlDown).Row).Name = "Total"
Range("E:E").Name = "TotalOut"
Range("Total").End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = "=SUM(Total)+SUM(TotalOut)"
FoodReport.Show
FoodReport.TextBox1.Value = ActiveCell.Value
Sheets("BUDGET").Select
FoodReport.Caption = "Category spending" & " - " & NewReport.ComboBox2.Value
End If
Else
MsgBox "No value found in date range"
End If
End With
Sheets("BUDGET").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Thanks for anyone's help.
cr