copy sheet a # of times based on cell value

Hello
I need to create a Macro for the following.
I have a workbook with 3 sheets. I need to create a copy of the third sheet a certain number of times based on the value in cell "O1" of the first worksheet. Once each sheet is created I need the sheet to automatically be renamed based on the the value in "K5" of the newly created sheet. If possible I would prefer to use the sheets index numbers as opposed to their names . Thanks for any help.

Hello Nuver,

For this code example, I am going to use Worksheet names "Sheet1", "Sheet2", and "Sheet3".You will need to make name changes as needed in the code. This macro uses the worksheet index number to name the newly added sheets.

Please Login or Register  to view this content.

If you have any questions orproblems, contact via e-mail at [email protected].

Sincerely,
Leith Ross

Thank you Leith
The code worked perfectly to create the sheets copying them from the third sheet. Thanks for the help. The second part of the request did not work however. I probably did not make my self clear enough.

I need each newly created sheet to be named based on the value of "K5" in that respective sheet. I have a formula in "K5" that calculates a unique name that I need each sheet to be named from. Also the name of the third sheet changes based on the value in cell "K5" of that sheet also which changes automatically every month. Below is the code I have pasted in the sheet to rename the thrid sheet. Any ideas on how to make this work? Again thanks for your help I am much closer to acomplishing this task with your suggestion.


Private Sub Worksheet_Calculate()
Application.EnableEvents = False

On Error Resume Next
Me.Name = Me.Range("K5").Value

Application.EnableEvents = True
End Sub