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?

 

[Tim_Excel_]

Think about rewriting the entire code. You are selecting and activating ranges which should be avoided and in your code seems to be without any use.

Give this page a read, it's very informative!

For example

'This code below...
Range("A2:A100").Select
Selection.Value = 3


'...should instead be like
Range("A2:A100").Value = 3

 

[stanco]

Think about rewriting the entire code. You are selecting and activating ranges which should be avoided and in your code seems to be without any use.

Give this page a read, it's very informative!

For example

'This code below...
Range("A2:A100").Select
Selection.Value = 3


'...should instead be like
Range("A2:A100").Value = 3

the code was actually written by a freelancer on a duplicate file with dummy data. i am trying to insert the codes into my actual file. so rewriting is not really an option.

so from the link you shared, i understand that .select is mainly due to marco recorder, and many other articles/ page mentioned that the sheet is not active and this is not a good way to code. but this is way too advance for me. is there any quick and easy fix for me?

 

[Tim_Excel_]

I should have mentioned it's only module 2 that needs rewriting. Shivers down my spine and that. The code in module 1 will never be executed as it is all commented out.

What is the error you are getting? I have the suspicion it's a Subscript out of Range error, which would mean you are referring to an object Excel cannot find.

 

[stanco]

I should have mentioned it's only module 2 that needs rewriting. Shivers down my spine and that. The code in module 1 will never be executed as it is all commented out.

What is the error you are getting? I have the suspicion it's a Subscript out of Range error, which would mean you are referring to an object Excel cannot find.

do you think u can help me to amend module 2 code?

the error i am getting is "Run-time error 1004. Select method of Range class failed"

 

[Tim_Excel_]

How about you just delete the whole line selected in yellow, it does nothing.

 

[stanco]

How about you just delete the whole line selected in yellow, it does nothing.

then i will get the next line highlighted in yellow. if i ignore that error, i get another error "Run-time error 9. Subscript out of range".

If i delete the next line as well, then i get more and more lines highlighted. lol. i dont know what i am doing.

 

[Tim_Excel_]

With

Workbooks("Book1").Activate
'or
Windows("Book1.xlsx").Activate

or anything of such sort, you are referring to a very specific object, in this example a workbook named Book1. If no such object exists, VBA will return an error. Since your code is not dynamic at all, it will keep running into these errors until all references are correct.

Best to do is to figure out what you want to achieve and go from that, and then ask us to help you with that. I am unable to rewrite the module as it's a mess specifically designed for a very rigid workbook.

 

[stanco]

With

Workbooks("Book1").Activate
'or
Windows("Book1.xlsx").Activate

or anything of such sort, you are referring to a very specific object, in this example a workbook named Book1. If no such object exists, VBA will return an error. Since your code is not dynamic at all, it will keep running into these errors until all references are correct.

Best to do is to figure out what you want to achieve and go from that, and then ask us to help you with that. I am unable to rewrite the module as it's a mess specifically designed for a very rigid workbook.

i have a table with all the companies details, including the different dates that the companies do their surveys. the surveys are done on ad hoc basis, so a company may be doing the X time survey, while some are on their first.

the vba codes above are supposed to extract all the companies who have done at least two surveys on a specific dates. if the company has done survey 2 and 3 within the specified date, then it should appear in the same row.

this is the file that the freelancer worked on with dummy data. i believe it's easier for you to understand once you see the file.

 

[Tim_Excel_]

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!