How to capture worksheet being added through Copy/Paste in Excel VBA

Question

I am trying to capture worksheets being copied in to a workbook from another workbook.
Workbook_NewSheet event does not trigger when the sheets are copied from another workbook. It is triggered only if the user manually inserts them through (Insert->Worksheet menu option), or when you add a new sheet through VBA as ThisWorkbook.Worksheets.Add.

What I am trying to capture is basically a Paste operation which is resulting in a new sheet.

This might be from any of the below user actions:

1. User copies an existing sheet by dragging it holding Control Key (which adds a new sheet)
2. User copies sheet/s from another workbook
3. user moved sheets from another workbook

or any of the below VBA code:

SourceWorkbook.Sheets(“SourceSheet”).Copy Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'copy across workbook'  
SourceWorkbook.Sheets(“SourceSheet”).Move Before:=TargetWorkbook.worksheets(“SheetNameIn Target”) 'move across workbook'  
ThisWorkbook. Sheets(“SheetName”).Copy 'copy within workbook'  

If you know any way of capturing this action/macro results within VBA that would be greatly helpful.

Please note that I do not want to avoid such an user action (so i do not want to secure the workbook) but I want to handle the pasted sheet programatically to verify the data, and if the similar sheet already exists then update the existing sheet rather than having same data in two sheets.

Answer 1

When a sheet is copied, its name will always end with "(2)", or at least ")". You could check on that like this

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name Like "*(2)" Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
    End If
End Sub

Answer 2

The SheetActivate event will fire under all of those circumstances. Obviously it will fire under a lot of other circumstances too. This sounds like a royal pain, but you could maintain your own collection of worksheets and compare your collection to the ThisWorkbook.Sheets collection to see if something was added/deleted.

If you're trying to prevent it, you might consider protecting the workbook structure instead of doing it in code.