Hello you wonderful people.

I am trying to create a module that will add a worksheet change event in another workbook and it is driving me completely insane and I am unsure what I am missing as it works one moment and then doesn't at all.

Code is below.

Sub Clean_Export()
    Dim wb, WB2 As Workbook
    Dim UB As Worksheet
    
    Set wb = ThisWorkbook
    Set UB = wb.Worksheets("UB")


    Workbooks.Add
    
    Set WB2 = ActiveWorkbook
    
    UB.Cells(1, 1).CurrentRegion.Copy
    WB2.Sheets(1).Paste
        
    With WB2.VBProject.VBComponents("Sheet1").CodeModule
    .DeleteLines StartLine:=1, Count:=.CountOfLines
    .AddFromFile "\\myfiles\Sheet1.cls"
    End With

    WB2.SaveAs Filename:="\\somewherespecial\RBC Unbilled Report\Unbilled Report - " & Format(Date, "dd mmm yyyy") & " - " & _
        Format(Time, "HH.MM") & ".xlsb", FileFormat:=xlExcel12
    
    WB2.Close True
    
    End Sub

When I F8 through the code I can see the event in the sheet1 object but when it is closed and I re-open it is not there.

What am I missing?