I have one sheet that serves as an menu to name several new sheets in
my workbook. I would like to add to this code. I need Range("a3") on
each NEW worksheet to contain a heading from the same list. It would
say, "Regional Office 325 2008". The "325" for the header would come
from the sheet.name. Any ideas?

Sub copyCleanTemp()
Dim rngName As Range
Dim i As Integer
Set rngName = ThisWorkbook.Sheets("RegDivList").Range("e2")
Do Until rngName.Value = ""
i = ThisWorkbook.Sheets.Count
Sheets("CleanTemp").Copy After:=Sheets(i)
ThisWorkbook.Sheets(i + 1).name = rngName.Value
Set rngName = rngName.Offset(1)
Loop

End Sub

Suggest a shorter name

Sub copytemp()
mc = "e"
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, mc).End(xlUp).Row
Sheets("CleanTemp").Copy After:=Sheets(i)
myname = "RegOff " & .Cells(i, mc) & " 08"
with ActiveSheet
.Name = myname
.Cells(1, 1) = myname
end with
Next i
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim" wrote in message
...
I have one sheet that serves as an menu to name several new sheets in
my workbook. I would like to add to this code. I need Range("a3") on
each NEW worksheet to contain a heading from the same list. It would
say, "Regional Office 325 2008". The "325" for the header would come
from the sheet.name. Any ideas?

Sub copyCleanTemp()
Dim rngName As Range
Dim i As Integer
Set rngName = ThisWorkbook.Sheets("RegDivList").Range("e2")
Do Until rngName.Value = ""
i = ThisWorkbook.Sheets.Count
Sheets("CleanTemp").Copy After:=Sheets(i)
ThisWorkbook.Sheets(i + 1).name = rngName.Value
Set rngName = rngName.Offset(1)
Loop

End Sub