It doesn't have to already exist:

dim NewWks as worksheet
dim NewWkbk as workbook
dim myCell as range
Dim WordApp as Object
dim WordApp2 as Word.Application

set NewWks = worksheets.add
set NewWkbk = workbooks.add
set myCell = workbooks.add(1).worksheets(1).range("A1")
Set WordApp = createobject("Word.Application")
set WordApp2 = New Word.Application

(but outside of .add or createobject or New or... I can't think of anything
else...)

JLGWhiz wrote:

When you use the Set statement to create an object variable, the object must
already exist. The error occurs if the value in the cell does not equal an
existing object, or if VBA cannot find the object where it would expect the
object to be stored. In this case you are using a list of names for for
worksheets and VBA is telling you that it cannot find one or more of those
names as an existing worksheet. Check the spelling of the name in the list,
make sure the name is for an existing worksheet and not a chartsheet. You
can also manually execute the code using F8 to see what value myCell has at
the time you attempt to Set the object variable. The key factor is that it
is not finding a qualified object to Set.

"K" wrote in message
...
Hi all, I have macro below in which i am getting error on line "Set
Wks = Worksheets(myCell.Value)" and in error message it says
Run-time error '9':
Subscript out of range

Please can any friend guide me what i should be doing

Private Sub CommandButton1_Click()
'CREATE TABS
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Dim Wks As Worksheet

Set TemplateWks = Worksheets("TEMPLATE")
Set ListWks = Worksheets("SUMMARY")

With ListWks
Set ListRng = .Range("H7:H18")
End With

For Each myCell In ListRng.Cells
Set Wks = Nothing
On Error Resume Next
Set Wks = Worksheets(myCell.Value)
On Error GoTo 0
If Wks Is Nothing Then

TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Err.Clear
End If
Else
Beep

MsgBox myCell.Value & " already exists"
End If
Next myCell
End Sub


--

Dave Peterson