Como cear macro para copiar celdas en función del resultado de otra celda.

Vamos a ello. Create un módulo dentro del proyecto y pega este código:

Option Explicit
Sub buscar()
'escribe en esta constante el nombre de la hoja destino
Const destino = "Hoja2"
'escribe en esta constante la primera celda de la columna J donde buscar
Const celda = "J2"
'me ajusto a tus necesidades de destino
Const copiar = "A2"
'si en un futuro necesitas más columnas, cambia este valor
Const datos = 3
Dim hoja As String, valor As String
Dim i As Integer, j As Integer, k As Integer, r As Integer, r1 As Integer, c As Integer
'como no se que valores contienen las celdas para afinar al máximo, tomo el tipo genérico y la vez,
'el saco roto porque es el que más memoria come a lo mejor sin necesidad
Dim valores(datos) As Variant
'me ubico en la posición
Range(celda).Select
'inicio variables
hoja = ActiveSheet.Name
r = ActiveCell.Row
c = ActiveCell.Column
i = 0
'busco todos los "si" en la columna J
Do While Cells(r + i, c).Value <> ""
If Cells(r + i, c).Value = "si" Or Cells(r + i, c).Value = "SI" Then
'relleno el array con los valores de las columna A, B y C con su fila correspondiente
'mucho ojo!!!! solo en este caso j coincide con la columna. Para otras columnas habría que modificar el bucle
For j = 1 To datos
valores(j) = Cells(r + i, j).Value
Next j
'me cambio de hoja, selecciono celda e inicio variables
Sheets(destino).Select
Range(copiar).Select
r1 = ActiveCell.Row
k = 0
'busca la ultima fila libra
Do While Cells(r1 + k, 1).Value <> ""
k = k + 1
Loop
'pego los datos
For j = 1 To datos
Cells(r1 + k, j).Value = valores(j)
Next j
'regreso a la hoja inicial y continuo con la búsqueda
Sheets(hoja).Select
End If
i = i + 1
Loop
End Sub

Ahora tienes que asignar este código a una celda o botón pero ojo! Dentro de la hoja de búsquedas sino no te funcionará.