VB - Clear a named selection in a newly created sheet when it's created?

[andyreloaded]

I have a worksheet that gets copied frequently within the same workbook via the CTRL+CLICK and drag feature. In this worksheet I have the macro below to clear the contents of the range when the macro button is pressed. Is there any way to make this macro fire automatically only on the new sheet, and only when a new sheet is created?

Sub Clear_WSMESSAGEALL()
' Select named range WSMESSAGEALL and clear contents
Application.Goto Reference:="WSMESSAGEALL"
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.Goto Reference:="HOME"
End Sub

Thanks,
Andy

 

[Andrew Poulsom]

There is a Workbook_NewSheet event procedure.

 

[andyreloaded]

There is a Workbook_NewSheet event procedure.

I found some examples of that event, but I can't get them to work. This one should move the new sheet to the end of the book, which if I could get it to work I would try to modify it. Should code like this be put in This Workbook section of VB?

Sub ThisWorkbook_NewSheet(ByVal Sh As Object) _
Handles Me.NewSheet

Dim newSheet As Excel.Worksheet = CType(Sh, Excel.Worksheet)
newSheet.Move(After:=Me.Sheets(Me.Sheets.Count))
End Sub

 

[Andrew Poulsom]

That's not VBA. Try this in the ThisWorkbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Sh.Move After:=Me.Sheets(Me.Sheets.Count)
End Sub