Macro to insert formula into an existing worksheet when new worksheet added?

[jtrib84]

does anyone know if i can run a macro that will insert a formula into an existing worksheet that pulls information from a newly added worksheet? the existing worksheet will be a summary of all the newly created worksheet. all of the new worksheets will be the same template.

thanks!

 

[Yard]

Something like this in the ThisWorkbook module of your workbook (in the VB Editor).

(This is an example since you don't say what you want your formula to do).

Press Alt+F11 to go the VBEditor.

In the Project Explorer window, navigate to the ThisWorkbook module of your workbook. Double click that and paste this into the code window:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim strSum As String
strSum = "=SUM(" & Sh.Name & "!R1C1:R10C1)"
Worksheets("Master Sheet").Range("A1").FormulaR1C1 = strSum
End Sub

Hope that gets you started.