Autofilter by a certain Date Range?

[Chronix99]

Hello,

I have a table of data which has a date column with dates in a very strict format (dd/mm/yyyy).

I have a userform with a bit of VBA script behind it to prompt a user for a "START" date and a "END" date. I need to filter my table to show only the entries between these two dates, copy it into a new worksheet and then show it.

I've got all the code together but the filter won't work, it just shows every record.

This is what I have so far:

'Declare variables
Dim dtmFrom As Date
Dim dtmTo As Date
Dim intRCount As Integer

'Check if date inpts are valid and then assign user inputs to variables
If IsDate(FromDate1) Then
dtmFrom = DateSerial(Year(FromDate1), Month(FromDate1), Day(FromDate1))
Else: dtmFrom = MsgBox("Please enter a valid ""FROM"" date.", vbCritical + vbOKOnly)
Exit Sub
End If

If IsDate(ToDate1) Then
dtmTo = DateSerial(Year(ToDate1), Month(ToDate1), Day(ToDate1))
Else: dtmFrom = MsgBox("Please enter a valid ""TO"" date.", vbCritical + vbOKOnly)
Exit Sub
End If

If ToDate1.Value < FromDate1.Value Then
dtmFrom = MsgBox("Please ensure the TO Date is after the FROM Date.")
Exit Sub
Else: End If

'Create a new Summary Worksheet
Worksheets.Add(After:=Worksheets(4)).Name = "SummaryWorksheet"
'Populate Orders Summary
Sheets("Orders").Select
intRCount = ActiveSheet.UsedRange.Rows.Count

'Auto Filter table for viewing
Range("$A$1:$F$" & intRCount).AutoFilter
ActiveSheet.Range("$A$1:$F$" & intRCount).AutoFilter Field:=1, Criteria1:=">=" & dtmFrom, Operator:=xlAnd, Criteria2:="<=" & dtmTo
Range("$A$2:$F$" & intRCount).Select
Selection.Copy
Sheets("SummaryWorksheet").Select
Range("A9").Select
ActiveSheet.Paste
Sheets("Orders").Select
Application.CutCopyMode = False

Now I wasn't sure how to do this initially, so I used the 'record macro' and got the basic structure off it and made a few modifications... any help?

 

[Datsmart]

Worked on something similar to this about a year ago.
http://www.mrexcel.com/forum/showthread.php?t=517110

This uses Advanced Filter. It might give you some ideas.

 

[Chronix99]

Hey DatSmart,
Thank you for your reply..but I'm not sure how to get this working.

Since the data input is captured from a User Form input box - I have to work variables instead of values of cells (I've always tried inserting these values into cells, and then using the values of those cells but it doesn't work either.)

Not sure exactly what I'm doing wrong but I've tried to copy the relevant bits from your code and it runs without any errors but it still copies all of the data and not the ones that fit the date range.

Working from your post here; http://www.mrexcel.com/forum/showpost.php?p=2555601&postcount=4

How do you tell VBA to use the date range?
How does VBA know which column to apply the filter to?

Just a few things im confused about

 

[Datsmart]

I can see where the confusion is coming from.
Your code is running a regular AutoFilter where the dates are gathered from an UserForm.
My code is running an Advanced Filter where the dates are generated by code using todays date for the From date and then seven days later for the To date.
Note the lines:

'Assign Date range to Advanced Filter Criteria cells
    Sheets("Sheet2").Range("I2").Value = ">=" & Date
    Sheets("Sheet2").Range("J2").Value = "<=" & Date + 7

In my Advanced Filter code the Data Table is on Sheet1.
Criteria for the filter is on Sheet2. (I2 and J2)
The filtered data is copied to the Target Sheet in another workbook.
Advanced Filter can copy data to another location without altering the original data.

You will have to decide which way to go.
You can have your UserForm insert dates to some cells, then run your code to use those cells as From/To criteria in your filter.

 

[Chronix99]

Ahh this is driving me insane! I've tried both (Autofilter and advanced filter) - it works perfectly fine when I manually do it but when a macro/vba code is run it just doesn't show any data.

I've tried both recording the macro and also manually coding it but neither works. Which is really bizarre because when I press record and manually do it, the results are fine but when I rerun that JUST record macro - it doesn't show anything.

I was wondering if you'd be able to help me out here DatSmart

