I placed the data in the Sheet "CofA" starting at row 12.
Sub FindCofA_B2()
Dim sFind As String
Dim rg As Range, rgCell As Range
Dim vColumns As Variant
Dim i As Long
' The Columns Marked "SS"
vColumns = Array(6, 7, 8, 9, 11, 14, 15, 16, 17)
sFind = Worksheets("CofA").Range("B2").Value
With Worksheets("Data")
Set rg = .Range("A4:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
Set rgCell = rg.Find( _
What:=sFind, _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rgCell Is Nothing Then
With Worksheets("CofA")
' Starting at Row 12
For i = LBound(vColumns) To UBound(vColumns)
.Cells(i + 12, "A") = Worksheets("Data").Cells(2, vColumns(i))
.Cells(i + 12, "C") = Worksheets("Data").Cells(rgCell.Row, vColumns(i))
Next i
End With
End If
End Sub
Bookmarks