Subscript out of range (Error 9)
 

Hi folks
I have a macro run for a form combo box, all works OK

I wish to use at vb combo box, have placed my code inside to run on
change .
All runs, I copy my template spreadsheet and go to rename it based on
the date selected from my combo box, the rename fails, error 9

Sdate = Format(cmbDate.Value, "dd mm")

' append name of sheet based on choice of day or night roster
If Sheets("menu").optDay.Value = True Then ' Day Roster
' Day roster
Sname = Sdate & "-D"
Else
' night roster
Sname = Sdate & "-N"
End If

' check if sheet by that name exists, if false proceed
If SheetExists(Sname) = False Then
Sheets("Menu").Select
If Sheets("menu").optDay.Value = True Then ' if Day Roster,
copy day roster template
Sheets("Template").Visible = True
Sheets("Template").Select
Sheets("Template").Copy Befo=Sheets(2)
Sheets("Template (2)").Select <= Error 9 HERE
Sheets("Template (2)").Name = Sname

I think I have a hint of the problem, is it the () characters?
Any fix possible?

thanks
John
Images of home (NZ)
http://www.titahi-bay.co.nz/home
What we are up to in the UK
http://www.titahi-bay.co.nz

Subscript out of range (Error 9)
 

The new sheet (copy) will be the activesheet, so you don't need to select
it. You can refer to it as the activesheet

Sdate = Format(cmbDate.Value, "dd mm")

' append name of sheet based on choice of day or night roster
If Sheets("menu").optDay.Value = True Then ' Day Roster
' Day roster
Sname = Sdate & "-D"
Else
' night roster
Sname = Sdate & "-N"
End If

' check if sheet by that name exists, if false proceed
If SheetExists(Sname) = False Then
Sheets("Menu").Select
If Sheets("menu").optDay.Value = True Then ' if Day Roster,
Sheets("Template").Visible = True
Sheets("Template").Select
Sheets("Template").Copy Befo=Sheets(2)
Activesheet.Name = Sname

--
Regards,
Tom Ogilvy


"John in Surrey" wrote in message
...

Hi folks
I have a macro run for a form combo box, all works OK

I wish to use at vb combo box, have placed my code inside to run on
change .
All runs, I copy my template spreadsheet and go to rename it based on
the date selected from my combo box, the rename fails, error 9

Sdate = Format(cmbDate.Value, "dd mm")

' append name of sheet based on choice of day or night roster
If Sheets("menu").optDay.Value = True Then ' Day Roster
' Day roster
Sname = Sdate & "-D"
Else
' night roster
Sname = Sdate & "-N"
End If

' check if sheet by that name exists, if false proceed
If SheetExists(Sname) = False Then
Sheets("Menu").Select
If Sheets("menu").optDay.Value = True Then ' if Day Roster,
copy day roster template
Sheets("Template").Visible = True
Sheets("Template").Select
Sheets("Template").Copy Befo=Sheets(2)
Sheets("Template (2)").Select <= Error 9 HERE
Sheets("Template (2)").Name = Sname

I think I have a hint of the problem, is it the () characters?
Any fix possible?

thanks
John
Images of home (NZ)
http://www.titahi-bay.co.nz/home
What we are up to in the UK
http://www.titahi-bay.co.nz