excel vba - Duplicate a template worksheet and populate from data table in another sheet -

i achieve following

1. use template ("amorttemplate"), create new worksheet in same workbook

2. once new sheet created ("amorttemplate (2)"), populate specific fields in worksheet table in existing worksheet ("assetinfo") in same workbook

3. rename new worksheet ("amorttemplate (2)") value of specific field in new worksheet, e.g. "abc 123 gp", cell g7 in sheet

4. repeat new sheet creation , field population using reference table in "assetinfo" worksheet until sheets created , fields populated records (rows) in reference table

i have managed create simple vba macros (using macro record function in excel 2016) shown below, need combine actions , repeat described above.

sub copy_amort_template() ' ' copy_amort_template macro '     sheets("amorttemplate").select     sheets("amorttemplate").copy before:=sheets(2) end sub    sub insert_asset_info() ' ' insert_asset_info macro '     sheets("amort template (2)").select     range("g6").select     activecell.formular1c1 = "=assetinfo!r[2]c[-4]"     range("g7").select     activecell.formular1c1 = "=assetinfo!r[1]c[-5]"     range("g8").select     activecell.formular1c1 = "=assetinfo!rc"     range("g9").select     activecell.formular1c1 = "=assetinfo!r[-1]c[-3]"     range("g10").select     activecell.formular1c1 = "=assetinfo!r[-2]c[-1]"     range("g11").select     activecell.formular1c1 = "=assetinfo!r[-3]c[1]"     range("g14").select     activecell.formular1c1 = "=assetinfo!r[-6]c[-2]"     range("e15").select     activecell.formular1c1 = "=assetinfo!r[-7]c[8]"     range("g15").select     activecell.formular1c1 = "=assetinfo!r[-7]c[5]"     range("g7").select     selection.copy     sheets("amort template (2)").select     sheets("amort template (2)").name = "abc 123 gp" end sub 

it impossible give complete answer question possible on way.

the macro recorder can limited. records do it. produces syntactically correct vba not vba. example, suppose click cell, enter value , press enter, record:

range("a1").select activecell.formular1c1 = "5" range("a2").select 

no programmer type that. type 1 of these alternatives:

range("a1").value = "5" cells(1, "a").value = "5" cells(1, 1).value = "5" cells(rowcrnt, colcrnt) .value = "5" 

in first 3 of these statements, have used different ways of specifying cell a1. in fourth, have used variables have set values necessary specify cell wish change.

i use macro recorder single, complex statements unsure of syntax. example, find macro recorder best way of getting code complex sort or find because not use these statements enough have memorised syntax.

the macro recorder not if, do or for statements. have code yourself.

you must learn @ least basics of vba before attempting else. cannot learn vba macro recorder or snippets of code find on or other sites. search “excel vba tutorial”. there many online tutorials choose find 1 matches learning style. prefer books. visited library, reviewed excel vba primers, borrowed promising further study @ home before buying 1 thought best permanent reference.

your chosen tutorial or primer teach variables, ifs, loops , accessing worksheets , workbooks. give enough information write macro. if not, try searching single objective. example, search “excel vba copy worksheet”. take https://msdn.microsoft.com/en-us/library/office/ff837784.aspx explains “worksheet.copy method (excel)”.

don’t use relative cell addresses. there occasions when relative address can useful not believe 1 of them. relative addresses, can loose track of are. particularly true if want put loop round code.

tidying current code gives:

  worksheets("amort template").copy before:=worksheets(2)   ' sheets("amort template (2)").select  ' not necessary; new worksheet active worksheet   worksheets("assetinfo")     range("g6").value = .cells(8, "c").value     range("g7").value = .cells(8, "b").value     range("g8").value = .cells(8, "g").value     range("g9").value = .cells(8, "d").value     range("g10").value = .cells(8, "f").value     range("g11").value = .cells(8, "h").value     range("g14").value = .cells(8, "e").value     range("e15").value = .cells(8, "m").value     range("g15").value = .cells(8, "l").value     activesheet.name = .cells(8, "b").value   end 

i may have misconverted of relative addresses not think so.

notes:

1. range("g6").value (no leading period) refers active worksheet.
2. .cells(8, "c").value (leading period) refers worksheet identified with statement.
3. range("c8") has same meaning cells(8, "c"). have chosen cells format reason become clear.

you mention having table in worksheets “assetinfo”. assume row 8 first row of table. if correct, need loop around code:

for rowcrnt = 8 rowlast   : next 

you need replace .cells(8, "c").value .cells(rowcrnt, "c").value. repeat other .cells(8, "x").values.

if search “excel vba find last row” find many sites tell how set value rowlast.

i hope started.