Should work in version xl2003 and later
Sub CreateSheetsFromAList()
Dim MyCell As Range
Dim MyRange As Range
Set MyRange = Sheets("Summary").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
With ActiveSheet
.Name = MyCell
Sheets("sheet1").Range("a2:h12").Copy .Range("b2")
.Range("a2:a12,b1") = MyCell
End With
Next MyCell
End Sub
On Feb 7, 12:44*am, prkhan56 <prkha...@gmail.com> wrote:
> Hello,
> I am using Excel 2010.
>
> I got this macro which makes new sheets according to the Names on the
> Summary Sheet.
>
> Sub CreateSheetsFromAList()
> Dim MyCell As Range, MyRange As Range
>
> Set MyRange = Sheets("Summary").Range("A2")
> Set MyRange = Range(MyRange, MyRange.End(xlDown))
>
> For Each MyCell In MyRange
> Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
> Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
> Next MyCell
> End Sub
>
> I also wish to achieve following with the macro.
> 1) It should copy the Data from Sheet1 (A2:H12) on all the newly
> created sheets in the Range B2:I2 on each sheet.
> *2) The name of each Sheet should appear in Cell B1 on all sheets and
> also in the range A2:A12 on all the sheets creates.
>
> Thanks in advance
> R Khan
