Thank you so much Dave. I tweeked the code a bit to suit my liking, and it
seems to be working fine. What is the "Option Explicit" and the "On Error
GoTo 0" for?

The following code works very well. It copies the template sheet, and does
nothing when there isn't one (which is exactly what I need). Sorry if I
confused you, but with the second part I was trying to adapt the macro to
other files, and didn't specify it. Thank you both for helping me.

Sub Copy_Template()
Dim sh As Worksheet
Set sh = Nothing
On Error Resume Next
Set sh = Sheets("Template")
If Not sh Is Nothing Then
sh.Copy Befo=sh
ActiveSheet.Name _
= Format(Date, "mm-dd-yyyy") _
& " ; " & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")
End If
End Sub

"Dave Peterson" wrote:

#1. You could use something like:

Sheets("Template (2)").Name _
= Format(Date, "mm-dd-yyyy") _
& ";" & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")


#2. Subscript out of range in this case means that the activeworkbook doesn't
have a worksheet named "Template". Are you sure you were looking at the correct
workbook?

Maybe something like this with some checks would work ok:

Option Explicit
Sub Copy_Template2()

Dim sh As Worksheet

Set sh = Nothing
On Error Resume Next
Set sh = Sheets("Template")
On Error GoTo 0

If sh Is Nothing Then
MsgBox "No sheet named Template in activeworkbook"
Else
sh.Copy Befo=sh
'activesheet is the sheet just copied.
ActiveSheet.Name = Format(Date, "mm-dd-yyyy") _
& ";" & Format(Year(Date), "yy") _
& Format(Date - DateSerial(Year(Date), 1, 0), "000")
End If
End Sub



Sloth wrote:

Thanks for your reply.

1. I was wanting to use VBA coding to create the text string, instead of
pulling from K2. This would help if I need to change in the template I won't
have to change the code as well. It is not a big deal, but I wanted to know
if and how to do it in the macro. Sorry for being unclear.

2. I figured out I can put the code in the Module1 under the Personal.XLS
to keep the other file opening (the one with the template that is). But when
I push the button I get an error message. At this line saying the subscript
is out of range.

Set sh = Sheets("Template")

I tried rearanging things and this did not work either (same error, but at
the if statement).

Sub Copy_Template()
If Not Sheets("Template") Is Nothing Then
Dim sh As Worksheet
Set sh = Sheets("Template")
sh.Copy Befo=sh
ActiveSheet.Name = Range("K2").Value
Range("K2").ClearContents
End If
End Sub

"Bob Phillips" wrote:


"Sloth" wrote in message
...

and the code is

Sub Copy_Template()
Sheets("Template").Copy Befo=Sheets("Template")
Sheets("Template (2)").Name = Cells(2, 11)
Range("K2").Select
Selection.ClearContents
End Sub

I am extremely new to VBA coding and created this code by editing a
recorded
macro. I have three questions concerning this macro.
1. How can I include the K2 formula in the coding?

It already does. Cells(2,11) and Range("K2") is K2

2. I created a custom button and added it to the toolbar. If the file is
not open, I want the button to do nothing, or bringup an error message
(something like "This button is unavailible). Is this possible, or
something
equally effective?

Could try this

Sub Copy_Template()
Dim sh as worksheet
Set sh = Sheets("Template")
If Not sh Is Nothing Then
sh .Copy Befo=sh
Activesheet.Name = Range("K2").Value
Range("K2").ClearContents
End If
End Sub


3. For you programers out there, is there anything majorly wrong with
this
macro? In works for now, but I don't want to accidently overwrite, or
delete, anything in the future.

Should be okay now it uses explicit references.

Also, can anyone suggest a good book to learn VBA programming?

John Walkenbach's VBA For Dummies.




--

Dave Peterson