hi
with this line.....
Set Wks = Worksheets(myCell.Value)
excel is looking for the name of a worksheet not the value of mycell.
try
Set Wks = myCell.Value
now the variable Wks has been set to the value of mycell.

regards
FSt1


"K" wrote:

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