Copy a template sheet if it doesnt exist from names in a column

  • Hi, I am very new to VBA and a learner. I have a series of names in a column in a sheet called "Names". I want to copy a template sheet for each of these names if it does not exist and rename the sheet to the name.


    I have got this code but it is erroring in various places. can someone please help?


  • Re: Copy a template sheet if it doesnt exist from names in a column


    "erroring in various places" is not very informative.


    Because of the 'Resume Next' statement, the only place it can actually throw an error is

    Code
    Set myrange = Worksheets("Names").Range("A1") 
        Set myrange = Range(myrange, myrange.End(xlDown))


    Assuming you DO have a worksheet named 'Names' then the error is probably in the 2nd of those lines. A long hand, and not very efficient way, to assign a range like that is to replace both those lines with

    Code
    '// TYped freehand - Untested
        Set myrange = Worksheets("Names").Range(Worksheets("Names").Range("A1"), Worksheets("Names").Range("A" & Rows.Count).End(xlUp))


    Every reference to a cell address is qualified with the worksheet name.


    Please help others to help you - give full information about an error; where it occurs and any message displayed.

  • Re: Copy a template sheet if it doesnt exist from names in a column


    Hi,
    Its working. Thanks.
    If i have multiple values in my source column it is working but if i have only 1 value, then it is throwing the below error:


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


    Sheets(Sheets.Count).Name = c.Value --- is highlighted for the error.

Participate now!

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