How to copy a sheet and move it to 2nd last (with VBA)

I have a workbook that will contain variable number of sheets and the sheets
names are numerical but not necessarily consecutive. I am trying to write a
macro that will copy a template sheet and move it to the second last sheet in
the workbook ("Template" sheet) is the final sheet. As mentioned, the new
sheet will have a numerical name (i.e. "15").
Any help is appreciated as I just can't get my head around this.
Thanks in advance,
Brad

Sub test()
Dim i As Long, wks As Worksheet, lngMax As Long

For Each wks In Worksheets
If IsNumeric(wks.Name) Then If CLng(wks.Name) > lngMax Then lngMax =
CLng(wks.Name)
Next

i = Worksheets.Count
Worksheets("Template").Copy Before:=Worksheets(i)
Worksheets(i).Name = CStr(lngMax + 1)
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brad K." <[email protected]> wrote in message
news:[email protected]...
>I have a workbook that will contain variable number of sheets and the
>sheets
> names are numerical but not necessarily consecutive. I am trying to write
> a
> macro that will copy a template sheet and move it to the second last sheet
> in
> the workbook ("Template" sheet) is the final sheet. As mentioned, the new
> sheet will have a numerical name (i.e. "15").
> Any help is appreciated as I just can't get my head around this.
> Thanks in advance,
> Brad