Help with Code

[Ged Traynor]

Hi All

Just wondering if some could help me with some code. Basically what I'm trying to do is have a sheet called "Dates" with a range that contains a list of dates in it, for example A1:A30
I need VBA Code to look at this date range and copy a sheet called "Template", so if A1 contains 16/06/2011 the code will copy the "Template" sheet and rename it to the value of cell A1 etc

I tried using the below code, which works to a certain extent, but I do get an error on this line Sheets(Sheets.Count).Name = Item
How can I modify the code to copy the "Templates" sheet and rename it as above.

Sub CreateAndNameWorksheets()
Sheets("Dates").Select
Words = Cells.Range("A1:A23")
For Each Item In Words
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Item
Next Item
End Sub

Thanks in Advance
Ged Traynor

 

[VoG]

Try

Sheets(Sheets.Count).Name = Format(Item.Value, "dd-mm-yyyy")

 

[Ged Traynor]

Thanks for the reply VoG

Still getting a runtime error on the line
Sheets(Sheets.Count).Name = Format(Item.Value, "dd-mm-yyyy")

What I also need to do is copy the Template sheet, the code here only creates new sheets and renames them, I need to copy the Template sheet and rename it.
The below code will create the new sheets for me and rename them, but I'm trying to copy the Template sheet and rename that with the values in the range A1:A30

Sub CreateAndNameWorksheets()
Sheets("Dates").Select
Words = Cells.Range("A1:A23")
For Each Item In Words
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Item
Next Item
End Sub

Thanks Again

 

[VoG]

Try this

Sub CreateNewSheets()
Dim i As Long
With Sheets("Dates")
    For i = 1 To 23
        Sheets("Template").Copy after:=Sheets(Sheets).Count
        ActiveSheet.Name = Format(.Range("A" & i).Value, "dd-mm-yyyy")
    Next i
End With
End Sub

 

[Ged Traynor]

Thanks again VoG

Code looks good but getting Type Mismatch error on the following line
Sheets("Template").Copy after:=Sheets(Sheets).Count

 

[VoG]

Oops!

Sub CreateNewSheets()
Dim i As Long
With Sheets("Dates")
    For i = 1 To 23
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(.Range("A" & i).Value, "dd-mm-yyyy")
    Next i
End With
End Sub

 

[Ged Traynor]

Hi VoG

Nearly there I think, seems to work it does create the sheets as requested but I'm getting a runtime error 1004
Application-Defined or Object-Defined error on this line

ActiveSheet.Name = Format(.Range("A" & i).Value, "dd-mm-yyyy")

I notice that it's adding another sheet at the end called Template (2)

Thanks for your help so far with this
Ged

 

[VoG]

I've tested it and it works for me. Are the dates on the Dates sheet true Excel Dates. What does

=ISNUMBER(A1)

return?

 

[Ged Traynor]

Hi VoG

Nearly there I think, seems to work it does create the sheets as requested but I'm getting a runtime error 1004
Application-Defined or Object-Defined error on this line

ActiveSheet.Name = Format(.Range("A" & i).Value, "dd-mm-yyyy")

I notice that it's adding another sheet at the end called Template (2)

Thanks for your help so far with this
Ged

Hi VoG

I think I found the solution to the error, if I don't have 23 rows it will throw up the error, so just have to adjust For i = 1 To 23 to suit the number of rows that actually contains data.

One more question, is it possible for the code to run without known the number of rows? so If the user has only entered data into 20 rows I won't get the runtime error

Thanks Again

 

[VoG]

Try this

Sub CreateNewSheets()
Dim i As Long, LR As Long
With Sheets("Dates")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(.Range("A" & i).Value, "dd-mm-yyyy")
    Next i
End With
End Sub