Need a basic VB loop

[markwood2k]

Hi all, I'm in over my head.

The middle part below ( My recorded macro) works fine, but I'd like some help making it loop. can you help?

For each office (name1, name2,  name3 ...)
 
Sheets("MA").Select
    ActiveWindow.SmallScroll Down:=-15
    Selection.AutoFilter Field:=6, Criteria1:="name"
    ActiveWindow.SmallScroll Down:=-42
    Range("A2:e1178").Select
    Selection.Copy
    Sheets("name").Select
    Rows("2:2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-18
    Sheets("MA").Select
    Range(Selection, Cells(1)).Select
    Selection.AutoFilter Field:=6
    Range("A2").Select
    Application.CutCopyMode = False
 
next office

and I don't know how to do a loop.

thanks in advance

[Killer42]

I think a lot would depend on where these names are coming from. If it is a set list that you just want to hard-code here, then a couple of techniques come to mind.

• One would be to put them in an array, then use a FOR loop to traverse the array (you use the loop counter as the index into the array).
• Another fairly simple (though slightly ugly) method would be to place the bulk of your code in another Sub and invoke it with each name in turn. For example:

  DoMyStuff "Name1"
  DoMyStuff "Name2"
  And so on.