VBA enter text in cell if value exists on another sheet

[js_odom]

I am using this code to find if the value in J1 of sheet1 exists in column B of sheet2, Then copy and paste the rows starting in the first empty row of sheet1. I'm sure it could be much cleaner, but it works well.

Private Sub Worksheet_Calculate()
Static oldval
If Range("J1").Value <> oldval Then
    oldval = Range("J1").Value

a = Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To a

    If Worksheets("Sheet2").Cells(i, 2) = Worksheets("Sheet1").Cells(1, 10) Then
        
        Worksheets("dbo.JobItems").Rows(i).Copy
        Worksheets("Sheet1").Activate
        b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet1").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Sheet2").Activate

End If

Next
Application.CutCopyMode = False

Worksheets("Sheet1").Activate
ThisWorkbook.Worksheets("Sheet1").Cells(2, 1).Select
End If

End Sub

What I want to do now is enter the text "Job Section" in the first empty row if the value of J1 on Sheet1 exists in Sheet2 then paste all rows that match below starting in the next empty row.
So, if sheet2 has:

A______________ B
Yellow__________Banana
Red____________ Apple
Purple__________Grape
Green__________ Apple
Orange________ Orange

I will get this:

A______________ B
Job Section:
Red____________ Apple
Green__________ Apple


Thanks,

 

[AFPathfinder]

Something like this?

Sub LookUp()
    Dim Found As Range
    Dim finalRow As Integer
    
    Sheets("Sheets1").Activate
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    Set Found = Sheets("Sheets2").Cells.Find(What:=Sheets("Sheet1").Range("J1").Value, _
        After:=Sheets("Sheets2").Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=True)
    On Error GoTo 0
        
    If Not Found Is Nothing Then
        With Range("B2:B" & finalRow)
            .FormulaR1C1 = "=VLOOKUP(RC[-1],Sheets2!" _
                & Sheets("Sheets2").Range("A1").CurrentRegion.Address & ",2,FALSE)"
            .Copy
            .PasteSpecial xlPasteValues
        End With
    End If
End Sub

 

[js_odom]

Thanks AFPathfinder, Sorry for the late reply, got busy with other projects over the holidays.

I wasn't able to get your code to work, I'm sure I wasn't doing something correctly.
But I was able to add this line to my original code and it works

Worksheets("Sheet1").Cells(b + 1, 1) = "Job Section"