Help required with Auto filter

[paulsolar]

Hi all

I'm pulling my hair out with this.

I cannot get the auto filter to work, I'm sure im missing something but cant seem to see what????

The code below is run from a userform, i've split it into two subs which I'll combine later. I've got all the dates into mm/dd/yyyy format as from past experience it seems to work better in this format.

I just cant get it to filter the two columns in the date range. Any help would preserve what i have left of my hair.

Kind regards

Paul

Private Sub CommandButton1_Click()


' clear sheet2 ready


Dim Lastrow1 As Long
Lastrow1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row


With Sheet2
  .Select
Range("A1:D" & Lastrow1).ClearContents
End With


'delete unwanted columns sheet1
With Sheet1
  .Select
Columns("A:E").EntireColumn.Delete
Columns("C:E").EntireColumn.Delete
End With


'filter dates


Sheet2.Range("D1") = TextBox1.Value 'from date
Sheet2.Range("B1") = TextBox2.Value 'end date


Sheet2.Range("B1").NumberFormat = "mm/dd/yyyy"
Sheet2.Range("D1").NumberFormat = "mm/dd/yyyy"


Sheet2.Range("A1").Value = "End Date"
Sheet2.Range("C1").Value = "Start Date"


Dim Lastrow As Long
Lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Range("A1:A" & Lastrow).NumberFormat = "mm/dd/yyyy"


sortoutfordates


End Sub


Sub sortoutfordates()


    Dim lDateFrom As Long
    Dim lDateTo As Long
    
    Dim Lastrow As Long
    Lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
    
    DateTo = Sheet2.Range("B1")
    DateFrom = Sheet2.Range("D1")
    
With Sheet1
    .AutoFilterMode = False
  
    With Sheet1
        Range("A1:B" & Lastrow).AutoFilter Field:=1, Criteria1:=">=" & DateFrom, Operator:=xlAnd, Criteria2:="<=" & DateTo
        Range("A1:B" & Lastrow).Copy
    End With
    
End With
     
    With Sheet2
        .Select
        With Range("A3")
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Select
        End With
    End With
    
    Application.CutCopyMode = False
    
    Unload Me
    
End Sub

 

[Fluff]

1st off try

With Sheet1
   .AutoFilterMode = False
   
   .Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:=">=" & DateFrom, Operator:=xlAnd, Criteria2:="<=" & DateTo
   .Range("A1:B" & LastRow).Copy
End With

If that doesn't work replace the variables, with the ranges

 

[paulsolar]

Hi Fluff

Good to hear from you as always.

I've tried that without any luck. It only shows one result in the filter for some reason where as there sould be 200'ish.

I tried typing in the excact range without any luck either.

I hate working on anything with dates

Any other ideas?

cheers

Paul

ps sorry about the rugby, i prayed for you but it didnt work

 

[Fluff]

I hate working on anything with dates

Snap!

Another option would be

Criteria1:=">=" & CLng(DateFrom), Operator:=xlAnd, Criteria2:="<=" & Clng(DateTo)

Rugby?? what's that

 

[paulsolar]

Hi Fluff

I just spotted something that may be of assistance. It is only showing on result evertime after the filter, that is something with todays date, which isnt in the date range I'm selecting, there are two items with todays date, but it is only showing the one????

cheers

Paul

 

[paulsolar]

Fluff, youve done it again

Sometimes i wonder why i dont just move in next door to you and call myself English

worked like a charm

 

[Fluff]

As always, glad to help & thanks for the feedback.