Maybe "For each" loop in VBA

[Titian]

I have sales data extracted onto Sheet1, some 6000 rows long, it will vary each time. The extract refers to say 12 companies and I now want to filter each company onto a new worksheet in this same workbook.

I have the code to do that, it's recorded and will be cleaned up later:-

ActiveSheet.Range("$A$1:$O$" & Lr1).AutoFilter Field:=2, Criteria1:="375"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "375"
    Sheets("Sheet1").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("375").Select
    Range("A1").Select
    ActiveSheet.Paste
        
    Lr2 = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveWorkbook.Worksheets("375").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("375").sort.SortFields.Add Key:=Range("D2:D" & Lr2), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("375").sort.SortFields.Add Key:=Range("I2:I" & Lr2), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("375").sort
        .SetRange Range("A1:O" & Lr2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The number 375 in the code is the Account Number which will change for each of the 12 or so companies. The new Worksheet numbers will increment from say Worksheet 2 to Worksheet 14 both of which need to be catered for in the new "For Each" loop maybe like:-

For Each Account in Array(375,1166,1698....)

I know this has been on the Board previously but my searching has failed to find the appropriate thread. Any help appreciated again.

 

[jim may]

Goto:
http://www.contextures.com/excelfiles.html#Filter

Scroll down to see:
FL0013 - Create New Sheets from Filtered List -- uses Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter (Get sample file)

 

[Titian]

Thanks Jim.

That looks like a workable solution once I incorporate my sort to leave each worksheet in the order required for the following processing.

I'll have to leave it shortly as England play Scotland at rugby which ought to be a great game (for England!)

Thanks again.