crear una tabla espejo, a modo de backup

Un lector interesado en resguardar bien las modificaciones que va sufriendo una tabla, me pregunta lo siguiente: ¿como hago para que cada vez que alguien modifique algún valor de la tabla me quede almacenado ese valor y el nuevo? Buena consulta, la cual resolveremos mediante Macros.

Se me ocurrió la idea de "tabla espejo": en una hoja tengo los registros y en otra (exactamente igual a la primera) voy a ir guardando todos los cambios que se hagan en la primera. Es muy poco código y bien "entendible", así que nos encontramos ante una tarea de fácil implementación y muy útil para generar resguardos o controlar el cambio de valores a lo largo del tiempo.

En el archivo que les dejo de ejemplo tenemos una hoja llamada "original", con la siguiente tabla:

como podrán observar.... sigo con muy poca creatividad y ganas de trabajar a la hora de generar tablas.

[+/-] Ver el resto / Ocultar

Sigamos. Vamos a copiar esa tabla y a pegarla en otra hoja, a la cual llamaremos "espejo":

lo único que cambié fue el color de los encabezados (nombres de campos) para diferenciarlas un poco.

Repasando: si alguien modifica cualquier valor de la primer tabla, en la segunda deberá aparecer el valor anterior y el nuevo. 

Una de las posibles soluciones, dentro de las miles que debe haber, es ir concatenando dichos valores dentro de una misma celda, separando cada dato por un guión bajo ( _ ). Utilizo ese caracter separador por una simple razón: si pongo un guión medio ( - ) o una barra ( / ) quizás Excel podría interpretar esa concatenación como fecha (en el caso de los números) y arruinarme todo. Al punto y a la coma los descarto, por ser los separadores decimales o de miles por defecto... y también nos traería problemas. Entonces me incliné por nuestro querido y tan "interneteado" guión bajo.

Para lograr el cometido, echaré mano a la función Intersect(rango1, rango2, rango3, rangoN), que me permite detectar si una celda pertenece o no a un rango determinado. Ya lo veremos en funcionamiento y comprenderemos bien su lógica. A esa función la voy a "disparar" con el procedimiento Private Sub Worksheet_Change(ByVal Target As Range), lo que equivale a decir "cada vez que se produzca un cambio en la hoja". Viendo un breve paso a paso, sería:

1) El usuario modifica un valor existente en la tabla original

2) Detecto si la celda en cuestión se encuentra dentro de la tabla

3) Si el resultado del punto anterior es verdadero, almaceno en variables: la fila, la columna y el valor de la celda modificada

4) Con los datos anteriores me es factible llevar esa modificación a la segunda tabla, y así concateno el nuevo valor al existente.

Antes de seguir, y para facilitar la tarea, voy a seleccionar la tabla (sin incluir los encabezados) y desde el cuadro de nombres la llamaré "tabla_o", de la siguiente forma:

en el cuadro superior izquierdo (resaltado de amarillo), y luego de seleccionar la tabla (sin los encabezados) ingresamos el nombre.

Ahora tengo que detectar si la celda modificada se encuentra dentro de "tabla_o", y es ahí donde se vuelve imprescindible Intersect(rango1, rango2, rangoN).

Para hacer las cosas fáciles, les muestro de forma muy sencilla su lógica: ¿como le pregunto si la celda B6 está dentro del rango al que llamamos "tabla_o"? De la siguiente forma:

If Not Intersect(Range("B6"), Range("tabla_o")) Is Nothing Then

Y listo. Ese If... me dará como respuesta un verdadero o un falso, y así podremos obrar en consecuencia.

Veamos el código, el cual se encuentra comentado para una mejor interpretación

Private Sub Worksheet_Change(ByVal Target As Range)
'si la celda modificada está dentro del rango "tabla_o", entonces
'tomo: el nro de fila, columna y valor de esa celda
If Not Intersect(Target, Range("tabla_o")) Is Nothing Then
    With Target
        Fila = .Row
        Columna = .Column
        Valor = .Value
    End With
    'ahora llevo a Valor a la "tabla espejo", concatenándolo con
    'el existente, mediante un guión bajo ( _ ). la cuestión se
    'torna sencilla, ya que la tabla espejo es exactamente igual
    'y sus registros comparten el mismo nro de fila y columna
    With Sheets("espejo")
        .Cells(Fila, Columna) = "_" & .Cells(Fila, Columna) & "_" & Valor
    End With
End If
End Sub

Modificamos un par de valores y vemos la "tabla espejo", para ver si la tarea se llevó a cabo correctamente:

si señor... cada uno de los cambios que realicé en la tabla original se concatenaron a los existentes en la tabla espejo.

