0

I have a macro that pulls a report from one worksheet, then pulls applicable data from another worksheet, and prints both pages to a single PDF file. The print to pdf side of it is working, but filtering the data to match the report is not.

I started with defining the start and end dates from the report, then I would like to filter the first column of my data worksheet based on this date range before printing.

My code is below:

Dim StartDate as Date, EndDate as Date, DataTable as String
EndDate = ActiveSheet.Range("G6")
StartDate = ActiveSheet.Range("G5")

Worksheets("Data").Activate
DataTable = Worksheets("Data").Range("A2").CurrentRegion.Address
ActiveSheet.Range(DataTable).AutoFilter Field:=1, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

Changing the filter manually before running the macro does work, but I'd like for the macro to do it on its own. Any advice is much appreciated.

DarbyH
  • 1
  • 1
  • What error does your current code throw? – BigBen Feb 16 '21 at 23:32
  • "AutoFilter method of Range class failed" – DarbyH Feb 16 '21 at 23:53
  • What is the value of `DataTable`? – BigBen Feb 16 '21 at 23:56
  • It's "$A$1:$W$502", which is the location/size of my table on the "Data" worksheet – DarbyH Feb 17 '21 at 00:00
  • 1
    Try it manually, but use the macro recorder. If you cannot figure it out, try adding the code you got from the macro recorder to your post. – VBasic2008 Feb 17 '21 at 00:55
  • 1
    Start by getting rid of all those `Activate` statements. It will often cause multiple problems. May or may not be an issue here, but, if not, it could become one in the future. See [How to avoid using Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Ron Rosenfeld Feb 17 '21 at 01:04

2 Answers2

0

Try converting the dates into doubles when constructing the criteria strings:

ActiveSheet.Range(DataTable).AutoFilter Field:=1, _
   Criteria1:=">=" & CDbl(StartDate), Operator:=xlAnd, _
   Criteria2:="<=" & CDbl(EndDate)
DimRasRecordset
  • 56
  • 4
0

Firstly, please get in the habit of being explicit with your sheet references – avoid using ActiveSheet and use the actual sheet name instead. The code below uses “Sheet2” to demonstrate this point. The code is tested and does work for me. Let me know how you go with it.

Option Explicit
Sub testFilter()
Dim StartDate As Date, EndDate As Date

StartDate = Sheets("Sheet2").Range("G5").Value2   <~~ **Change Sheet2 to your actual sheet name
EndDate = Sheets("Sheet2").Range("G6").Value2

With Sheets("Data").Range("A2").CurrentRegion
    .AutoFilter 1, ">=" & 1 * StartDate, 1, "<=" & 1 * EndDate
End With

End Sub