Home/ Questions/Q 15229
Next
Answered
computing
  • 9
Asked: 2021-12-18T22:33:31+00:00 In:

Excel Formula To Create New Worksheet

  • 9

Please can someone write me a macro to create a new worksheet in Excel based on a trigger.

Basically I have a master worksheet which is the first tab in the series containing some data. Column F has a drop down list containing two options “Completed” and “Pending”. I want excel to create a new worksheet whenever a user selects “Completed” from the drop down list. The trigger will always be “Completed” and in case the selection is “Pending” then nothing should happen.

I don’t know if its possible or not but I would also like the new worksheet created to be renamed automatically to data input in other cells. E.g. – If I user “Completed” in Cell F1 then the new worksheet created should be renamed to the data in Cell A1, B1 and C1.

Values and result for e.g :

Cell A1 = Jan 2011
Cell B1 = BACS
Cell C1 = Pre Flight

Result required = A new worksheet named “Jan 2011-BACS-Pre Flight”

Share

    1 Answer

    1. Best Answer
      Right click the sheet tab for the “master” worksheet, choose View Code and paste this code into the pane that opens:

      Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 6 And Target = "Completed" Then
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
     Sheets(Sheets.Count).Name = _
        Range("A" & Target.Row) & "- " & _
        Range("B" & Target.Row) & "- " & _
        Range("C" & Target.Row)
   End If
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0