macro required to filter data on several tabs and save to a file, then repeating action

zbor - now that you've taken the time to check the title, do you have a siggestion as to how I might solve the problem?

No Problem, I have it working but the code is very ugly.

Its essentially reiterating the code every time instead of being in a loop

Sheets("100%").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets(">75%").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets(">50%").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets(">25%").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets(">10%").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets("Court Cases_Zero Arrs").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets("Zero Net Rent").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets("New Tenancies").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets("All Cases").Select
    Selection.AutoFilter Field:=1, Criteria1:="Patch 3"
    Sheets("100%").Select
   
'At the end, first tab is displayed again prior to saving
'Will save the current "Active workbook" as name specified plus todays date.
    
    ActiveWorkbook.SaveAs Filename:= _
        "\\**********FolderDirectory****\Patch_3 " & Format(Now, "dd mm yyyy") & ".xls" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

What I'd like if possible is to contain this in a loop. Is it possible to create an array/loop that will cycle through each one? I can't expalin so well but essentaially
>
for int i=0; i<number of patches; i++{

above code

Save as filename patch+(i);

}

Any help would be greatly appreaciated, many thanks.

Hi Arletter, that's effectively what I've done but the code is 'ugly' what I pasted above is reiterated 13 times and it seemed unnecessary.

Would be nice if I could contain it all within a loop instead?

Hi,

Sorry for not answering your earlier question. The code above repeats itself 12 times with just the patch and save name changing. Theres one tab that doesn't get checked which contains graphs etc. All sheets bar this one should be filtered.

>
Also, as a side note, I realised my idea was bad pratice as I'm effectively creating 12 5+mb sheets which should be a great deal smaller.
I'm thinking that because I use the 'main' sheet I don't want to delete any unfiltered/selected cells before saving as I may not be able to undo the action so that leads me to think that I'd need to create a new sheet for every filter (patch 3) and then save These sheets only as a new workbook, then delete sheets and repeat process.

Is this doable at all?

I'm sent a 'comprehensive' sheet as a report each week. I need to keep a copy of it intact.

I think I made a poor effort at explaining what I'd like.

I'd like each of the sheets within the workbook to be filtered. Then save only the selected data as a new file.

I'd then need to repeat this process but with a different filter name.

If I can do this without compromising the integrity of the original, I'd be happy And within a loop of course Many thanks again!

Yes, they're all patch and then a number, patch 1, patch 2,...,patch 12.

Hi,

I'm afraid I can't provide sample data due to it's nature.

I've attached a very basic workbook that comprises some of the same features.

testSheet.xls

I've had a play with some for each statements but can't seem to get it working. I keep tinking of how I'd do it in java and it's no good at all. VBA is a mystery. As always, any help is greatly appreciated.