CODEWITHSUNDEEP
excelvba
Cielo Salas
Cielo Salas

Reputation: 95

Find data in a column from a cell reference in another worksheet then copy some data to another worksheet

I have three worksheets in the workbook: First is the Info sheet where cell S1 has the Trainer Name to find in the second sheet named Classes.

Once the Trainer Name is found in the Classes sheet (Column H), I 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.

The code runs but does not enter the data into the Output sheet. I've only tested two 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

Upvotes: 0

Views: 93

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Related Questions