Help Modify Copy and Rename a Sheet Multiple Times

[richardking]

Hi Guys, I have a worksheet that has the following tabs:

1. Template
2. List

The Template tab is like the base tab.
The List tab has a list of numbers in column A that starts in row 2. Row 1 contains the header. The items in the List tab varies.

What the worksheet does are as follows:
1. Duplicate the Template tab to as many times as the number of items there are in the List tab (starting in row 2).
2. Each new tab should is renamed according to the values from the List tab

So if there are 3 items on the List tab like, 123,456, and 789. The new duplicate tabs are named as 123, 456, and 789.
The VBA on the worksheet is able to do that. However, I would like to make additional changes.

1. After a new duplicate is made and renamed, whatever the name of the new duplicate tab should also be written/pasted "as values" in cell C2.
So from the same example as above, if the new duplicate tab is renamed into 123, the number 123 should also be written/pasted "as values" in cell C2.
2. If the name of a newly duplicated tab already exists, a messagebox would appear and say something then stops and exits the sub.

The code is show below.

Please Login or Register  to view this content.

[Logit]

.
It appears your macro does everything you require ... as is.
Was there something else you were seeking or did you post this for the benefit of others needing a macro
to create duplicate sheets ?

[richardking]

Hi Logit,

Its missing the part where I mentioned:

However, I would like to make additional changes.

1. After a new duplicate is made and renamed, whatever the name of the new duplicate tab should also be written/pasted "as values" in cell C2.
So from the same example as above, if the new duplicate tab is renamed into 123, the number 123 should also be written/pasted "as values" in cell C2.
2. If the name of a newly duplicated tab already exists, a messagebox would appear and say something then stops and exits the sub.

I am hoping someone could help me with that.

[Logit]

.
The only change I made to your macro was adding the "Exit Sub" towards the bottom and changed the range from
A1 to A2.

The remainder of the macro runs as you have described in your post. So ... I am missing something from your description.

Please reword.

Download sample workbook : https://www.amazon.com/clouddrive/sh...FPSCYZ9a7HJ1vk

[jolivanes]

Below this line

Please Login or Register  to view this content.

add this line

Please Login or Register  to view this content.

[Logit]

.
Ahh .. I see what you are saying now. Didn't look closely enough.

Please Login or Register  to view this content.

[richardking]

Hi Logit, thank you. Your solution works, except for the part wherein if there's an error due to a duplicate name, it would still create a new tab.
I also forgot to mention, if I make a filter of the list, it would still create the same number of duplicate tabs. Though, I have already thought of a solution like creating and temp sheet then deleting that after.
However, if there's a way to skip creating a temp tab, I think it would be better.

[Logit]

.
Ok ... will you consider taking a different approach ? Complete a search of Column A of the LIST sheet and delete any dupes.
Then run the macro for creating sheets.

Please Login or Register  to view this content.

[richardking]

That's a good approach. However, it will still create a new duplicate tab, though with a generic name, if an error occurs.
It would still create the same number of duplicate tabs regardless if you filter the list or not.

[Logit]

.
Let's change macros again :

Please Login or Register  to view this content.

[richardking]

I really appreciate you taking the time in writing those codes. Your new macro was certainly able to stop and end the sub when there is a duplicate name without creating the unnecessary duplicate tab.
It's the list when its filtered that's left. I, however, have drafted an incomplete code wherein it would copy the filtered list, create a temp sheet, then make that temp sheet as the base for the names and number of duplicate tabs.
Then delete that temp sheet at the end. The rest of the codes are your codes.

[Logit]

.
Just so long as you have the final product you were seeking. If it all works .... it is all good.

Anything else or are you good to go ?

[richardking]

Yes it is. Thank you so much.