What is the code for the function CBool? It sounds like a custom function.
I suspect that its argument is a text string that represents the name of a
worksheet.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Geoff C" wrote in message
...
Hi people, using various posts in this board, have created the following
code, but it's returning a 424 Object Required error on the line..
SheetExists = CBool(Workbook.Sheets(SheetName) Is Nothing)

Can you see what I'm doing wrong?

Many thanks for any suggestions,
Geoff.


Sub CopyTemplate()
Dim S_No As String
Dim Message, Title, Default, SheetName As String
Dim SheetExists As Boolean

Message = "Enter Subject Number"
Title = "InputBox"
Default = "0000"

'Get subject number
S_No = InputBox(Message, Title, Default)
'error conditions
If S_No = "" Then GoTo nosubentered
If S_No = "0000" Then GoTo nosubentered
'Create sheetname
SheetName = "Sub" & S_No
On Error Resume Next
SheetExists = CBool(Workbook.Sheets(SheetName) Is Nothing)
On Error GoTo 0
If SheetExists Then GoTo subexistsalready
'Copy template sheet into new sheet name
.......(code omitted)......


subexistsalready:
Sheets(SheetName).Select

nosubentered:

End Sub