|By clearing the log, I assume you mean deleting the Row that contains the sheet name of the deleted sheet. If you mean to simply delete the data in the Row (all except the sheet name) the same concept would be used but the code would need to be modified, slightly.|
I cannot take full credit for the following solution. I merely modified the concept that can be found here, as well as many other places on the interweb.
Since there is no event handler for the deletion of a sheet, we have to use a different event to trigger the code to clear the log, unless you want to run a macro manually. In this case, I'm using the SheetDeactivate event. Deleting a sheet essentially deactivates it, so we can capture that event and run some code.
Open the VBA editor and double click the ThisWorkbook module. Paste this code into the pane that opens:
Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
Application.OnTime Now + TimeSerial(0, 0, 1), "DeletedSheet"
Now paste this code into a Standard module:
Dim oWS As Object
'Allow code to continue when the Set instruction throws
'up an error because the sheet doesn't exist
On Error Resume Next
'Determine last row with Sheet Names in Transmittal Log Column A
lastRw = Sheets("Transmittal Log").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet Names in reverse order so Rows can be deleted
For shtNum = lastRw To 2 Step -1
'Fill variable with Sheet Name
nxtSht = Sheets("Transmittal Log").Range("A" & shtNum)
'Try to access Sheet
Set oWS = Sheets(nxtSht)
'If Object cannot be set then Sheet doesn't exist, so delete Row
If oWS Is Nothing Then
Sheets("Transmittal Log").Range("A" & shtNum).EntireRow.Delete
'Clear Object if Sheet existed
Set oWS = Nothing
What will happen is this:
Whenever a sheet is Deactiavted, the event will be captured and the Workbook_SheetDeactivate code will call the SheetDeleted code. The SheetDeleted code will then loop through the Sheets Names in Transmittal Log Column A and try to set an Object variable to each sheet. If it cannot set the variable to a given sheet because the sheet no longer exists, it will delete that row from the Transmittal Log sheet.
You should be aware that this code will run every time any sheet is deactivated for any reason. In other words, it will run every time a sheet tab is clicked to switch sheets. Whether or not this will have a performance impact on the operation of your workbook will be based on the speed of your machine and the number of worksheet in the workbook.
You may have noticed the irony of the fact that we eliminated the need to loop through every sheet in the workbook in order to update the log, only to replace that with the need to loop through every sheet in the workbook to test for deletions. :-)
There are other options:
Instead of using the SheetDeactivate event, you could use the BeforeClose and/or BeforeSave event to clear the log just before the workbook is Closed or Saved. That would also be automatic but it probably wouldn't run as often. As I mentioned earlier, you could also just manually run the SheetDeleted macro to clear the log whenever you felt the need.
Let me know if this makes sense.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.