Manos a la obra. Continúo con el tema de separar los datos y enviar a cada uno lo suyo (ver primera parte).

Para simplificar los bucles, vamos a definir algunos nombres en el libro, por ejemplo definimos el nombre ListaDestinatarios con el rango que contiene los nombres que luego aplicaremos en el filtro.

También defino un nombre Datos que se refiere al rango donde se encuentran los datos.

Es muy recomendable que los datos estén en una tabla, así al añadir nuevos registros, la tabla se adapta al nuevo tamaño y no hay que perder el tiempo rehaciendo fórmulas.

Vamos a preparar una hoja auxiliar, que será el modelo que se enviará a los destinatarios.

En esta hoja vamos a poner los criterios de filtrado (así quien lo reciba sabrá qué se le está mandando). Para este ejemplo el único criterio va a ser el nombre del destinatario.

También defino unos nombres:
CeldaCriterio que es la celda que contendrá el nombre del destinatario
RangoCriterios que contiene los rangos de filtrado, en nuestro caso la celda con el nombre del campo (destinatario) y la celda de debajo, que hemos llamado CeldaCriterio.
emaildestinatario en una celda de al lado, mediante BUSCARV hago que Excel busque el email del destinatario.
y EncabezadoResultado que contiene los encabezados dato1, dato3, dato3 de los datos que queremos extraer al filtrar.

El filtro

Si ejecutásemos el filtro avanzado a mano, haríamos algo así (aprovechando los nombres que hemos definido, que queda todo más claro)

Escribiría el nombre del destinatario en la celda criterio y luego aplicaría este filtro

Hay que tener en cuenta una cosa, y es que el filtro avanzado debe ejecutarse en la hoja de destino, en nuestro caso, debe ejecutarse desde la hoja que contiene el modelo de los datos filtrados.

Truco para insertar un nombre definido, utiliza la tecla [F3]

Ahora con una macro

Vamos ahora a ver cómo sería una macro que aplicase este filtro (no os voy a engañar, he grabado una macro mientras ejecutaba el filtro a mano).

Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "RangoCriterios"), CopyToRange:=Range("EncabezadoResultado"), Unique:=False

Copiar la hoja y enviarla

Lo que hay que hacer a continuación es copiar esta hoja a un nuevo libro y después enviar el libro al destinatario (también he grabado una macro para esto)

Sheets("ModeloDatosFiltrados").Select
Sheets("ModeloDatosFiltrados").Copy

Para enviar el email, al grabar la macro emplea Application.Dialogs(xlDialogSendMail).Show, pero esto muestra el cuadro de diálogo para enviar el correo, así que no nos sirve, porque tendríamos que elegir al destinatario a mano.

Vamos a utilizar mejor el método sendmail del libro activo

Activeworkbook.SendMail  "emaildestinatario", "asunto"

Al enviar un email desde programación es probable que el programa de correo muestre una advertencia de seguridad. En el caso de Outlook es algo así, y puede evitarse modificando las opciones del centro de confianza (cada programa de correo tiene sus opciones).

El programa de correo deberá estar abierto para que empiece a mandar los emails.
Para más información sobre enviar correos desde Excel mira aquí y aquí.

El bucle que lo envuelve todo

No hay que olvidar que queremos repetir este filtro para cada destinatario de la lista. Pues vamos a hacer un bucle que recorra toda esta lista.

Los destinatarios están en el rango que hemos llamado ListaDestinatarios, así que recorremos las celdas de este rango con un bucle For Each...

y quedaría algo así.

Sub SepararYEnviar()
'macro que filtra datos y envía a cada destinatario una copia con lo suyo
Dim Celda As Range
Dim EmailDestino As String
'Activamos la hoja destino
Sheets("ModeloDatosFiltrados").Select
    For Each Celda In Range("ListaDestinatarios").Cells
        Range("CeldaCriterio").Value = Celda.Value       'ponemos el  criterio
        EmailDestino = Range("emaildestinatario").Value  'tomamos el email del destinatario
        ' aplicamos el filtro avanzado  
        Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "RangoCriterios"), CopyToRange:=Range("EncabezadoResultado"), Unique:=False
       
        Sheets("ModeloDatosFiltrados").Copy ' copiamos la hoja a un nuevo libro
        ' el nuevo libro es ahora el activo
        ActiveWorkbook.SendMail EmailDestino,"envío de datos" 'enviamos el correo
        ActiveWorkbook.Close False  ' cerramos el nuevo libro sin guardar los cambios
    Next
    ' siempre es bueno avisar de que has terminado
    MsgBox "Mensajes enviados", vbInformation + vbOKOnly, "Ya sé Excel, pero necesito más"
End Sub