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