VBA Code to Create A New Sheet from a Template, input some values from a Master sheet, and return results from Template back into Master

[Hachimiya]

Hi, I'm new to VBA and need to use it for something similar to what was posted in this thread ( https://www.mrexcel.com/forum/excel...de-create-new-sheet-template-rename-list.html ), but with a few differences.

I have a workbook that tracks the names of various assets & certain numerical metrics in the same row (eg. Price, Opex, etc.) in a sheet called "Master". The names start in column B, row 3, and the various metrics run all the way to Column L.

I also have a sheet named 'Template' that has various formulas, and takes the numerical metrics from columns C-L as inputs to spit out financial indicators (IRR, NPV, etc.) on these assets. The section to input these metrics on the Template sheet is a row (E3:O3) with the same format/ordering of metrics as the Master sheet.

I want to write a macro that copies the 'Template' sheet, renames it to the asset name, and populates the inputs section with the appropriate metrics. After that, I want to pull the financial ratios that are calculated (these will appear in cells F7:I7), and paste them into the Master document on the corresponding row, starting from column M.

This is what I want the master sheet to look like

------------inputs for template---------- | ----outputs from template--------

B | C | D |.... | L | M | N | ...
Name | Price | Age | ....| Interest rate | NPV | IRR | ...


Could you help me out please? Thanks so much

 

[DanteAmor]

Try this

Sub CreateSheetFromTemplate()
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Dim u1 As Double
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set sh1 = Sheets("Master")
    Set sh2 = Sheets("Template")
    u1 = sh1.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 3 To u1
        asset = sh1.Cells(i, "B").Value
        On Error Resume Next
        Sheets(asset).Delete
        On Error GoTo 0
        sh2.Copy after:=Sheets(Sheets.Count)
        Set sh3 = ActiveSheet
        sh3.Name = asset
        sh3.Range("E3:N3").Value = sh1.Range("C" & i & ":L" & i).Value
        sh1.Range("M" & i & ":P" & i).Value = sh3.Range("F7:I7").Value
    Next
    
    sh1.Select
    Application.ScreenUpdating = True
    MsgBox "Finish"
End Sub