Code in excel file to automatically run when manually copy pasting downloaded excel data

Hi All,
I am quite new to vba and have been trying to put together codes available online but unfortunately is not working due to my lack of background knowledge.

What I would like to do is whenever I download excel dataset with updated data (i.e. each month's data added) from the website & manually copy paste into my excel file (which I will be using for reporting purposes) into the worksheet named "All_Data", the code to check for 3 conditions (specialLookUp) should run automatically and the resulting subset must appear in Sheet2.

Initially I used just the Subroutine specialLookUp and it worked but when I copy pasted the downloaded data into "All_Data" Worksheet it stopped working, then I tried to add the Worksheet_change and other bits.

All of this code is in Module1.

Please help!

Here's the code:

Public PreVal As Variant

Private Sub Workbook_Open()
    PreVal = All_Data.Range("B1:P500").Value2
End Sub

Public Sub Worksheet_Change(ByVal Target As Range)
Dim woArea As Range, isect As Range
Set woArea = All_Data.Range("B1:P500")
Set isect = Application.Intersect(Target, woArea)

If isect Is Nothing Then
    specialLookUp
Else
    Worksheet_Calculate
End If
End Sub

Private Sub Worksheet_Calculate()
If All_Data.Range("B1:P500").Value2 <> PreVal Then
specialLookUp
PreVal = All_Data.Range("B1:P500").Value2
End If
End Sub

Public Sub specialLookUp()
    Dim keyword As String: keyword = "Client Email"
    Dim keyword2 As String: keyword2 = "Client Email Reply"
    Dim keyword3 As String: keyword3 = ""
    
    Dim countRows1 As Long, countRows2 As Long
    countRows1 = 2 'the first row of dataset in sheet1
    endRows1 = 500 'the last row of dataset in sheet1, make sure to increase this value based on dataset increase after download
    countRows2 = 2 'the first row where you want to start writing the found rows
    For j = countRows1 To endRows1
        If (Sheets("All_Data").Range("B" & j).Value = keyword Or Sheets("All_Data").Range("B" & j).Value = keyword2) And Sheets("All_Data").Range("I" & j).Value = keyword3 Then
                        Sheets("sheet2").Rows(countRows2).Value = Sheets("All_Data").Rows(j).Value
        countRows2 = countRows2 + 1
    End If
    Next j
End Sub

ExcelerNew,

Welcome to the forum. We would really like to help you but please take a moment to read our Forum Rules.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Once you complied to this we can help you with your project.

For starters both the event macro's ( Change & Calculate) should be in the SheetModule of sheet All_Data.