Anuncios

miércoles, 20 de noviembre de 2019

Como realizar combinación de correspondencia entre dos hojas de Excel

Aprende combinar correspondencia entre dos hojas de excel o como realizar combinación de correspondencia entre dos hojas de excel el cual es una herramienta fundamental cuando desee crear un conjunto de documentos, como una carta modelo que se debe enviar a muchos clientes o una hoja de etiquetas de dirección. Cada carta o etiqueta contiene el mismo tipo de información, aunque su contenido es único. Por ejemplo, en las cartas que envíe a sus clientes, cada carta se puede personalizar con el nombre de la persona a la que vaya dirigida. La información única de cada carta o etiqueta procede de las entradas contenidas en un origen de datos.


combinacion de correspondencia en excel

¿COMO REALIZAR COMBINACIÓN DE CORRESPONDENCIA ENTRE DOS HOJAS DE EXCEL?


Con esta macro nos va permitir realizar lo siguiente:


  • Realizar combinación de correspondencia entre dos hojas e excel
  • Tener presente que se debe tener una hoja con la carta o estructura modelo y la segunda hoja con la base de datos para realizar la combinación de correspondencia.

Revisa el siguiente video para que aprendas a utilizar la macro y en la parte inferior del articulo se encuentra el enlace para que la puedas descargar




Importante que para utilizar la macro debemos de tener habilitados el menú de programador o desarrollador dependiendo de la versión de excel que tengas. En este CORTO VIDEO te explico como lo puedes HABILITAR


Se recomienda como el ejemplo que vamos a realizar donde tenemos una hoja con la carta modelo y se va llamar correspondencia y la otra hoja donde vamos a tener la base de datos se va llamar BD, como se muestra en la imagen.


combinación de correspondencia en excel


En la siguiente imagen se encuentra la información en la hoja BD para realizar la combinación de correspondencia.


combinación de correspondencia en excel

Es muy importante que en la hoja correspondencia vamos a colocar de manera manual o escribir las variables donde queremos que aparezca la información entre signo < > ejemplo como se muestra en la imagen tenemos Señor(a): y aquí queremos que nos aparezca el nombre entonces colocamos <NOMBRE> se debe tener presente que se debe colocar tal cual como lo tenemos el titulo en la hoja BD


combinación de correspondencia en excel

Quedaría nuestra carta de la siguiente manera con todas las variables que voy a utilizar en la hoja BD


combinación de correspondencia en excel

Después de que tengamos habilitado las macros debemos de dirigirnos a la pestaña de desarrollador o programador  y posteriormente grabar macro.


combinación de correspondencia en excel

En la ventana de grabar macro podemos colocar el nombre que queramos a la macro y algo que es muy importante que vamos a digitar un comando para ejecutar la macro y para eso podemos colocar la r para que en el momento de presionar ctrl + r se ejecute la macro y presionamos aceptar.


combinación de correspondencia en excel

Posteriormente nos debemos dirigir a la pestaña programador y luego en el grupo de código presionamos en detener grabación.


combinación de correspondencia en excel

luego nos vamos nuevamente para la pestaña programador y nos dirigimos en el grupo código y presionamos clic en macros


combinación de correspondencia en excel

En el momento que realizamos el paso anterior debemos presionar clic en el botón de modificar, para aquí acceder al código de la macro.


combinación de correspondencia en excel

En la siguiente ventana debemos de pegar la macro que la puedes descargar en la parte inferior del articulo.


combinación de correspondencia en excel

En el momento de presionar con el teclado los comandos ctrl + r automaticamente nos va ejecutar la macro y nos abre la siguiente ventana, escribe el número de criterios para el ejemplo va ser el número 6 y presionamos aceptar.


combinación de correspondencia en excel

Luego se ejecuta la macro donde me crea las combinaciones de correspondencias como se muestra en la imagen.


combinación de correspondencia en excel

