Auto Filter is lost when removed

[Guzzlr]

Hello
I used the recorder to get:

Rows("5:5").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$5:$AK$23665").AutoFilter Field:=4, Criteria1:= _
        "Maintenance"
    ActiveSheet.Range("$A$5:$AK$23665").AutoFilter Field:=5, Criteria1:= _
        "Completed"
        Selection.AutoFilter

I wish to copy sheet and move to a new sheet named TEST.
My problem, is i cant copy and paste to he new sheet because the filter is on.
If I take the filter off, then all the data comes back.
so how do I filter and retain the filtered data, with out a filter and copied to the new work sheet?
Thank you

 

[jkpieterse]

If you're setting the filter through VBA, you could simply repeat that, could you not?

 

[Guzzlr]

If you're setting the filter through VBA, you could simply repeat that, could you not?

So you are saying simply keep the filter in place. The problem is I want to copy and paste the entire sheet to a new sheet with it already filtered, which excel doesn't let me. At least I dont know how to do it.
The work around is to copy and paste into a new work sheet, then place the filters and retain the fileters throughout the entire program. The finial report would still have the filters in place, which looks a little odd.
If there is no other way, then Ill have to do it that way
Thanks for the hlep

 

[RoryA]

Why can’t you copy and paste the filtered data?

 

[Guzzlr]

Why can’t you copy and paste the filtered data?

I can, the problem is when I take the filter off, everything comes back. I want to be able to copy the filtered data to a new worksheet, with the filter off
Thank you

 

[Alex Blakenburg]

I think you misunderstood Rory.
Are you using the Move or Copy Sheet option to copy the entire sheet ?
If you are do you need the whole sheet copies or just the filtered data ?
You need to create a new blank sheet first and they copy the contents of the original sheet to the blank sheet and it will only copy the visible cells.

 

[Guzzlr]

I think you misunderstood Rory.
Are you using the Move or Copy Sheet option to copy the entire sheet ?
If you are do you need the whole sheet copies or just the filtered data ?
You need to create a new blank sheet first and they copy the contents of the original sheet to the blank sheet and it will only copy the visible cells.

I have added a sheet called "Test"
I would like to copy just the filtered data to my added sheet of Test
Thanks for the help

 

[Alex Blakenburg]

Once you have created a sheet called test what happens if you click in the top left corner of the original spreadsheet (with the filter on) to select the whole sheet, then go to A1 on Test and hit paste ?

 

[Guzzlr]

This is working!
Is there VBA for this or simply use the recorder?
Thank you