How do you copy a worksheet and rename it to a number

[M Hill]

Hi guys,

I have a workbook that contains the following worksheets named '1', '2',
'3', and template.

I am trying to copy the template worksheet, and rename it to the next number
'4', '5' and so on...
Can you do this automatically through a macro?

Any help greatly appreciated.

--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[J.E. McGimpsey]

One way:

Public Sub CopyTemplate()
Dim nSheets As Integer
nSheets = Worksheets.Count
Worksheets("Template").Copy After:=Sheets(nSheets)
ActiveSheet.Name = nSheets
End Sub

 

[M Hill]

JE, you are a true wiz, thank you.
I hope you dont mind me asking a few more things.
I tested it and it works.
I assigned a picture text box to the macro, but when I run it though, the
text box disappears. I must be thick.
How do I keep the text box there so I can run the macro whenever I want?
Also, JE could you explain why the procedure you created is a public sub and
not just a normal one?
I really appreciate all your help mate.



--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[M Hill]

Sorry JE,
please dont worry about the text box thing, that was just me.

I am still working on this project, so I hope you dont mind if I get stuck I
ask another question or so if i need to.



--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[J.E. McGimpsey]

All subs are public unless declared Private, or the module that
contains them has Option Private at the top. My using "Public" just
makes it explicit.