Maybe "For each" loop in VBA

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
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:-

Code:
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:-

Code:
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,817
Messages
5,525,070
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top