VBA, cut data based on filter, save workbook as....

[_BigBear]

Hi Everyone,

Below is a segment of code that I have recorded for an instance in a macro that I hope to fully automate.

I have a column (J) of account managers in my '2014-2015' tab, the idea is to cut all the data except for the account manager who I have selected, refresh the entire workbook as the tab drives pivot tables, then save the workbook as a new copy based of the account managers name.

The account managers are as follows: Adam, Billy, Brennan, Michael, Michael H, and "other"

The code below would just be one instance of selecting an account manager, then clearing the remaining data out for the others. I figured it I could get a working block of code for one, then there would be a way to loop through the procedure.

In the case below, I actually used the filter to select every account manager but the first in the autofilter array, then I deleted out the remaining data, I am not sure which way is easier.

If anyone has suggestion on how to make this code usable for a repeatable procedure I would appreciate any and all help as I am a novice when it comes to VBA.

Thanks,

J

Sub DataSplit()


    Sheets("2014-2015").Select
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveSheet.Range("$A$1:$U$295313").AutoFilter Field:=10, Criteria1:=Array( _
        "Billy", "Brennan", "Michael", "Michael H", "Other", "=") _
        , Operator:=xlFilterValues
    Rows("197:197").Select
    Range("D197").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Sheets("ECTS_Rev_Pivot").Select
    ActiveWorkbook.RefreshAll
    ActiveWindow.SmallScroll Down:=-9
    ChDir "Z:\"
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\2014-2015 Billing Reports_macro build_adam.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub