Hi,
I am pulling my hair out with this issue.
I have a source sheet containing all the data to be filtered.
I have a destination sheet containing the first 3 rows for the filter criteria range set up and from row 4 the results to be copied to including the header and data from the source sheet.
I have it working as long as I don't set any dates in the filter criteria.
As soon as I have dates as criteria, nothing is copied over.
My source file has column E containing the starting date, column F containing the ending date, and the column I containing a product type (string).
I set up the filter criteria data via VBA, ie: destination sheet (called "ForecastFilter") like the code below:
Case "AccMth"
.Range("E2").Value = ">=" & Range("startdate")
.Range("F2").Value = "<=" & Range("enddate")
.Range("I2").Value = "Product A"
.Range("J2").Value = ""
.Range("K2").Value = ""
'Set the filter criteria range according to entries
.Range(.Range("E1"), .Range("I2")).Name = "FilterCriteria2"
.Range refers to the destination sheet "ForecastFilter". The workbook as a few range names. Two are used to contain the startdate and enddate from another sheet in normal date format (not US format...)
PS. I tried with ">=1/4/2013" and "<=30/4/2013" instead of the range("startdate") and range("enddate") to be sure! No luck either.
Then the "Update" button on a third final destination sheet ("Account Monthly") should receive a specific range from the filtered data that were copied in the "ForecastFilter" sheet based on the criteria set, using a pivot table (that is not working either but it is not the question here).
If I don't include the dates in my filter criteria, but "Product A" only I have the correct rows copied over into the "ForecastFilter" sheet. As soon as I add any date in the criteria, nothing shows up below the headings.
The range "FilterCriteria2" is being updated at runtime with the information above. Information varies depending what sheet is calling for the data to be filtered from the source "ForecastData".
Here is the code that should copy data from source "ForecastData" to the filtered data sheet "ForecastFilter":
'AdvancedFilter data by chosen criteria
lastRow = ThisWorkbook.Worksheets("ForecastData").Cells(Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Worksheets("ForecastData").Range("A3:EB" & lastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("FilterCriteria2"), _
CopyToRange:=ThisWorkbook.Worksheets("ForecastFilter").Range("A5"), _
Unique:=False
If I remove the "CriteriaRange" line, all comes through unfiltered. If I leave the criteria value for "E2" and "F2" as empty, I have all data for "Product A" coming through, so the filter on the text is working as expected.
I can't find anything that could help me solve this issue.
The file was originally created in Excel 2003 and is now being used with Excel 2010. Everything was working fine in 2003... Not sure why it stopped working when using Excel 2010.
I am pulling my hair out with this issue.
I have a source sheet containing all the data to be filtered.
I have a destination sheet containing the first 3 rows for the filter criteria range set up and from row 4 the results to be copied to including the header and data from the source sheet.
I have it working as long as I don't set any dates in the filter criteria.
As soon as I have dates as criteria, nothing is copied over.
My source file has column E containing the starting date, column F containing the ending date, and the column I containing a product type (string).
I set up the filter criteria data via VBA, ie: destination sheet (called "ForecastFilter") like the code below:
Case "AccMth"
.Range("E2").Value = ">=" & Range("startdate")
.Range("F2").Value = "<=" & Range("enddate")
.Range("I2").Value = "Product A"
.Range("J2").Value = ""
.Range("K2").Value = ""
'Set the filter criteria range according to entries
.Range(.Range("E1"), .Range("I2")).Name = "FilterCriteria2"
.Range refers to the destination sheet "ForecastFilter". The workbook as a few range names. Two are used to contain the startdate and enddate from another sheet in normal date format (not US format...)
PS. I tried with ">=1/4/2013" and "<=30/4/2013" instead of the range("startdate") and range("enddate") to be sure! No luck either.
Then the "Update" button on a third final destination sheet ("Account Monthly") should receive a specific range from the filtered data that were copied in the "ForecastFilter" sheet based on the criteria set, using a pivot table (that is not working either but it is not the question here).
If I don't include the dates in my filter criteria, but "Product A" only I have the correct rows copied over into the "ForecastFilter" sheet. As soon as I add any date in the criteria, nothing shows up below the headings.
The range "FilterCriteria2" is being updated at runtime with the information above. Information varies depending what sheet is calling for the data to be filtered from the source "ForecastData".
Here is the code that should copy data from source "ForecastData" to the filtered data sheet "ForecastFilter":
'AdvancedFilter data by chosen criteria
lastRow = ThisWorkbook.Worksheets("ForecastData").Cells(Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Worksheets("ForecastData").Range("A3:EB" & lastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("FilterCriteria2"), _
CopyToRange:=ThisWorkbook.Worksheets("ForecastFilter").Range("A5"), _
Unique:=False
If I remove the "CriteriaRange" line, all comes through unfiltered. If I leave the criteria value for "E2" and "F2" as empty, I have all data for "Product A" coming through, so the filter on the text is working as expected.
I can't find anything that could help me solve this issue.
The file was originally created in Excel 2003 and is now being used with Excel 2010. Everything was working fine in 2003... Not sure why it stopped working when using Excel 2010.
Last edited:
