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

M

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

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top