Excel Vba : Execute event or macro on creation of new sheet

Question

I am trying to create a drop down on a particular column on creation of a new sheet by the user.

The drop down is present as a template in a hidden sheet which needs to be populated on creation of a new sheet on a particular column.

1) I guess there should be some event which needs to be triggered on creation of new sheet and den call a macro

2) The macro will populate each cell in a particular column with the list of values

Can some one guide me how to do this....

Answer 1

In VBA, in the ThisWorkbook module, you can create an event handler for the New Sheet event:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    <copy stuff from ThisWorkbook.Worksheets("TemplateSheetName") to sh.cells(...)>
End Sub