vba - Automatically creating worksheets based on a list in excel -

i trying achieve following.

when enter value on 'master' worksheet in range a5:a50, macro run creates new worksheet same name value , copies template onto new sheet.

in addition copy value adjacent value enter on master worksheet new worksheet calculations automatically.

for example enter '1' in a5 , '2' in b5. create new worksheet name '1', copy template 'template' worksheet , copy value of b5 on new worksheet named '1'.

i have following code tries copy template worksheet macro run results in error because worksheet name 'template' exists.

sub createandnameworksheets()     dim c range      application.screenupdating = false     each c in sheets("master").range("a5:a50")         sheets("template").copy after:=sheets(sheets.count)         c             activesheet.name = .value             .parent.hyperlinks.add anchor:=c, address:="", subaddress:= _                 "'" & .text & "'!a1", texttodisplay:=.text         end     next c     application.screenupdating = true  end sub 

right-click master worksheet's name tab , select view code. when vbe opens up, paste following window titled book1 - master (code).

private sub worksheet_change(byval target range)     if not intersect(target, rows("5:50"), columns("a:b")) nothing         on error goto bm_safe_exit         application.screenupdating = false         application.enableevents = false         application.displayalerts = false         application.calculation = xlcalculationmanual         dim r long, rw long, w long         r = 1 intersect(target, rows("5:50"), columns("a:b")).rows.count             rw = intersect(target, rows("5:50"), columns("a:b")).rows(r).row             if application.counta(cells(rw, 1).resize(1, 2)) = 2                 w = 1 worksheets.count                     if lcase(worksheets(w).name) = lcase(cells(rw, 1).value2) exit                 next w                 if w > worksheets.count                     worksheets("template").visible = true                     worksheets("template").copy after:=sheets(sheets.count)                     sheets(sheets.count)                         .name = cells(rw, 1).value2                         .cells(1, 1) = cells(rw, 2).value                     end                 end if                 cells(rw, 1)                     .parent.hyperlinks.add anchor:=cells(rw, 1), address:="", _                         subaddress:="'" & .value2 & "'!a1", texttodisplay:=.value2                 end             end if         next r         me.activate     end if bm_safe_exit:     worksheets("template").visible = xlveryhidden     me.activate     application.calculation = xlcalculationautomatic     application.displayalerts = true     application.enableevents = true     application.screenupdating = true end sub 

note depends on having worksheet named template in order generate new worksheets. keeps template worksheet xlveryhidden means not show if try unhide it. go vbe , use properties window (e.g. f4) set visibility visible.

this routine should survive pasting multiple values a2:b50 discard proposed worksheet names in column exists. there must value both column , column b of row before proceed.

there no checks illegal worksheet name characters. may want familiarize , add error checking.