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

[Guest]

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

 

[Rob van Gelder]

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