How do I change my filter then copying all data shown

Question

I have an excel worksheet with lots of data in table form. Here's the macro recording for what want to do

    ActiveSheet.Range("$A$1:$M$2735").AutoFilter Field:=1, Criteria1:= _
    "2015-05-28"
    Range("A2618").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

The problem I'm having with this is that it gives specific cell's for the autofilter range (I want to apply this to different table's which may have a different number of rows/columns)

I also want to just select the top left cell once the data is filtered, this code does it by naming it. After that I want to select all the data in the table and copy it. I know this has something to do with Range("A1").CurrentRegion, but I dont know how to implement this.

Another problem I'm having with my code is filtering the results with dates in relation to todays date. For example I want to filter to yesterdays date, then to dates 3 days ago, then last weeks dates.

Sorry this is 3 questions in a post but help would be appreciated

Máté Juhász Máté Juhász · Accepted Answer · 2015-05-29T11:24:30

VBA knows tables as listobjects, so if you want to refer a table in a sheet you can use: Sheets(i).ListObjects(1).Range

To select the whole table after filtering: Sheets(i).ListObjects(1).Range.SpecialCells(xlCellTypeVisible).Select

Setting filter to yesterday: Sheets(i).ListObjects(1).Range.AutoFilter Field:=1, Criteria:=format(date()-1,"yyyy-mm-dd")

How do I change my filter then copying all data shown

1 Answers