Here is a possible solution, but I doubt anyone will
like it because it involves an infinite loop.
Add a VBA module to your app with the following code:
Option Explicit
Public SheetCount As Integer
Public CopiedSheet As Boolean
Sub Auto_Open()
SheetCount% = Sheets.Count
CopiedSheet = False
Do While True
If Sheets.Count > SheetCount% Then
If FindLastCell <> "ERROR" Then
CopiedSheet = True
MsgBox "Sheet copied from " & _
"other workbook"
End If
End If
SheetCount% = Sheets.Count
DoEvents
Loop
End Sub
Public Function FindLastCell() As String
'Returns address of last cell used
'(highest row & col) on active sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With ActiveSheet
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, _
LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function
Public Function FindLastCell() As String
'Returns address of last cell used (highest row & col)
on active sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With ActiveSheet
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&,
LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function
In my tests, it always detected when a sheet with data
was copied in from another workbook. It does not react
to inserting a new blank worksheet or copying in a
blank worksheet from another workbook. Hopefully, it
may be a starting point for you.