Copy sheet and rename from a list, ignore duplicates

  • 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.


    The list i am using is on a tab called "Opportunity Pipeline" in column A. The tab I would like to copy and rename is called "Template" - the "Template" is hidden so I also need the code to unhide the template, copy it, rename it (multiple times) and then hide it again.


    Can someone please help me? Here is my code:

    Code
    Sub CreateSheetsFromAList()Dim MyCell, MyRange As RangeSet MyRange = Sheets("Opportunity Pipeline").Range("A3")Set MyRange = Range(MyRange, MyRange.End(xlDown))Sheets("Template").Visible = TrueFor Each MyCell In MyRange    Sheets("Template").Copy After:=Sheets(Sheets.Count)    ActiveSheet.Name = MyCell.Value ' renames the new worksheet   Next MyCellSheets("Template").Visible = FalseEnd Sub
  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello,


    You could test the following



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates



    Hi Carim,
    Thank you for the help. I ran a test on this and it does not work. I was able to run the macro once successfully, but upon adding a new name to the list and running a second time, I got the following error:


    Run-time error '1004':
    Application-defined or object defined error


    Thoughts?

  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello,


    Cannot guess where the code stops with the error ...


    Is there a conflict of worksheet names with the ones just created ...?


    Have you deleted the just-added worksheets before running again the code ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    Carim,


    That seems to be the problem that I cannot find a workaround for. The workbook is a constantly evolving planning tool for sales prospects so I cannot delete the previously entered names from the list and I cannot delete the just-added worksheets before running the code again.


    In June, I may enter Company A, Company B, and Company C into my opportunity pipeline, and then use the macro to create templates for each of the prospects where I will then enter company specific information. A couple months later, I would like to add Company D and Company E to my opportunity pipeline. When I run the macro to create templates for those companies, i get the run-time error because Companies A, B and C are still in the list and the worksheets still exist. I also cannot clear the list each time because it is the first column of a table that contains a lot of other company specific metrics.


    Any other thoughts?

  • Re: Copy sheet and rename from a list, ignore duplicates


    My recommendation would be to have, in the Column next to your names, some sort of a flag to indicate your macro has already dealt with this item ...


    And within your macro ... you can add a test in order to skip the ones that have already been flagged ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    Quote from Carim;788382

    My recommendation would be to have, in the Column next to your names, some sort of a flag to indicate your macro has already dealt with this item ...


    And within your macro ... you can add a test in order to skip the ones that have already been flagged ...


    Hope this will help


    Ok, that could definitely work. A couple of questions about this solution:
    1 - Can the flag be automatic with the macro?
    2 - Can the column with the flag be hidden?
    3 - Do you know how to write this into the macro?


    I am sharing this workbook with colleagues that are VERY basic in their excel abilities. Expecting them to be able to flag the 'old' items before running the macro again is outside their comprehension.


    Thanks!

  • Re: Copy sheet and rename from a list, ignore duplicates


    Fine ...


    The answers to your three questions are positive ... :wink:


    You are right ... there is no need to bother users with issues of this nature ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    Another suggestion ... (the lazy way ...:wink:)


    Could you test the following code :



    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    That worked!! Please accept my BIGGEST gratitude as I have been searching for this solution for months. Please also consider yourself the smartest person I've ever encountered :)


    Hope you have a great day! Thanks for making mine!

  • Re: Copy sheet and rename from a list, ignore duplicates


    Quote from jlgrunsky;788386

    That worked!! Please accept my BIGGEST gratitude as I have been searching for this solution for months. Please also consider yourself the smartest person I've ever encountered :)


    Hope you have a great day! Thanks for making mine!


    Glad it did help you out ...:wink:


    Thanks a lot for your very kind words ...:sing:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    Good morning,


    I've just found this code using Google & wanted to say that I've used it on an excel sheet. It's made one of my tasks significantly easier. Thank you.


    I have only one question:


    Is it possible to adapt this code to skip blank cells in the column? My spreadsheet has a list of reference codes in column A. These codes are divided up into sections with a subheading in column B. Where there is a subheading in column B, the cell in column A is blank.


    E.g. in B1 you have "Timber" and in A2 to A4 'TB-01', 'TB-02', 'TB-03'. Then in B5 "Metal", followed by ME-01, ME-02, ME-03 in A6 to A9. This leaves cells A1, A5 etc blank. On some versions of this sheet, I could have 1 record in each section, in others I could have 10+. There may be anywhere from 2 to 10+ different sections.


    Thank you for your help!

  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello,


    Glad the code is helping you out ...


    From your description ... most probably the code can be adjusted to your exact needs ...


    Why don't you post the code you are currently using ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    The code is from a few posts back. The only changes I made were to the sheet names.




    Thank you for such a speedy reply!

  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello again,


    You could test following ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    I don't think you need a dictionary here.


    an alternative


    [vb]Sub CreateSheetsFromAList() ' Example Add Worksheets with Unique Names

    Dim MyRange As Range, i As Long
    Dim ShtName As String

    Application.ScreenUpdating = 0
    With Sheets("Schedule")
    Set MyRange = .Range("A11:A" & .Range("a" & .Rows.Count).End(xlUp).Row)
    End With

    Sheets("Template").Visible = True

    With MyRange
    For i = 1 To .Rows.Count
    ShtName = Trim(.Cells(i, 1).Value)
    If Len(ShtName) Then
    If Not WorksheetExists(ShtName) Then
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = ShtName
    End If
    End If
    Next
    End With

    Sheets("Template").Visible = False
    Application.ScreenUpdating = 1

    End Sub[/vb]

  • Re: Copy sheet and rename from a list, ignore duplicates


    Thank you for the replies.


    Carim, your code runs up until the blank cell & appears to stop copying from there.


    Krishnakumar, your code shows an error code 400. When you close the error code, it creates the sheets, and an extra one called "Template (2)"
    I inserted this in the end because it wouldn't run otherwise.

    Code
    Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
        On Error Resume Next
        WorksheetExists = (Sheets(WorksheetName).Name <> "")
        On Error GoTo 0
    End Function


    Either of you have any ideas?


    Thank you so far!

  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello,


    A lot depends on how you have defined : Sheets("Schedule").Range("A11")


    Is it a reference column ...???


    Why don't you post a sample workbook ... for testing purposes ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Copy sheet and rename from a list, ignore duplicates


    Hello again,


    Thanks a lot for the sample workbook ...


    Regarding the worksheets to be created ... Is it Column A, B or C to take into account in your "Schedule" worksheet ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!