Automate sheet creation

[sbrien]

Hello,

I'm trying to automate the process of creating new sheets every time we have a new project.

What I'm envisioning is a macro button that will pop up a box that, when filled, will be the name for the new sheet. The sheet would include a standard list of project phases, steps, and statuses, which are then referenced on a row of a summary sheet.

https://app.box.com/s/875o97i2f4zxn3tzr8288to3fdd007vo

That link includes a sample of what I'm looking at. I'd like the automation to also create a new row at the bottom of the Status Summary sheet that corresponds to the newly created sheet and references the individual status cells.

Any ideas on how to approach this? I'm very green when it comes to VBA, but my boss dropped this on me.

Thanks,
sbrien

 

[MARK858]

The macro below will get you started (please note I have changed the worksheet name to myProject because Project is a word used by Excel and so will cause issues).

There is some repetitive filling for you to do.

And this part

references the individual status cells.

is better done with formula which someone else will probably help you with (although it probably will be better to insert the formula with the macro)

Sub Macro14()
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "myProject " & Sheets.Count - 1
    With ActiveSheet
        .Range("A1:D1") = Array("Phase", "Step", "Status", "Comments and Detail")
        .Range("A2:A4").Value = "ID Opp"
        .Range("A5:A10").Value = "Analytics"
        .Range("a11:a17").Value = "Solution"

        .Range("B1").Value = "Step"
        .Range("B2").Value = "Identify and ideate people business issue"
        .Range("B3").Value = "Propose + visualize analytics solution"
        .Range("B4").Value = "Commit to work - leader contracting and approval"
        .Range("B5").Value = "Identify and ideate people business issue"
        ' ADD IN THE REST OF YOUR RANGES

    End With
    Sheets("Status Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name

End Sub

 

[sbrien]

Thanks! That worked great. I threw in a couple tweaks, some file references, and attached it all to a control button and it works just like I'd hoped.

The macro below will get you started (please note I have changed the worksheet name to myProject because Project is a word used by Excel and so will cause issues).

There is some repetitive filling for you to do.

And this part is better done with formula which someone else will probably help you with (although it probably will be better to insert the formula with the macro)

Sub Macro14()
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "myProject " & Sheets.Count - 1
    With ActiveSheet
        .Range("A1:D1") = Array("Phase", "Step", "Status", "Comments and Detail")
        .Range("A2:A4").Value = "ID Opp"
        .Range("A5:A10").Value = "Analytics"
        .Range("a11:a17").Value = "Solution"

        .Range("B1").Value = "Step"
        .Range("B2").Value = "Identify and ideate people business issue"
        .Range("B3").Value = "Propose + visualize analytics solution"
        .Range("B4").Value = "Commit to work - leader contracting and approval"
        .Range("B5").Value = "Identify and ideate people business issue"
        ' ADD IN THE REST OF YOUR RANGES

    End With
    Sheets("Status Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name

End Sub