Solved: Add sheets based on rows

[sujittalukde]

I have a workbook named ?RowBasedSheet.xls? which contains data across rows in various sheets.
The rows contains the name of the employees and other related data.
I want to create a output report for all the rows ie employees to a new workbook by taking values from different sheets.
A sample WB is attached to clarify the matter.

Note that in all the sheets, the name of the employees will be same in order, position etc.
While transferring data from the rows to the new workbook and placing the data to new WB, some calculations has to be made also which will be same for all the records.

How can this be done?

Other thing is that as the report in new workbook will be in a fixed format can I use the Template(xlt) file ie One template file will the saved and sheets will be added for the rows?

[mdmackillop]

Add a template to your original workbook

Option Explicit
Sub CopyData()
Dim Arr(), a, tp As Worksheet, sh As Range
Dim i As Long
Application.ScreenUpdating = False

'Put names into an array
ReDim Arr(0)
i = -1
With Sheets("Sheet1")
For Each sh In Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
i = i + 1
ReDim Preserve Arr(i)
Arr(i) = sh
Next
End With

'Add name to template
Set tp = Sheets("TPlate")
For Each a In Arr
tp.Range("B1") = a
'Copy to a new sheet
tp.Copy After:=Sheets(Sheets.Count)
'Rename
Sheets(Sheets.Count).Name = a
'Fix values
Sheets(a).Range("B1:B7").Value = Sheets(a).Range("B1:B7").Value
Next
'Move name sheets to new workbook
Sheets(Arr).Move
Application.ScreenUpdating = True
End Sub

[sujittalukde]

Thanks md, its working great.