What would the naming convention be for the copied sheets?

Here's a macro from Dave Peterson that copies a sheet named Template and
names each copy according to a list of names on a sheet named List.

Post back with more info if not what you want.

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: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
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

On Wed, 1 Apr 2009 10:09:01 -0700, tutink777
wrote:

Gord,

I want to copy a specific sheet I already created (inlcuding its formulas,
etc) and change the name of that sheet, 55 times. Can you tell me the macro
to use to do that? Thanks so much for any help.

Michelle

"Gord Dibben" wrote:

I may have misread your post.

The macro I supplied adds new sheets and names them.

Do you also want the incremented date entered in a cell on each sheet?

Do you want to copy a specific sheet rather than create new sheets?


Gord

On Thu, 30 Aug 2007 08:07:57 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Sub Add_Year_Sheets()
Dim dFrom As Date, dTo As Date
Dim d As Date, i As Long

dFrom = CDate("1/Jan/07") ' edit to suit
dTo = CDate("31/Dec/07") ' edit to suit
dTo = dFrom + 364 ' 365 if above year is a leap year

n = Worksheets.Count - 1

For d = dFrom To dTo
'If Weekday(CDate(d), 2) < 6 Then
n = n + 1
Worksheets.Add(After:=Worksheets(n)).Name = Format(d, "ddd dd mmm")
'End If
Next

Worksheets(1).Activate
End Sub


Gord Dibben MS Excel MVP

On Wed, 29 Aug 2007 09:42:03 -0700, Den wrote:

This looks like something I want to do for a daily log i'm making. Can you
tell me how to create the macro?

"Kaybay" wrote:

I'm trying to create a new sheet for each day of the year. It needs to
contain the day and date but I really don't want to copy and paste 2 years of
forms and manually change the date. Please can anybody help.