La hoja "espejo" deberá estar oculta y no nos deberemos preocupar por su formato, ya que de allí solo nos interesa saber que se fue modificando con el tiempo. Podemos almacenar cientos y cientos de cambios en una misma celda, concatenandolos y luego proceder sin problemas a su lectura.

Un agregado que haré a la consulta de mi lector, y que estimo de suma utilidad, será guardar en esa tabla espejo la: fecha, hora y usuarios que realizaron esa modificación.

Dejamos a la tabla espejo, entonces, con dos columnas mas:

E incorporamos un poco mas de código para que todo se resuelva satisfactoriamente:

Private Sub Worksheet_Change(ByVal Target As Range)
'si la celda modificada está dentro del rango "tabla_o", entonces
'tomo: el nro de fila, columna y valor de esa celda
If Not Intersect(Target, Range("tabla_o")) Is Nothing Then
    With Target
        Fila = .Row
        Columna = .Column
        Valor = .Value
    End With
    'ahora llevo a Valor a la "tabla espejo", concatenándolo con
    'el existente, mediante un guión bajo ( _ ). la cuestión se
    'torna sencilla, ya que la tabla espejo es exactamente igual
    'y sus registros comparten el mismo nro de fila y columna
    With Sheets("espejo")
        .Cells(Fila, Columna) = "_" & .Cells(Fila, Columna) & "_" & Valor
        'la fecha:
        .Cells(Fila, "D") = "_" & .Cells(Fila, "D") & "_" & Now
        'el usuario:
        .Cells(Fila, "E") = "_" & .Cells(Fila, "E") & "_" & Environ("username")
    End With
End If
End Sub

Listo. Si modifico el legajo de "esteban" y luego la edad de "walter" dicho cambio quedará reflejado, y a la vez también guardo en que fecha/hora se realizó y que usuario llevó a cabo tal tarea:

podemos ver: el dato anterior, el guión bajo y el nuevo dato. y en las columnas D y E, respectivamente, la fecha/hora y el usuario que efectuó los cambios.

Claro que ver tan pocos registros es sencillo, pero ¿que pasa si hablamos de cientos de modificaciones? ¿como le "seguimos el hilo" a tantos datos? Una posible salida sería crear una macro que, previa selección de la celda en cuestión, nos arme un reporte fácil de leer:

Sub CrearReporte()
Dim Direccion As String
Dim Matriz, X

'guardo el nombre de la celda seleccionada:
Direccion = ActiveCell.Address

If MsgBox("Desea crear un reporte con los datos de la celda " & Direccion, vbYesNo) = vbNo Then
    Exit Sub
Else
    'utilizo a split para crear una matriz con todos los valores que
    'contiene la celda. paso como segundo argumento al guión bajo,
    'ya que es el caracter que separa cada valor:
    Matriz = Split(ActiveCell.Value, "_")
    'primero veo si existen modificaciones, contando la cantidad de
    'elementos de la matriz
    If UBound(Matriz) = 0 Then
        'si UBound=0, no hay registros históricos
        MsgBox "La celda " & Direccion & " no posee cambios", vbInformation
        Exit Sub
    Else
        'deshabilito los eventos de excel
        Application.EnableEvents = False
        'agrego una nueva hoja
        Sheets.Add
        'y recorro a Matriz, volcando los valores:
        For X = 0 To UBound(Matriz)
            ActiveSheet.Cells(X + 1, 1) = Matriz(X)
        Next X
        'pongo un encabezado
        ActiveSheet.Range("a1") = "legajos"
        'habilito nuevamente los eventos
        Application.EnableEvents = True
    End If
End If
End Sub

Entonces, al ejecutar la macro:

Y luego de presionar "Sí":

tendremos un reporte con todos los movimientos históricos (cambios) que se realizaron en esa celda

Habrán notado que utilicé a Environ() para obtener el nombre de usuario. Esta instrucción me permite acceder a las variables de entorno del sistema operativo. ¿Que son? Aquí les dejo un link interesante sobre el tema. Podemos utilizar a cualquiera de ellas... y les aseguro que les serán de mucho provecho en sus proyectos.

El tema de crear un reporte puede llevar, casi sin temor a exagerar, un blog aparte. Incluí un pequeño ejemplo para (mas que nada) demostrar una posible forma, aunque si hablamos de muchos registros será mejor llevar esa data a un archivo TXT y desde allí controlarla. También sería oportuno incluir las fechas y usuarios... pero se los dejo, de práctica.
Aquí está el archivo con el ejemplo. Mucha suerte, en serio muchas gracias por todos sus mails/mensajes y hasta la próxima consulta o entrada.