I am using the macro below. It is working great to add data and formulas to the new sheet, however I want to add another feature to this tlmsbiwk.xls file.

    Workbooks.OpenText Filename:="L:\tapeaudit\tlmsbiwk.txt", Origin:=437, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, _
        1)), TrailingMinusNumbers:=True
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],6)"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C2000"), Type:=xlFillDefault
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF(R1C1:R2000C1,RC[-1]),"""",""NOPE"")"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D2000"), Type:=xlFillDefault
    Range("F1").Select
    ActiveCell.Formula = "=IF(ISERROR(MATCH(A1,B:B,0)),A1,"""")"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F2000"), Type:=xlFillDefault
    ActiveWorkbook.SaveAs Filename:="L:\tapeaudit\tlmsbiwk.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
What I want to add is the code below. I just want it to be included in the new .xls file that is created. I now add it manually and it works fine, I would like to have it included in this step automatically. 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