Excel formula to create new worksheet

August 11, 2011 at 07:19:10
Specs: Windows 7
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"

See More: Excel formula to create new worksheet

Report •

August 17, 2011 at 07:26:07
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.

Report •
Related Solutions

Ask Question