Macro to copy sheet, rename from list, and skip duplicate

[jlgrunsky]

Hi all,

I am new to the forum. I have been using it for excel help for years and never had a need to ask my own question until now. I have searched high and low for the answer to this. I am sure it is simple, but I cant find it anywhere.

I have an excel macro that is duplicating a template and renaming the tabs from a list. What I would like, is to be able to run the macro more than once (i.e. if someone adds to the list) without getting a bug error. So, I need to add to this macro to tell excel to just overlook the duplicates and keep going until it gets to the next unique name.

Can someone please help me? Here is my code:

Sub CreateSheetsFromAList()
Dim MyCell, MyRange As Range
Set MyRange = Sheets("Opportunity Pipeline").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Template").Visible = True
For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = MyCell.Value ' renames the new worksheet
   
Next MyCell
Sheets("Template").Visible = False
End Sub

 

[My Aswer Is This]

I suggest you have the script clear the range at the end. To have the script keep running over the same range of sheet names and ignore the duplicates is not a good way to do things.

 

[jlgrunsky]

I suggest you have the script clear the range at the end. To have the script keep running over the same range of sheet names and ignore the duplicates is not a good way to do things.

Sure, I'm open to trying that. Can you tell me how to add that to my code? Thanks.

 

[My Aswer Is This]

Try this:

Sub CreateSheetsFromAList()
Dim MyCell, MyRange As Range
Set MyRange = Sheets("Opportunity Pipeline").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Template").Visible = True
For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = MyCell.Value ' renames the new worksheet
   
Next MyCell
MyRange.ClearContents
Sheets("Template").Visible = False
End Sub

 

[jlgrunsky]

Try this:

Sub CreateSheetsFromAList()
Dim MyCell, MyRange As Range
Set MyRange = Sheets("Opportunity Pipeline").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Template").Visible = True
For Each MyCell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = MyCell.Value ' renames the new worksheet
   
Next MyCell
MyRange.ClearContents
Sheets("Template").Visible = False
End Sub

Ah, now I see what you meant. That works, thank you, but the problem is that I am using that list for other reports so I need it to stay put. With that said, I revert back to my original request. Any other ideas?

 

[My Aswer Is This]

I have no other suggestion. But if your wanting to create new sheets from a list of values you need to find some other place to put and refer to to create new sheets.