Populate New Sheet From Drop-down Menu Of Template Sheets

[SavantE]

Hello all!

I am hoping for a solution to this seemingly simple dilemma.

I have a workbook with 8 differently named sheets that all act as templates for different project types.

I am hoping to enable a macro/vba so that any new sheet created is blank with a drop-down list that has all 8 template sheets names in it. Upon selecting one, I am hoping that the sheet will clear and populate as a copy of the template sheet.

Example. There is a template sheet for "Budget Analysis". I hit the + button to create a new sheet. The sheet has a drop-down list. I select "Budget Analysis" from the list. The drop-down list disappears and the sheet copies completely the template sheet for "Budget Analysis". Then I am free to fill it out.

Is this possible/simple?

 

[richh]

I would recommend creating the form templates and having a "generator" worksheet. on the sheet, have a command button that pops up a combo box selector. Upon selecting a template, it gets copied. Otherwise, you're looking at a bunch of INDIRECT formulas.

Here's the Code

Private Sub ToggleButton1_Click()
    If Me.ComboBox1.Value = "" Then Exit Sub
    Sheets(Me.ComboBox1.Value).Copy After:=Sheets(1)
End Sub

 

[SavantE]

I'm trying to make this work as the concept seems perfectly reasonable. Do you have a full VBA write-up to get the button to pop up the combo box? Sorry if that seems like a stupid question.

Having trouble getting this to work. And is it a toggle button or command button?

 

[richh]

Command button I think got renamed to Toggle Button in 365; not sure though. You can find it on the Developer Ribbon.

The other bit of code is rather small. First, create the button on the generator sheet. Assign it a new macro. The macro contains a single line - UserForm1.Show

The ComboBox on the form references a named range. I just put all the sheet names in a list and named it something like "pageNames". In the ComboBox's properties, the RowSource value is that named range.