Retrieving filtered data best practise

[Undz12]

Hi

Total noob here, I'm teaching myself the basics and just playing around with moving data between spreadsheets.

I've got two Workbooks open, I'm creating a Macro to filter the data on one of the Workbooks, copy that data and paste it into another Workbook, the macro works:
Windows("Timesheets Week 1 - TEST.xlsx").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$Q$85").AutoFilter Field:=11, Criteria1:= _
"Region 11"

But the problem is if the data is longer than 85 rows this data will get missed off.
I got around this problem to just changing the macro to a really high number of rows

Windows("Timesheets Week 1 - TEST.xlsx").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$Q$20000").AutoFilter Field:=11, Criteria1:= _
"Region 11"

What is the simplest method around this?
Remember I want to copy the filtered data afterwards

 

[Joe4]

It looks like your first filter is filtering on column K.
We can dynamically find the last row with data in column K like this:

Dim lr as Long
lr = Cells(Rows.Count, "K").End(xlUp).Row

Then we can dynamically build that into your filter like this:

ActiveSheet.Range("$A$1:$Q$" & lr).AutoFilter Field:=11, Criteria1:= _
"Region 11"

 

[Undz12]

Thank you so much!!

I managed to use that same code to then dynamically find the last row again and copy the filtered data that I want!
I just had to declare a new count which I called lr1

I'm going to do this process several times in one macro so there will be lr1, lr2, lr3, lr4 etc. all the way up to lr12

Can you clear the original "lr" once it's been used so I can always use "lr" as my count?
Ideally I don't really want to have to rename the count as something new each time if possible.

 

[Joe4]

Yes, you can re-use the "lr" value. Just do it before you apply it to each filter, i.e.

Dim lr as Long

'First filter
lr = Cells ...
ActiveSheet.Range(...

'Second filter
lr = Cells ...
ActiveSheet.Range(...

etc