excel vba - Subscript out of range for setting worksheet -

excel vba - Subscript out of range for setting worksheet -

this code takes row current (data) worksheet, checks see if worksheet exists given name of info row , creates new worksheet based on template if not. then, paste relevant info first blank row in our destination worksheet.

the issue having line 'set destws = sheets(name)', getting "subscript out of range" error. tried multiple fixes , looking @ other solutions , not find solution. error occurs regardless of whether new sheet created or not.

sub pastedata() dim dataws worksheet: set dataws = activesheet dim destws worksheet dim current worksheet dim datarow integer dim destrow integer dim name string dim newsheet boolean 'assumes current worksheet dataws datarow = 5 30 if isempty(dataws.cells(datarow, 3)) = false 'we found our destination sheet , name name = dataws.range("c" & datarow).value each current in worksheets if current.name = name newsheet = true next if newsheet = true create_supplier_sheet activesheet.name = name end if set destws = sheets(name) 'finds next blank row destrow = 5 34 if isempty(destws.cells(destrow, 3)) = false 'found destws.range("c" & destrow).value = dataws.range("d" & datarow).value destws.range("d" & destrow).value = dataws.range("e" & datarow).value destws.range("f" & destrow).value = dataws.range("f" & datarow).value destws.range("g" & destrow).value = dataws.range("g" & datarow).value if dataws.range("d" & datarow + 1).value not dataws.range("d" & datarow).value goto lastline end if next destrow end if next datarow lastline: end sub

this module creates sheet based on template:

sub create_name_sheet() sheets("template").copy before:=sheets(1) range("c5:g34").clearcontents range("c2:g2").clearcontents end sub

excel-vba