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

Question

I would like to achieve the following

Use a template ("AmortTemplate"), to create a new worksheet in the same workbook
Once the new sheet is created ("AmortTemplate (2)"), populate specific fields in this worksheet from a table in an existing worksheet ("AssetInfo") in the same workbook
Rename the new worksheet ("AmortTemplate (2)") to the value of a specific field in the new worksheet, e.g. "ABC 123 GP", from cell G7 in the sheet
repeat the new sheet creation and field population using the reference table in "AssetInfo" worksheet until sheets are created and fields populated for all records (rows) in the reference table

I have managed to create simple VBA macros (using macro record function in Excel 2016) as shown below, but need to combine the actions and repeat as 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

Tony Dallimore Tony Dallimore · Accepted Answer · 2016-01-08T21:36:06

It is impossible to give a complete answer to your question but it is possible to help you on your way.

The Macro Recorder can be a help but it is limited. It records what you do as you do it. It produces syntactically correct VBA but not good VBA. For example, suppose I click a cell, enter a value and press Enter, it will record:

Range("A1").Select
ActiveCell.FormulaR1C1 = "5"
Range("A2").Select

No programmer would type anything like that. They would type something like one of these alternatives:

Range("A1").Value = "5"
Cells(1, "A").Value = "5"
Cells(1, 1).Value = "5"
Cells(RowCrnt, ColCrnt) .Value = "5"

In the first three of these statements, I have used different ways of specifying cell A1. In the fourth, I have used variables that I would have previously set to values necessary to specify the cell I wish to change.

I use the Macro Recorder for single, complex statements for which I am unsure of the syntax. For example, I find the Macro Recorder the best way of getting the code for a complex Sort or Find because I do not use these statements often enough to have memorised their syntax.

The Macro Recorder will not help you with If, Do or For statements. Those you have to code yourself.

You MUST learn at least the basics of VBA before attempting anything else. You cannot learn VBA from the Macro Recorder or from the snippets of code that you will find on this or other sites. Search for “Excel VBA tutorial”. There are many online tutorials to choose from so find one that matches your learning style. I prefer books. I visited a good library, reviewed their Excel VBA primers, borrowed the most promising for further study at home before buying the one I thought best as a permanent reference.

Your chosen tutorial or primer will teach you about variables, ifs, loops and accessing worksheets and workbooks. That could give you enough information to write your macro. If it does not, try searching for a single objective. For example, search for “Excel VBA copy worksheet”. That will take you to https://msdn.microsoft.com/en-us/library/office/ff837784.aspx which explains “Worksheet.Copy Method (Excel)”.

Don’t use relative cell addresses. There are occasions when relative address can be useful but I do not believe this is one of them. With relative addresses, you can very quickly loose track of where you are. This is particularly true if you want to put a loop round the code.

Tidying up you current code gives:

  Worksheets("Amort Template").Copy Before:=Worksheets(2)
  ' Sheets("Amort Template (2)").Select  ' Not necessary; the new worksheet is the active worksheet
  With 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 With

I may have misconverted some of the relative addresses but I do not think so.

Notes:

Range("G6").Value (no leading period) refers to the active worksheet.
.Cells(8, "C").Value (leading period) refers to the worksheet identified by the With statement.
Range("C8") has the same meaning as Cells(8, "C"). I have chosen the Cells format for a reason that will become clear.

You mention having a table in worksheets “AssetInfo”. I assume row 8 is the first row of the table. If I am correct, you will need a loop around this code:

For RowCrnt = 8 to RowLast
  :
Next

You will also need to replace .Cells(8, "C").Value by .Cells(RowCrnt, "C").Value. Repeat for all the other .Cells(8, "X").Values.

If you search for “Excel VBA find last row” you will find many sites that will tell you how to set the value for RowLast.

I hope this will get you started.

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

1 Answers