RE: is there a shorter, more elegant way of doing this...

[chazrab]

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 ?

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