automatically copy and past VBA codes from one sheet to another-CodePudding

I have a VBA code in a sheet that is activated when a cell is changed. But this sheet is re-created by another macro, and when the sheet is re-created it does not have the VBA code inside the sheet.

Then I was looking for two solution (I don't know how to do it and did not find anything on the web, then I ask here):

a way to automatically copy and past the VBA code so the the new sheet created by the macro will have the VBA code
or call the VBA code that can be stored in a module (don't know even if it is possible, since is "Private Sub Worksheet_Change(ByVal Target As Range)")

Just to clarify better, below the VBA code that is inside the sheet that is re-created by a macro

Public Sub Worksheet_Change(ByVal Target As Range)
Dim sht As Worksheet
Dim LastRow As Long
Dim isect As Range
Dim firstCell As Range


modulo = ActiveSheet.Offset(-1, -3).Value
tipo = ActiveSheet.Offset(-1, -2).Value
nome = ActiveSheet.Offset(-1, -1).Value
descrizione = ActiveSheet.Offset(-1, 0).Value

Worksheets(modulo).Activate
Range(A1).Select
  

With ActiveSheet
.Range("A1:E10000").AutoFilter Field:=1, Criteria1:=modulo
.Range("A1:E10000").AutoFilter Field:=2, Criteria1:=tipo
.Range("A1:E10000").AutoFilter Field:=3, Criteria1:=nome


ActiveSheet.UsedRange.Offset(1, 3).SpecialCells(xlCellTypeVisible)(1).Value = descrizione

End With


UserForm3.Show


End Sub

Thanks!

CodePudding user response：

a) For an example on how to copy code see Copy VBA code from one Worksheet to another using VBA code

b) You can copy code, but you need to set "Trust Access To Visual Basics Project". How to do so, see https://stackoverflow.com/a/11680865/7599798. Be aware that this is an individual setting (per user) and therefore you cannot be sure that this works for all user.

c) Your attempt to put the code into a module will not work. Event routines need to be in the Workbook/Worksheet where the event happens. If the new sheet is copied into a Workbook where you have already code, you could use the Workbook_SheetChange event which is triggered at any change on any sheet within the workbook.

d) If the (re)creation of the sheet is done by a macro that you control: Instead of adding a new sheet, copy an existing template sheet that already contains code.