Did I made my requirement clear?

Indeed you did say you only want code in one module, but you also said you
want to allow for the 'Common' sheet to be copied to other wb's and its
event code to be copied too. On the basis of the limited information you
provided, at times contradictory, I would have thought my suggestion was a
workable compromise.

[all code..] in one workbook module other than ThisWorkbook module.

The only way to do that, trap sheet events in a single module but not
ThisWorkbook module, would be in a 'WithEvents' class module. This could be
set up to trap workbook events (incl sheet activate etc), Worksheet events,
or application events.

Normally the only reason to make your own 'WithEvents' class module for
sheet or wb events would be to trap events in (an)other workbook(s).

It's difficult to know what you want to do within the constraints of VBA,
would one module to trap sheet events in ALL open workbooks suffice, eg

' in a class module named clsAppEvents
Public WithEvents xl As Excel.Application

Private Sub xl_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name, , Sh.Parent.Name
End Sub

' in normal module

Dim mcApp As clsAppEvents

Sub SetAppEvents()
Set mcApp = New clsAppEvents
Set mcApp.xl = Application
End Sub

Run SetAppEvents then activate sheets in all open workbooks.

Regards,
Peter T


"Dileep Chandran" wrote in message
oups.com...
Peter,

What I excatly need is to put the entire code (for all the 40 sheets)
in one workbook module other than ThisWorkbook module.

Did I made my requirement clear?

-DC