insert sheet and naming it

[hans]

I have a workbook containing the workrosters for each week.
the sheets are named 1,2,3,4, etc (This are the week numbers)
Sheet 54 is the original sheet from witch i copy.
Other sheets have normal names like "tijden", "medewerkers", etc etc

If my last rostersheet is named 9, i would like to know how to insert a new
sheet called 10 after 9.

I would like a vba solution.
Can someone help.

Greetings Hans

 

[Markus Scheible]

Hi Hans,

Sheet 54 is the original sheet from witch i copy.
Other sheets have normal names

If my last rostersheet is named 9, i would like to know how to insert a new
sheet called 10 after 9.

try

Sheets("54").Select
Sheets.Add

or if you want to have a copy of sheet 54

Sheets("54").Select
Sheets("").Copy Before:=Sheets("54")


Best

Markus

 

[Tom Ogilvy]

Dim sh as Worksheet, sName as String
Dim MaxWeek as Long, MaxIndex as Long

MaxWeek = 0
MaxIndex = 0
for each sh in ActiveWorkbook.Sheets
sName = sh.name
if isnumeric(sName) then
if clng(sName) < 53 then
if clng(sName) > MaxWeek then
MaxWeek = clng(sName)
MaxIndex = sh.Index
end if
End if
end if
Next
Worksheets.Add(After:=worksheets(MaxIndex)).Name _
= cStr(MaxWeek + 1)

 

[Tom Ogilvy]

Left off the last part

Dim sh as Worksheet, sName as String
Dim MaxWeek as Long, MaxIndex as Long

MaxWeek = 0
MaxIndex = 0
for each sh in ActiveWorkbook.Sheets
sName = sh.name
if isnumeric(sName) then
if clng(sName) < 53 then
if clng(sName) > MaxWeek then
MaxWeek = clng(sName)
MaxIndex = sh.Index
end if
End if
end if
Next
Worksheets.Add(After:=worksheets(MaxIndex)).Name _
= cStr(MaxWeek + 1)
worksheets("54").Cells.copy worksheets(cStr(MaxWeek + 1))

 

[hans]

Thanks, this works perfectly.

Greetings Hans

Left off the last part

Dim sh as Worksheet, sName as String
Dim MaxWeek as Long, MaxIndex as Long

MaxWeek = 0
MaxIndex = 0
for each sh in ActiveWorkbook.Sheets
sName = sh.name
if isnumeric(sName) then
if clng(sName) < 53 then
if clng(sName) > MaxWeek then
MaxWeek = clng(sName)
MaxIndex = sh.Index
end if
End if
end if
Next
Worksheets.Add(After:=worksheets(MaxIndex)).Name _
= cStr(MaxWeek + 1)
worksheets("54").Cells.copy worksheets(cStr(MaxWeek + 1))