excel vba transfer of code from module to sheet newly created work sheet

Question

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$10" Then
        Call mymacro
    End If
End Sub

the question is:

1. i can store this code to a module?
2. if not, how can the code be the code transfer to the newly created worksheet
3. from module how can i transfer this code every time a sheet is added Thanks in advance

Answer 1

You could use the Workbook_SheetChange Event. Put the code in the workbook module. Then there is no need to copy any code it all.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Target.Address = "$D$10" Then
       Call mymacro
   End If
End Sub

EDIT If you need to prevent the code from running on certain sheets you could add the follwoing function

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    On Error Resume Next ' Invalid Parameters passed, IsInArray will be defaulted to FALSE
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

and change the Workbook_SheetChange Event to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim shArr() As Variant

    'Example, mymacro will not run on the sheets "Overview" and "Total"
    shArr = Array("Overview", "Total")

    If Not IsInArray(Sh.Name, shArr) Then
        If Target.Address = "$D$10" Then
            Call mymacro
        End If
    End If

End Sub

Answer 2

An example adaption of @davesexcel as i have never tried this way before. This code goes in a standard module which you could call from your main sub. Requires a reference to be added to VBA Extensibility and access to vb model to be trusted.

Public Sub AddWorksheetEventCode()
'Tools > references > Microsoft Visual Basic for Applications Extensibility 5.3 
'Trust access to VBA model

    Dim wb As Workbook
    Dim wsNew As Worksheet

    Set wb = ThisWorkbook

    Dim xPro As VBIDE.VBProject
    Dim xCom As VBIDE.VBComponent
    Dim xMod As VBIDE.CodeModule
    Dim xLine As Long

    wb.Worksheets.Add After:= wb.Worksheets(ActiveSheet.Index)
    Set wsNew = ActiveSheet

    With wsNew
        Set xPro = wb.VBProject
        Set xCom = xPro.VBComponents(wsNew.Name)
        Set xMod = xCom.CodeModule

        With xMod

            xLine = .CreateEventProc("Change", "Worksheet")
            xLine = xLine + 1
            .InsertLines xLine, "If Target.Address = ""$D$10"" Then "
            xLine = xLine + 1
            .InsertLines xLine, "Call mymacro"

        End With

    End With

End Sub

Enable Trust access to the VBA project object model:

1. Click File and then Options.
2. In the navigation pane, select Trust Center.
3. Click Trust Center Settings....
4. In the navigation pane, select Macro Settings.
5. Ensure that Trust access to the VBA project object model is checked.
6. Click OK.

**Read up on trusting access to the vba project model to determine if appropriate for you.

Answer 3

do like this

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$10" Then

    Call mymacro
    Me.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Cells.Clear
End If
End Sub

Answer 4

I have up-voted @Storax answer because I didn't know that. However, you should bear in mind that the Workbook_SheetChange event will fire for all sheets, possibly including some where you don't want your macro to run. Therefore you would need to add code to prevent the macro from taking action when the event is triggered where you don't want it.

As an alternative, look at the way you create your sheets. If you insert a new sheet the new addition will be totally blank, but if you use "Move or Copy / Create a copy" (or its VBA equivalent) you get a new sheet which is the copy of the original including its code. A further advantage is that you get a fully formatted sheet, and it is normally quite easy to clean out any data that were also copied in this process.