Thanks so much Trowa! Now it works!
May I ask one more thing? I got the list in Sheet1 with two columns as below?
Is it possbile to copy each Emp Code to Cell $C$7 in the corresponding worksheet (those automatically generated ones from the "Template" worksheet) as well?
Emp Code Employee Name
My current workings are below:
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Sheet1").Range("B2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets(Sheets.Count).Name = MyCell.Value