Run Macro "If" the number of sheets in a document change.

[mikedevt]

How do I code a macro to run "If" a new sheet is added? My code works with a button, I am trying to make it automated when a sheet is added or deleted.

I appreciate any help on this topic. Thanks in advance.

 

[Roderick_E]

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'your macro here
End Sub

and....

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
call yourmacro
End Sub

 

[mikedevt]

That did not work for me. I have a button that adds new sheets and a separate code to sort a table of contents.

My macro for the table of contents works when it adds a new sheet, I need to get it to run when a sheets gets deleted as well.

Thanks

 

[Roderick_E]

sorry please see my edit above for deletion process.

 

[mikedevt]

this is what I have right now and it is not working, it needs to go to Table of Contents before it runs the macro:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
Sheets("Table of Contents").Select
Call UpdateTOC
End Sub

This code works (button click) when a sheet is added:

Private Sub AddNewJob_Click()

CopyRename

Sheets("Table of Contents").Select

UpdateTOC


End Sub

 

[Roderick_E]

Sheets("Table of Contents").activate not select