Macro para consolidar datos usando la función "Consolidar" de excel

[sky_eyes]

Buenas tardes,

Tengo una hoja excel con varias pestañas con datos. Estos datos los consolido en otra hoja en el mismo documento. Lo hago manualmente utilizando la función del menú Datos-> Consolidar. Quería automatizar el proceso y crear una macro para que sólo pulsando un botón lo hiciera, A través de Menú Programador, quise grabar la macro, pero me da error al grabarla.

He estado buscando por internet y en el foro y no encuentro como usar la función "Consolidar" en una macro.

¿Alquien me podría ayudar por favor?

Gracias!

 

 

[Israel Cassales]

Hola, la solicitud es clara. Tal vez si me permites la aplicación no tanto.

Un libro con el ejemplo ayudaría para ver ciertas cosas, como el diseño de cada hoja individual que debe ser igual, mencionar también que si lo dejas formulado hacia una hoja se podría pensar que usar VBA no es necesario porque los resultados se calculan automáticamente. La idea obviamente la tienes bien definida, el compartirla es en ocasiones lo que se complica, sin mencionar el explicar o entender.

Si ya tienes algún código adelantado por favor podrías mostrarlo, seguro algo se puede recomendar.

Saludines.

[sky_eyes]

Hola, 

Adjunto Excel simplificado de ejemplo. Hojas de 1 a 4 , a partir de la linea 28, se debe rellenar el número de proyecto y para cada dia de la semana, las horas dedicadas a ese proyecto. En la hoja Resume, al hacer la consolidación, se obtienen las horas totales dedicadas a cada numero de proyecto.

Para hacerlo manualmente, sitúo el cursor en la hoja Resume celda A2, y voy a la opción "Consolidar", las referencias ya están incluídas (todas las hojas de la 1 a la 4, celdas de $B$28 a $I$60), selecciono la columna izquierda y a partir de allí me agrupa por número de proyecto el total de horas dedicadas. La columna I de la Hoja Resume sencillamente hace la suma, así se obtiene el total de tiempo dedicado a cada proyecto.

No tengo código por compartir, ya que desconozco cuál es la función que me permite hacer lo mismo que hace la funsión Datos -> Consolidar (es precisamente lo que estoy consultando)., ya que al grabar la Macro haciendo el proceso manual, me da error y no se genera ningún código.

Gracias

Ejemplo.xlsm

[Israel Cassales]

hace 7 horas, Israel Cassales dijo:

mencionar también que si lo dejas formulado hacia una hoja se podría pensar que usar VBA no es necesario porque los resultados se calculan automáticamente.

Hola, viendo el libro me sigue pareciendo una buena recomendación usar funciones, así el cálculo se da sin necesidad de dar clic en algún botón.

Esta es mi propuesta en fórmulas, dale una revisada. Pero como estamos en la sección VBA dame unos minutos para hacer un código que haga lo mismo que las funciones pero dando clic en algún botón.

Saludines.

 

Ejemplo - FUNCIONES.xlsm

[Israel Cassales]

En código, sería algo así:

Sub CalcularSuma()
    Dim total As Double
    Dim hoja As Worksheet
    Dim fila As Integer
    Dim columna As Integer
    For fila = 2 To 100
        For columna = 4 To 8
            total = 0
            For Each hoja In ThisWorkbook.Worksheets
                If hoja.Name = "1" Or hoja.Name = "2" Or hoja.Name = "3" Or hoja.Name = "4" Then
                    total = total + Application.WorksheetFunction.SumIf(hoja.Range("B28:B100"), Worksheets("Resume").Range("A" & fila).Value, hoja.Range(hoja.Cells(28, columna + 1), hoja.Cells(100, columna + 1)))
                End If
            Next hoja
            Worksheets("Resume").Cells(fila, columna).Value = total
        Next columna
    Next fila
End Sub

Sería que lo adaptes a tu libro de trabajo.

[Israel Cassales]

Te dejo el libro donde hice pruebas con el VBA.

Debería cumplir con la solicitud. Saludines.

 

Ejemplo - VBA.xlsm

[Abraham Valencia]

Hola amigos

La herramienta Consolidar de Excel sí tiene su equivalente en VBA y es el método Consolidate del objeto Range, hay información al respecto en internet, yo diría que su única dificultad es que los rangos de las hojas a consolidar se tienen que incluir con referencias R1C1 que a algunas personas se les hace un poco difícil de entender.  Para tu caso sería así:

Sub Consolidar_VBA()

Worksheets("Resume").Range("A2").Consolidate _
    Sources:=Array("1!R28C2:R60C9", "2!R28C2:R60C9", "3!R28C2:R60C9", "4!R28C2:R60C9"), _
        Function:=xlSum

End Sub

No olviden que siempre es más rápido trabajar con matrices que con bucles.

Saludos

[JSDJSD]

 

Un saludo para todos.

Para aquellas personas a las que se les puede resistir el entender las referencias R1C1 dejo una breve explicación:

Cada elemento del array de la macro del Maestro Abraham Valencia corresponde a una dirección de rango en formato R1C1

- -> "1!R28C2:R60C9" se refiere al rango de celdas desde la celda R28C2 (fila 28, columna 2) hasta la celda R60C9 (fila 60, columna 9) en la hoja "1".

--> "2!R28C2:R60C9" se refiere al rango de celdas desde la celda R28C2 (fila 28, columna 2) hasta la celda R60C9 (fila 60, columna 9) en la hoja "2".

--> "3!R28C2:R60C9" se refiere al rango de celdas desde la celda R28C2 (fila 28, columna 2) hasta la celda R60C9 (fila 60, columna 9) en la hoja "3".

--> "4!R28C2:R60C9"se refiere al rango de celdas desde la celda R28C2 (fila 28, columna 2) hasta la celda R60C9 (fila 60, columna 9) en la hoja "4".

[Israel Cassales]

Saludines

En resumen la R es de ROW que significa Fila y la C es de COLUMN que significa Columna contando de arriba para abajo y de izquierda a derecha.

R1 (Fila1) C1 (Columna1)

[Abraham Valencia]

hace 25 minutos , Israel Cassales dijo:

Saludines

En resumen la R es de ROW que significa Fila y la C es de COLUMN que significa Columna contando de arriba para abajo y de izquierda a derecha.

R1 (Fila1) C1 (Columna1)

Hola. Sí y no; sí porque R sí es de ROW y C por Column, pero para nada R1 es fila 1 y C1 columna 1, ya que eso depende del rango al qué se ha referencia. En este caso  al no hacer referencia directa a ninguna celda y solo a la hoja, se toma A1 de forma predeterminada, pero no siempre es así. Ejemplo:

Range("A11").FormulaR1C1 ="=SUM(R[-10]C:R[-1]C)"

¿Existe una fila -10, u otra -1 o columna cero? No, lo que pasa es que aquí se hace referencia a la celda A11 por lo que es lo mismo que decir:

Suma(A1:A10)

Por eso justamente decía que suele resultar confuso para la mayoría. Décadas en esto como para equivocarme.

[Israel Cassales]

hace 5 minutos , Abraham Valencia dijo:

Por eso justamente decía que suele resultar confuso para la mayoría. Décadas en esto como para equivocarme.

Pues a  mí me parece excelente que haya extensiones o aclaraciones a los temas.  El saber es bueno, el compartirlo no cualquiera.

[sky_eyes]

Muchas gracias a todos! He logrado hacerlo con el código facilitado por Abraham!