Sub AddTopStockOutKitsWeek1()
Dim N As Long
Dim sName As String
Dim sNum As String
Dim itm As Long
With ThisWorkbook.Worksheets
For N = .Count To 1 Step -1
sName = .Item(N).Name
If LCase(Left(sName, 23)) = "Top Stock Out Kits Week" Then
sNum = Right(sName, Len(sName) - 23)
itm = N
.Item(N).Name = "Top Stock Out Kits Week" & CLng(sNum) + 1
End If
Next N
if itm = 0 then itm = 1
.Add(befo=.Item(itm)).Name = "Top Stock Out Kits Week1"
End With

Should work if the names are Top Stock Out Kits Week

--
Regards,
Tom Ogilvy


"Duncan J" wrote in message
...
Sorry Tom the tab names are
Top Stock Out Kits Week1, Top Stock Out Kits Week2, Top Stock Out Kits
Week3, and so on.
I changed the names in your code and it still wouldn't work. I also
changed the tab names in the spreadsheet to match your code. It debugs at
this line.
.Add(befo=.Item(itm)).Name = "TopStockWeek1"
As I said I renamed them to match the tabs in your code but still got the
error.
Thanks... I'm having a lot of fun with this one.