Run-time error 9

[K]

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

 

[FSt1]

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

 

[p45cal]

Since that line is surrounded by On Error Resume Next and On Error GoTo 0,
I'd be surprised if that error didn't come up at all, unlees you remove those
lines. It's half expected that the line will error, if it errors, it means
the sheet tab name doesn't already exist, so one with that name can be added.
If it doesn't error Set Wks sets Wks to something, if it does error, Wks
becomes Nothing, which is tested for in the subsequent line.

I think all is well.

 

[JLGWhiz]

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.

 

[Dave Peterson]

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]

Guess I could have chosen my words better. <g>

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...)

 

[Dave Peterson]

Just that one <vvbg>.

Guess I could have chosen my words better. <g>