Find data in a column from a cell reference in ano

Question

I have 3 worksheets in the workbook, 1st is the Info sheet where cell S1 has the Trainer Name that i need to find in the 2nd sheet named Classes. once the Trainer Name is found in the Classes sheet (Column H), i then need to put that Trainer name in the Output sheet(next blank row, column A). Then i also need to get Class Name from Classes (column A), Grad Date(Column P) and a few more data in columns X to AB. i can't seem to get how to code this correctly as the code i have runs but it does not enter the data into the Output sheet. i've only tested 2 fields so far.

Sub GetClassData()
Dim cls As Worksheet
Dim shOUT As Worksheet
Set cls = Worksheets("Classes")
Set shOUT = Worksheets("Output")
Dim trName As Range
Set trName = Worksheets("Info").Range("S1")

cls.Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
    ' Decide if to copy based on column H
    thisvalue = Cells(x, 8).Value
    If thisvalue = trName.Value Then
        irow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        With shOUT
            .Cells(irow, 1).Value = trName.Value
            .Cells(irow, 2).Value = trName.Offset(, -7).Value
        End With
    End If
Next x
End Sub

Answer 1

Try the code below (explanations are inside the Code comments):

Option Explicit

Sub GetClassData()

Dim cls As Worksheet
Dim shOUT As Worksheet
Dim trName As Range
Dim x As Long
Dim iRow As Long
Dim FinalRow As Long
Dim thisvalue As String

Set cls = Worksheets("Classes")
Set shOUT = Worksheets("Output")
Set trName = Worksheets("Info").Range("S1")

With cls
    ' Find the last row of data in Column "A"
    FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column H
        thisvalue = .Range("H" & x).Value
        If thisvalue Like trName.Value Then ' <-- check the names
            iRow = shOUT.Cells(shOUT.Rows.Count, "A").End(xlUp).Row + 1

            shOUT.Range("A" & iRow).Value = thisvalue '<-- get Trainer Name
            shOUT.Range("B" & iRow).Value = .Range("A" & x).Value '<-- get Class Name for Classes
            ' add the rest of the thing you need to copy here

        End If
    Next x
End With

End Sub