Consolidar número de filas variable en VBA
Hola, expertos!
Tengo un libro con 12 hojas (una por cada mes) con datos económicos de la empresa.
Todas las hojas tienen exactamente la misma estructura; lo único que puede cambiar en el libro es el número de filas que tiene cada hoja. Es decir, un mes todas las hojas contendrán 35 filas, y otro mes puede ser que tengan 540 filas.
Quiero consolidar las 12 hojas en una nueva hoja, en vba. Para ello uso la siguiente instrucción:
Selection.Consolidate Sources:=Array( _
wb.Sheets(1).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True), _
wb.Sheets(2).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True), _
...
wb.Sheets(12).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True) _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True
Mi pregunta es:
¿Cómo puedo hacer esto sabiendo que el número de filas (en este ejemplo, 45) puede variar?
He probado con "A5:B" & cstr(nroFilas) & """", pero no funciona.
Y ya de paso, ¿se puede optimizar esta instrucción, en vez de poner las 12 hojas?
Gracias por vuestra atención, un saludo
Tengo un libro con 12 hojas (una por cada mes) con datos económicos de la empresa.
Todas las hojas tienen exactamente la misma estructura; lo único que puede cambiar en el libro es el número de filas que tiene cada hoja. Es decir, un mes todas las hojas contendrán 35 filas, y otro mes puede ser que tengan 540 filas.
Quiero consolidar las 12 hojas en una nueva hoja, en vba. Para ello uso la siguiente instrucción:
Selection.Consolidate Sources:=Array( _
wb.Sheets(1).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True), _
wb.Sheets(2).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True), _
...
wb.Sheets(12).Range("A5:B45").Address(ReferenceStyle:=xlR1C1, External:=True) _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True
Mi pregunta es:
¿Cómo puedo hacer esto sabiendo que el número de filas (en este ejemplo, 45) puede variar?
He probado con "A5:B" & cstr(nroFilas) & """", pero no funciona.
Y ya de paso, ¿se puede optimizar esta instrucción, en vez de poner las 12 hojas?
Gracias por vuestra atención, un saludo
1 Respuesta
Respuesta de Elsa Matilde
1
Estupendo, ¡Me ha ido de fábula!
Veo que no se copia el formato de las celdas, ¿se puede hacer a la vez que la consolidación?
Y respecto al último punto, de optimizar la consolidación de las 12 hojas, ¿quieres qué abra otra consulta o prefieres seguir con ésta?
¡Muchas gracias!
Veo que no se copia el formato de las celdas, ¿se puede hacer a la vez que la consolidación?
Y respecto al último punto, de optimizar la consolidación de las 12 hojas, ¿quieres qué abra otra consulta o prefieres seguir con ésta?
¡Muchas gracias!
Acabo de ver algo que no me gusta...
1) Las columnas las ordena alfabéticamente. Así, en lugar de enero, febrero, ..., aparecen abril, agosto, diciembre, etc.
2) Entre la primera columna (literales de conceptos) y la segunda (correspondiente a los datos de enero - bueno, de abril...-), aparece una columna que no existe en las hojas mensuales conteniendo el nombre del archivo excel.
¿Se puede arreglar alguno de estos puntos?
1) Las columnas las ordena alfabéticamente. Así, en lugar de enero, febrero, ..., aparecen abril, agosto, diciembre, etc.
2) Entre la primera columna (literales de conceptos) y la segunda (correspondiente a los datos de enero - bueno, de abril...-), aparece una columna que no existe en las hojas mensuales conteniendo el nombre del archivo excel.
¿Se puede arreglar alguno de estos puntos?
Esto ya es un problema diferente a la consulta original.
La consolidación, si la hacés manualmente, verás que no tiene muchas opciones más que elegir los rangos y establecer títulos y vínculos. No tiene opción de formato.
Además si la realizás manualmente verás que automáticamente te intercala las hojas dejándote ordenadas alfabéticamente.
Como esto aparentemente no resuelve tu tarea, quizás debieras pensar en un bucle para recorrer las hojas y copiar los rangos 1 a continuación del otro. Aquí podrías obtener el orden y formato deseados.
Utilizá la grabadora para obtener las instrucciones de copiar, pegar y pegado especial, formatos. Luego tené´s que incluirlas en un bucle del tipo:
For each sh in Sheets
if sh.name <> "Tu_HOja_consolidada" then
'tus instrucciones de copia
end if
next sh
Si necesitas más ayuda con el bucle por favor ingresa 1 nueva consulta para no mezclar los temas.
La consolidación, si la hacés manualmente, verás que no tiene muchas opciones más que elegir los rangos y establecer títulos y vínculos. No tiene opción de formato.
Además si la realizás manualmente verás que automáticamente te intercala las hojas dejándote ordenadas alfabéticamente.
Como esto aparentemente no resuelve tu tarea, quizás debieras pensar en un bucle para recorrer las hojas y copiar los rangos 1 a continuación del otro. Aquí podrías obtener el orden y formato deseados.
Utilizá la grabadora para obtener las instrucciones de copiar, pegar y pegado especial, formatos. Luego tené´s que incluirlas en un bucle del tipo:
For each sh in Sheets
if sh.name <> "Tu_HOja_consolidada" then
'tus instrucciones de copia
end if
next sh
Si necesitas más ayuda con el bucle por favor ingresa 1 nueva consulta para no mezclar los temas.
