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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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.
 

Forum statistics

Threads
1,141,226
Messages
5,705,130
Members
421,379
Latest member
SoundlessResonance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top