Want Excel Macro to automatically run on newly added worksheets

Question

So I am learning VBA basics and have run into a problem. Basically I want to create a macro that formats and sums some weekly data. I want it to also automatically apply to each newly created worksheet within the workbook as the weeks go on and I add more data. Currently what I have will apply the formats to all worksheets within the workbook but when I add a new one I have to run the macro again. But when I do that it runs it over the previous worksheets as well creating multiple of the same headers and multiple sums of the same data. I'm not sure what I can do to have the macro automatically run whenever data is added to a new sheet or have it not run over sheets it has already applied changes to.

   Dim lastcell As String
   Dim x As Integer
   
   x = 1
   
   Do While x <= Worksheets.Count
       
       Worksheets(x).Select
       
       Range("F2").Select
       Selection.End(xlDown).Select
       lastcell = ActiveCell.Address(False, False)
       ActiveCell.Offset(1, 0).Select
       ActiveCell.Value = "=SUM(F2: " & lastcell & ")"
       
       x = x + 1
   Loop 
End Sub

Again I am very much a beginner here so any help is greatly appreciated!!!

Workbook-level events might be useful here, perhaps https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.sheetchange. — BigBen, Aug 18 '20 at 18:31
There is also a [New sheet](https://learn.microsoft.com/de-de/office/vba/api/excel.workbook.newsheet) event. — Storax, Aug 18 '20 at 18:47

score 0 · Answer 1 · answered Aug 18 '20 at 18:58

Try adding this into ThisWorkbook:

Option Explicit

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim lastrow As Long
Dim lastcell As String

With Sh
    lastrow = .Cells(.Rows.Count, 6).End(xlUp).Row
    lastcell = .Cells(lastrow, 6).Address
    .Cells(lastrow + 1, 6).Value = "=SUM(F2: " & lastcell & ")"
End With

End Sub

This is assuming you have other code to put before this that will add the data you need when a new sheet is added. It's also worth noting that Workbook_NewSheet will not be called if a sheet is copied to a new sheet.

I would also suggest avoiding using Select, as discussed here.

Want Excel Macro to automatically run on newly added worksheets

1 Answers1