By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,622 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,622 IT Pros & Developers. It's quick & easy.

Need a basic VB loop

P: 2
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?

Expand|Select|Wrap|Line Numbers
  1. For each office (name1, name2,  name3 ...)
  3. Sheets("MA").Select
  4.     ActiveWindow.SmallScroll Down:=-15
  5.     Selection.AutoFilter Field:=6, Criteria1:="name"
  6.     ActiveWindow.SmallScroll Down:=-42
  7.     Range("A2:e1178").Select
  8.     Selection.Copy
  9.     Sheets("name").Select
  10.     Rows("2:2").Select
  11.     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  12.     ActiveSheet.Paste
  13.     ActiveWindow.SmallScroll Down:=-18
  14.     Sheets("MA").Select
  15.     Range(Selection, Cells(1)).Select
  16.     Selection.AutoFilter Field:=6
  17.     Range("A2").Select
  18.     Application.CutCopyMode = False
  20. next office
and I don't know how to do a loop.

thanks in advance
Mar 16 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 5K+
P: 8,434
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.
Mar 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.