Buscar valores dentro de una tabla!

Considero que la manera más sencilla de hacer esto es con un tabla dinámica, primero escoges todas las columnas que contienen datos en tu tabla, luego vas a insertar y eliges tabla dinámica, das aceptar y para el llenado de la tabla en filtro del informe escoges apellido y en rótulos de fila escoges todo lo demás, das entre y lista tu tabla.

Cuando necesitas un apellido en especifico vas al filtro del informe y en apellido eliges el que necesitas, y tu tabla filtrará solo lo que necesitas.

El único cuidado que debes tener es que cada vez que vayas a generar el listado, antes des click derecho sobre la tabla y escoges actualizar, por si hayas ingresado algún dato nuevo desde la última vez que actualizaste la tabla.

el dilema es que la tabla es enorme y contiene muchos apellidos entonces el buscar dentro de la lista del filtro el apellido me supone la misma dedicación a buscarlo manualmente no se si me doy a entender. existirá otra solución posible???

Ok podríamos probar con filtros avanzados, pero lo vamos a hacer con una macro ya que lo necesitas en otra hoja.

1) Siguiendo tu ejemplo primero en un modulo pega los siguientes códigos.

Sub Filtra()
valor = Sheets("Hoja2").Range("B1").Value
Sheets("Hoja1").Select
Sheets("Hoja1").Range("I1").Value = "Apellido"
Sheets("Hoja1").Range("I2").Value = valor
Columns("A:G").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I1:I2"), CopyToRange:=Range("K1:Q1"), Unique:=False
Range("I1").Value = ""
Range("I2").Value = ""
Columns("K:P").Select
Selection.Cut
Sheets("Hoja2").Select
Range("D1").Select
ActiveSheet.Paste
Range("B1").Select
End Sub

Luego para darle funcionalidad a tu hoja 2 en el vba de la hoja2 escribe el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Call Filtra
End If
End Sub

Con estos códigos debes escribir el apellido a buscar en B1, y cada vez que cambies el apellido, la macro automáticamente irá a buscar en tu tabala base y utilizando los filtros avanzados te extraerá una lista donde la única condición de similitud es el apellido.

Avisame como te va

disculpa la tardanza , introduje los códigos como me dijiste pero me sale el error 1004 no se a que se refiere y me muestra la siguiente linea del modulo que no anda

Columns("A:G").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I1:I2"), CopyToRange:=Range("K1:Q1"), Unique:=False

atento a tus comentarios.

1) Como se llaman las hojas que contienen los datos y en la que pegas la info para que la busque(¿Hoja1 y Hoja2?)

2) Cuál es el rango de la información

3) El rango luego de la columna I, ¿está libre?

te contesto

1) efectivamente las hojas se llaman Hoja1 (donde esta la tabla) y Hoja2 (la que sera el buscador)

2) rango desde B3 hasta H1886 (creo que asi se define B3:H1886)

3) tengo valores ocultos que utilizo para unas formulas (hipervínculo y concatenar) desde las columnas I a la M

espero darme a entender. sigo agradeciendo tu buena disposición.

Ok, prueba con estos cambios.

Sub Filtra()
valor = Sheets("Hoja2").Range("B1").Value
Sheets("Hoja1").Select
Sheets("Hoja1").Range("N1").Value = "Apellido"
Sheets("Hoja1").Range("N2").Value = valor
Columns("B:H").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"N1:N2"), CopyToRange:=Range("N1:U1"), Unique:=False
Range("N1").Value = ""
Range("N2").Value = ""
Columns("N:U").Select
Selection.Cut
Sheets("Hoja2").Select
Range("D1").Select
ActiveSheet.Paste
Range("B1").Select
End Sub
Luego para darle funcionalidad a tu hoja 2 en el vba de la hoja2 escribe el siguiente código:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Call Filtra
End If
End Sub

Para que funcione en las columnas ed B hasta H, debe en la fila 1 estar los totulos de las columnas, y la información a buscar debe estar desde la fila 2 hacía abajo.

Prueba y me avisas