Adicionalmente me crea una nueva hoja por cada fila que vamos a utilizar en la correspondencia en total de acuerdo a la imagen anteriores la base de datos era con 10 nombres o 10 filas y para eso excel crea las combinaciones con los diferentes datos y crea las 10 horas con el nombre de correspondencia seguido del número consecutivo.


combinación de correspondencia en excel




combinación de correspondencia en excel


La macro consisten en lo siguiente

Caja de texto para escribir el numero de criterios
NumCriterios = InputBox("Escribe el número de criterios")


''''''''''''''''''PARA 1 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''

If NumCriterios = 1 Then

Selecionamos y copiamos al primera hoja de correspondencia

Sheets("Correspondencia").Select
Sheets("Correspondencia").Copy Before:=Sheets(2)

contar el numero de datos
CantDatos = Sheets("BD").Cells(Rows.Count, "A").End(xlUp).Row 

Variable1 = Sheets("BD").Range("A1")
Dato1 = Sheets("BD").Range("A2")
Cells.Replace What:="<" & Variable1 & ">", Replacement:=Dato1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Reemplazamos las variables por los datos de la base de datos
ReplaceFormat:=False
ContFilaAnt = 2
NumPagina = 3

Ciclo para realizar las combinaciones
For datos = 2 To CantDatos 
NombreHoja = ActiveSheet.Name

Copia hoja y enumera el consecutivo
Sheets(NombreHoja).Copy Before:=Sheets(NumPagina) 
Variable1 = Sheets("BD").Range("A" & ContFilaAnt)
Dato1 = Sheets("BD").Range("A" & NumPagina)
Cells.Replace What:=Variable1, Replacement:=Dato1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Busca y reemplaza los valores de la base de datos
ReplaceFormat:=False 

incremento de contadores
ContFilaAnt = ContFilaAnt + 1 
NumPagina = NumPagina + 1
Next
Else

''''''''''''''''''PARA 2 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''

If NumCriterios = 2 Then
Sheets("Correspondencia").Select
Sheets("Correspondencia").Copy Before:=Sheets(2)

contar el numero de datos
CantDatos = Sheets("BD").Cells(Rows.Count, "A").End(xlUp).Row 

Variable1 = Sheets("BD").Range("A1")
Variable2 = Sheets("BD").Range("B1")

Dato1 = Sheets("BD").Range("A2")
Dato2 = Sheets("BD").Range("B2")

Cells.Replace What:="<" & Variable1 & ">", Replacement:=Dato1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="<" & Variable2 & ">", Replacement:=Dato2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ContFilaAnt = 2
NumPagina = 3

For datos = 2 To CantDatos
NombreHoja = ActiveSheet.Name
Sheets(NombreHoja).Copy Before:=Sheets(NumPagina)
Variable1 = Sheets("BD").Range("A" & ContFilaAnt)
Variable2 = Sheets("BD").Range("B" & ContFilaAnt)
Dato1 = Sheets("BD").Range("A" & NumPagina)
Dato2 = Sheets("BD").Range("B" & NumPagina)

Cells.Replace What:=Variable1, Replacement:=Dato1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:=Variable2, Replacement:=Dato2, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ContFilaAnt = ContFilaAnt + 1
NumPagina = NumPagina + 1
Next
Else

''''''''''''''''''PARA 3 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''

La macro se repite los ciclos para cada criterio con la diferencia que se agrega una variable mas para que pueda realizar los cambios, la macro esta realizada hasta 8 criterios o variables


En el siguiente ENLACE puedes DESCARGAR el archivo para realizar las pruebas que se menciona en el ejemplo


En el siguiente ENLACE puedes DESCARGAR la macro para combinación de correspondencia entre dos hojas de excel.





Seguramente quieres sabes más acerca de Excel, no te olvides darte una vuelta por las siguientes secciones disponibles para ti de manera gratuita

Canal de
 YouTube
Comunidad Telegram

Hasta una próxima y te deseo el mayor de los EXITOS.

Aprende y Enseña Excel.