Code doesnt work after being imported into a new excel

[stanco]

This are the original codes.

Module 1

'Sub Test()''
'' Test Macro
''
'' Keyboard Shortcut: Ctrl+f
''
'    Sheets("Engagement Log").Range("Table1[#All]").AdvancedFilter Action:= _
'        xlFilterCopy, CriteriaRange:=Range("Sheet1!Criteria"), CopyToRange:=Range( _
'        "A6:H7"), Unique:=False
'End Sub

Module 2

Sub Macro2()'
' Macro2 Macro
'


'
    Range("Table1[[#Headers],[SURVEY 1 DATE]]").Select
    Windows("Book1.xlsx").Activate
    Application.WindowState = xlNormal
    Application.WindowState = xlNormal
    Windows("20190322.xlsm").Activate
    Columns("B:B").Select
    Selection.Copy
    Range("Table1[[#Headers],[COMPANY NAME]]").Select
    Sheets.Add After:=ActiveSheet
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").EntireColumn.AutoFit
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$2:$A$1602").RemoveDuplicates Columns:=1, Header:=xlNo
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Engagement Log").Select
    Range("AE2").Select
    Sheets("Sheet1").Select
End Sub

Module 3

Sub dateCheck()    Dim sht, sht2 As Worksheet
    Dim xStartDate As Date
    Dim xEndDate As Date
    Dim xDate As Date
    
    Set sht = ThisWorkbook.Worksheets("Engagement Log")
    Set sht2 = ThisWorkbook.Worksheets("Result")
    
    
    a = sht.Cells(Rows.Count, 2).End(xlUp).Row
    b = sht.Cells(1, Columns.Count).End(xlToLeft).Column
    xcol = Replace(ActiveSheet.Cells(1, b).Address(True, False), "$1", "")
    Rng = sht.Range("A1:" & xcol & 1)
     
    
    
    a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
    If a2 > 5 Then sht2.Range("A6:A" & a2).EntireRow.Delete
    a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
    j = a2
    b2 = sht2.Cells(5, Columns.Count).End(xlToLeft).Column
    xcol2 = Replace(ActiveSheet.Cells(1, b2).Address(True, False), "$1", "")
    Rng2 = sht2.Range("A5:" & xcol2 & 5)
    
    
    
    xSurveyCount = sht2.Range("H1").Value
    xStartDate = sht2.Range("B1").Value
    xEndDate = sht2.Range("B2").Value
    
    Set RowRange = sht.Range("B2:B" & a)
    
    For Each rowvalue In RowRange
        xrow = rowvalue.Row
        
        xCert = sht.Cells(xrow, 1).Value
        xUEN = sht.Cells(xrow, 2).Value
        xCName = sht.Cells(xrow, 3).Value
'        xSProject = sht.Cells(xrow, 4).Value
'        xSector = sht.Cells(xrow, 8).Value
        Z = 0
        For i = 2 To xSurveyCount
            d = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng, 0)
            xDate = sht.Cells(xrow, d).Value
            d2 = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng2, 0)
            If xDate >= xStartDate And xDate <= xEndDate Then
'                d2 = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng2, 0)
                If xCert <> sht2.Cells(j, 1).Value And xUEN <> xUEN2 And xCName <> sht.Cells(j, 3).Value Then
                  z2 = d2
                  Z = Z + 1
                  j = j + 1
                  sht2.Cells(j, 1).Value = sht.Cells(xrow, 1).Value
                  sht2.Cells(j, 2).Value = sht.Cells(xrow, 3).Value
                  sht2.Cells(j, 3).Value = sht.Cells(xrow, 4).Value
                  sht2.Cells(j, 4).Value = sht.Cells(xrow, 8).Value
                  
                  sht2.Cells(j, d2).Value = sht.Cells(xrow, d).Value
                Else
                 z2 = d2
                 Z = Z + 1
                 sht2.Cells(j, d2).Value = sht.Cells(xrow, d).Value
                End If
            End If
        Next
'        If Z >= 2 Then xZdate = sht2.Cells(j, z2).Value
'        If Z >= 2 Then xZdate1 = sht2.Cells(j, z2 - 1).Value
'        If Z >= 2 And xZdate > xZdate1 Then sht2.Cells(j, d2 + 1).Value = sht2.Cells(j, z2).Value - sht2.Cells(j, z2 - 1).Value
        If Z >= 1 Then sht2.Cells(j, d2 + 1).Value = sht2.Cells(j, z2).Value


        xUEN2 = xUEN
    Next
    MsgBox "Task Completed"
End Sub


Sub ClearResult()
    Dim sht2
    Set sht2 = ThisWorkbook.Worksheets("Result")
   
    
    a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
    b2 = sht2.Cells(5, Columns.Count).End(xlToLeft).Column
    'Set RowRange = sht2.Range("A6:A" & a2)
    If a2 > 5 Then sht2.Range("A6:A" & a2).EntireRow.Delete
    
End Sub

i copied and pasted the exact same thing into a new excel file (only the excel file name is different, the rest of the tabs name are the same). but it doesnt work and i am brought into the debugger mode and shown this.

how do i make the codes work in the new excel file too?

 

[stanco]

I don't have too much time on my hands right now so unfortunately I can't help you further.
I can tell that module 2 copies a bunch of data and creates/deletes sheets. Perhaps it's an idea if you start another thread with what you want to achieve with module 2 so someone else can help you out. Good luck!

all right. thanks for all your suggestions and time.