Add vb code to a new workbook using a macro

    ChDir "L:\tapeaudit"
    Workbooks.OpenText Filename:="L:\tapeaudit\tlmsbiwk.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 2), _
        Array(3, 1)), TrailingMinusNumbers:=True
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R1C2:R2000C2,RC[-1]),"""",""NOPE"")"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D2000"), Type:=xlFillDefault
    Range("D1:D2000").Select
    ActiveWorkbook.SaveAs Filename:="L:\tapeaudit\tlmsbiwk.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End Sub

This is the code I am using now. I am creating a new workbook with the data from this text file and inputting the formulas that I need. The issue that I am having is that I want to be able to add the code below to the new sheet on it's creation and I have not been able to get it to work. Thanks for your help.

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
    Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
    ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        For Each Cell In Intersect(Target, Range("B:B"))
            If Range("D" & Cell.Row) = "NOPE" Then
                Call sndPlaySound32("C:\windows\media\notify.wav", 1)
                Exit Sub
            End If
        Next Cell
    End If

End Sub