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

  • Thread starter Thread starter prkhan56
  • Start date Start date
P

prkhan56

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









- Show quoted text -
Click to expand...

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