Create and name multiple sheets
See Dave's post for making new books from sheets.
Gord
On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224
wrote:
Gord,
Do you also know how I can save each worksheet as a workbook with the name
of the file being derived from the worksheet name?
"Gord Dibben" wrote:
Mike
Sheet1 will contain your list of names.
Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: 1-1-2007
' and name the sheets accordingly
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Set TemplateWks = Worksheets("1-1-2007")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub
Gord Dibben MS Excel MVP
On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224
wrote:
I currently have a worksheet named 1-1-2007. I need to copy this sheet 60
times and rename each. The names for each sheet are contained on Sheet1
(named "Schedule") and are in column C.
Can I use a macro or an array formula to do this? Any help would be great.
Thanks,
Mike
|