VBA Excel How to populate formula in cells after creation of new sheet

Question

Ok so I have a macro that creates a new set of sheets off of a template sheet within the workbook. I have an existing sheet that needs to pull data off of the new sheets. How can I add to this macro so that it auto fills the statistics sheet with the proper references on the newly created sheets (they are named by user input so they could be anything). The existing macro I have is:

Sub CreateSheetsFromAList()

    Dim MyCell As Range, MyRange As Range

    Set MyRange = Sheets("Instructions").Range("M5")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange

        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        Worksheets("Template").Cells.Copy ActiveSheet.Range("A1") 'copies data from "Template"

    Next MyCell

    MsgBox ("Done!")

End Sub

I would like the statistics page refresh to happen after all the new sheets are created.

Thanks for your help!!

Liza Liza · Accepted Answer · 2014-07-30T19:32:34

Within your For loop, you could set the references using "MyCell.Value" as the sheet name:

For Each MyCell In MyRange
    Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Worksheets("Template").Cells.Copy ActiveSheet.Range("A1") 'copies data from "Template"
    Sheets("Summary").Range("A" & (MyCell.Row)).Formula = "=" & MyCell.Value & "!A1"
Next MyCell

This will put references to A1 of each sheet in the sheet Summary. I just used the existing MyCell.Row so that these wouldn't all overwrite each other, but, depending on where you need to formulas to go, you could add a new variable for this.

VBA Excel How to populate formula in cells after creation of new sheet

1 Answers