I have a simple worksheet with order lines in it and a date column. I just want to create a report (which I've done) and just populate it with the orders that fall in the date range that is provided by the user (or even in specific cells because I can dump the user input into those cells.)

 

[Datsmart]

You are going to have to describe the layout of your data, column headers, and the layout of your report page.
Post your code and describe where it doesn't work as expected.
Any error messages?

 

[buxtongt]

I've used something along these lines before, but pulling the values from cells instead of pop up boxes, should be the same for the filtering though. The line I used was:

Columns("A:J").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:=">=" & upper, Operator:=xlOr, _
Criteria2:="<=" & lower

with upper and lower being the two variable names. It looks like you are trying to do it all in one line, which might be possible, I'm not sure. Give this a try and see if it helps.

 

[Chronix99]

@buxtongt - I tried this and just tried it again but the filter won't work. No errors, the code runs to end but nothing gets filtered.

@DatSmart:

The orders are in a worksheet called "Orders". This work sheet has the following headers:
Order Number, Customer Number, Rose Variety, Category, Number , Date

When a button on the home screen is pressed, the user is prompted to put a "START" date and an "END" date. These are checked for validation (using the IsDate function) and if the validation passes they are stored in the variables called "dtmFrom" and "dtmTo" respectively.

Then the code creates a new worksheet called "SummaryWorksheet".
This is formatted with headers and what not, and the headings (mentioned above) are created on row #9 (i.e Order number is A9, Customer Number on B9 etc.)

It starts at row 9 because above it there are other headers/information that needs to go on top.

- NO errors, everything runs to the end but no filtering happens. I've tried both advanced filtering and copying it to the SummaryWorksheet and also tried Autofilter and copying the filtered data - still no good.

 

[Chronix99]

Here is the code for when the "Create Summary" button is clicked. Most of it is formatting coding but if you look at the comment "Populate Orders Summary" - this is where it doesn't work.

Private Sub CommandButton2_Click()
'Declare variables
Dim dtmFrom As Date
Dim dtmTo As Date
Dim intRCount As Integer

'Check if date inpts are valid and then assign user inputs to variables
If IsDate(FromDate1) Then
dtmFrom = DateSerial(Year(FromDate1), Month(FromDate1), Day(FromDate1))
Else: dtmFrom = MsgBox("Please enter a valid ""FROM"" date.", vbCritical + vbOKOnly)
Exit Sub
End If

If IsDate(ToDate1) Then
dtmTo = DateSerial(Year(ToDate1), Month(ToDate1), Day(ToDate1))
Else: dtmFrom = MsgBox("Please enter a valid ""TO"" date.", vbCritical + vbOKOnly)
Exit Sub
End If

If ToDate1.Value < FromDate1.Value Then
dtmFrom = MsgBox("Please ensure the TO Date is after the FROM Date.")
Exit Sub
Else: End If

'Create a new Summary Worksheet
Worksheets.Add(After:=Worksheets(4)).Name = "SummaryWorksheet"

'Format Worksheet to look nice
Sheets("SummaryWorksheet").Select
Range("A1:G2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Sheets("SummaryWorksheet").Select
Range("A1:F2").Select
ActiveCell.FormulaR1C1 = "Summary Report - " & dtmFrom & " to " & dtmTo
Selection.Font.Size = 18
Selection.Font.Bold = True
Range("A3:F3").Select
Selection.Merge
ActiveCell.FormulaR1C1 = "Total Number of Sales:"
Range("A4:F4").Select
Selection.Merge
ActiveCell.FormulaR1C1 = "Total Income from Sales:"
Range("A5:F5").Select
Selection.Merge
ActiveCell.FormulaR1C1 = "Total raised for ""ENVIRONMENT"":"
Range("A6:g6").Select
Selection.Merge
ActiveCell.FormulaR1C1 = "Report Created on: " & Date
Range("A8").Select
Sheets("SummaryWorksheet").Select
ActiveCell.FormulaR1C1 = "Orders Summary"

Range("A8:G8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("A9").Select
Sheets("SummaryWorksheet").Select
ActiveCell.FormulaR1C1 = "Order Number"
Range("B9").Select
ActiveCell.FormulaR1C1 = "Customer Number"
Range("C9").Select
ActiveCell.FormulaR1C1 = "Rose Variety"
Range("D9").Select
ActiveCell.FormulaR1C1 = "Category"
Range("E9").Select
ActiveCell.FormulaR1C1 = "Number"
Range("F9").Select
ActiveCell.FormulaR1C1 = "Date"
Range("G9").Select
ActiveCell.FormulaR1C1 = "Revenue ($)"


'Populate Orders Summary
'ADVANCED FILTER THISSSS!!!!!!!!
Sheets("Orders").Activate
Columns("A:F").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">=" & dtmFrom, Operator:=xlOr, _
Criteria2:="<=" & dtmTo


'Dim intRowCountOrders As Integer
'intRowCountOrders = Sheets("Orders").UsedRange.Rows.Count
'Sheets("Orders").Select

'Populate Information about Orders Summary
'Total sales, income from sales, amount for environment etc.
'Total revenue from sales

Dim intRowCount As Integer
intRowCount = Sheets("SummaryWorksheet").UsedRange.Rows.Count
Range("C10").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(ActiveCell, Worksheets("Roses").Range("A:B"), 2, False) * ActiveCell.Offset(0, 2)
ActiveCell.Offset(1, 0).Select
Loop
Range("G9:G" & intRowCount).Select
Selection.Style = "Currency"
Range("G3").Value = WorksheetFunction.Sum(Range("E9:E" & intRowCount))
Range("G4").Value = WorksheetFunction.Sum(Range("G9:G" & intRowCount))
Range("G4").Select
Selection.Style = "Currency"
Range("G3").Select
Selection.NumberFormat = "#,##0"

'Greening the environment
Range("G5").Select
ActiveCell.FormulaR1C1 = Range("G3") * 0.02
Selection.Style = "Currency"

'Borders for Order Summary Range and Autofitting all cells, change A8:F8 with automatic calculation of filled cells
Sheets("SummaryWorksheet").Select
Range("A9:G9").Select 'change this line to include all the entries not just the header, post import of filtered data
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Cells.Select
Cells.EntireColumn.AutoFit
Sheets("orders").Select
Columns("G:G").Select
Selection.ClearContents
Sheets("SummaryWorksheet").Select
End Sub

 

[buxtongt]

Have you tried having the from and to date variables pasted into cells somewhere, so you can make sure they are getting defined like you want them to?