Macro to create multiple sheet, copy certain values and loop until end of row data

Got it,Rory! You've been a big help! One more request, what if aside from the autopopulate there is a bunch formula as well which are linked to the autopopulated sheets made by the macro, they are on absolute reference. Let me make it clear:

A. B. C. D. E.
1 name rank office =sheet2!b$4$ =sheet2!b$6$
2 ..
3 ..

The macro you gave me really saved me. I would appreciate it if you could help me further. Thanks again, Rory.