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

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

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 Befo=Worksheets(i)
Worksheets(i).Name = CStr(lngMax + 1)
End Sub


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


"Brad K." wrote in message
...
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