Input Date for Filter

[luckyearl]

Hi
I want to filter records that lie in between dates, with startDate & endDate cells F1 & H1, Data is in A2:E2000, Date in just wondered if I could input the dates in a dialog (do away with F1& H1), also make the range dynamic
My Code

Sub MyDateFilter()
    Dim lngStart As Long, lngEnd As Long
    lngStart = Range("F1").Value 'start date
    lngEnd = Range("H1").Value 'end date
    Range("A1:A2000").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd
End Sub

 

[luckyearl]

Hi @kevin9999
Just wondering how do I tweak the code
I want to have "Index" sheet as active sheet, with name of sheet to filter in A3 of "Index" Sheet

Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String, ws As Worksheet, MySheet As String
    lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", "1/10/20")
    lngEnd = InputBox("Insert Date in format dd/mm/yyyy", "End Date Input box", "31/01/2021")
    
    MySheet = Sheets("Index").Range("A3").Value
    Set ws = Sheets(MySheet)
    ws.Activate
    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Index").Select
Range("a12").Select
Selection.PasteSpecial xlPasteValues
End Sub

This line → With ActiveSheet.Cells(1, 1).CurrentRegion → Run-time error '13': Type mismatch
Any Suggestions?
Cheers

 

[kevin9999]

Hi @kevin9999
Just wondering how do I tweak the code
I want to have "Index" sheet as active sheet, with name of sheet to filter in A3 of "Index" Sheet

Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String, ws As Worksheet, MySheet As String
    lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", "1/10/20")
    lngEnd = InputBox("Insert Date in format dd/mm/yyyy", "End Date Input box", "31/01/2021")
   
    MySheet = Sheets("Index").Range("A3").Value
    Set ws = Sheets(MySheet)
    ws.Activate
    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Index").Select
Range("a12").Select
Selection.PasteSpecial xlPasteValues
End Sub

This line → With ActiveSheet.Cells(1, 1).CurrentRegion → Run-time error '13': Type mismatch
Any Suggestions?
Cheers

What is the value of MySheet once you've set it, and where does the data range start (top left corner) on that sheet?

 

[luckyearl]

Hi,
MySheet points to A3
A3 has validation list → names of all worksheets of the workBook, and is on "Index" workSheet
Data range starts A1, but has many tables on the sheet with many empty cells scattered , but none after row 12
Thank you

 

[kevin9999]

Hi,
MySheet points to A3
A3 has validation list → names of all worksheets of the workBook, and is on "Index" workSheet
Data range starts A1, but has many tables on the sheet with many empty cells scattered , but none after row 12
Thank you

I'm struggling a bit to follow your flow of logic, but see if I've got this right:
1. You get the value of MySheet from the Index sheet, range A3 which is showing Index
2. You set ws to MySheet - which is the value you get from the Index sheet - which is Index
3. Later in your code, you're copying a filtered range from the ws sheet (Index) to range A12 on the sheet - Index

Is that right?

 

[luckyearl]

I have workbook with several sheets. The first sheet is "Index"
A3 cell on "Index" Sheet has validation list → names of all worksheets of the workBook
I would select a sheet example "BownessExpenses" from dropdown List in A3 of "Index" Sheet
Now I want to filter the data on workSheet "BownessExpenses" → copy filtered range
Then would paste on A12 of "Index" workSheet
So
1. You get the value of MySheet from the Index sheet, range A3→correct, but the value would be some other sheet "BownessExpenses"
2. You set ws to MySheet - which is the value you get from the Index sheet → Correct, which is NOT Index, it is some other Sheet "BownessExpenses"
3. Later in your code, you're copying a filtered range from the ws sheet (Index) to range A12 on the sheet - Index → Correct

Thank you

 

[kevin9999]

I have workbook with several sheets. The first sheet is "Index"
A3 cell on "Index" Sheet has validation list → names of all worksheets of the workBook
I would select a sheet example "BownessExpenses" from dropdown List in A3 of "Index" Sheet
Now I want to filter the data on workSheet "BownessExpenses" → copy filtered range
Then would paste on A12 of "Index" workSheet
So
1. You get the value of MySheet from the Index sheet, range A3→correct, but the value would be some other sheet "BownessExpenses"
2. You set ws to MySheet - which is the value you get from the Index sheet → Correct, which is NOT Index, it is some other Sheet "BownessExpenses"
3. Later in your code, you're copying a filtered range from the ws sheet (Index) to range A12 on the sheet - Index → Correct

Thank you

OK, I think I understand it a bit better now. Your code is starting to grow weeds - you hardly ever need to use .Select. The following code should work, as long as the data in your sheet BownessExpenses starts in cell A1, and as I pointed out in my first post: as long as your data is contiguous.

Option Explicit
Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String, ws As Worksheet, MySheet As String
    lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", "1/10/20")
    lngEnd = InputBox("Insert Date in format dd/mm/yyyy", "End Date Input box", "31/01/2021")
    
    MySheet = Sheets("Index").Range("A3").Value
    Set ws = Worksheets(MySheet)
    With ws.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
        .Copy
        Sheets("Index").Range("A12").PasteSpecial xlPasteValues
        Application.CutCopyMode = 0
        .AutoFilter
    End With
End Sub

If this doesn't work, you'll need to upload your sheet using the XL2BB Tool before I can help you further.
Good luck

 

[luckyearl]

I like the expression "starting to grow weeds", no wonder this happens when do not know what u are doing
BTW the code works perfectly, Thank you for all your help, u have been kind

 

[kevin9999]

I like the expression "starting to grow weeds", no wonder this happens when do not know what u are doing
BTW the code works perfectly, Thank you for all your help, u have been kind

My pleasure @luckyearl and thanks for the feedback. Sometimes we have no choice but to record a macro. When we do, however, we always end up with a ton of .Selects and .Activates in the code, which are largely superfluous.