Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names
 

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

Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names
 

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 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

Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names
 

On Feb 7, 7:27*pm, Don Guillett wrote:
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 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- Hide quoted text -

- Show quoted text -

Works like a charm! You are a great help...always.
Thanks a lot