Excel - Insertar filas según el valor de una celda y copiar formula de la fila superior

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 27/03/2018 20:32:04

Hola

He creado un libro de excel (resumen.xlsm) cuyos datos están vinculados a múltiples libros generados a partir de
una plantilla. El objetivo de este archivo es crear dos tablas dinámicas (ubicadas en la hoja RESUMEN) para contar todos los alumnos por profesor/curso/aula agrupándolos por 1) año de nacimiento y 2) año de aprobado.

Una tabla "Aulas" me dice cuántos alumnos (Núm. Alumnos) han pasado por el centro, y a qué profesor/curso/aula pertenecen. Cuando se inscriben nuevos alumnos, la columna "Nuevos" son los alumnos que faltan por añadir en la tabla "Datos".

La tabla "Datos" es la que contiene los datos de todos los alumnos vinculados a los libros de cada aula.

La cuestión es crear una macro que inserte en la tabla "Datos" tantas filas como alumnos nuevos se indican en la columna "Nuevos" de la tabla "Aulas" de cada aula debajo de la última fila que coincida con las condiciones de igualdad para "Profesor" y "Curso" y luego copie el contenido de esta última fila a las añadidas por la macro.

Es decir, en el ejemplo adjunto:

- Insertar 5 filas en la tabla "Datos" en la última fila que coincida "Profesor" y "Aula", Aurelia Arístides y Delfines, respectivamente y luego copie las fórmulas de ésta última fila a las 5 añadidas,
- Insertar 8 filas en la tabla "Datos" en la última fila que coincida "Profesor" y "Aula", Aurelia Arístides y Colibríes, respectivamente y luego copie las fórmulas de ésta última fila a las 8 añadidas,
- ... Y así sucesivamente hasta el total de filas de la tabla "Aulas".

Espero que me haya explicado correctamente y puedan ayudarme ya que esto me libraría de un trabajo tedioso.

Un saludo!

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 4.908

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Antoni Masana (2478 intervenciones) el 28/03/2018 07:03:39

Hace falta ver los libros para hacer la macro.
Programar a ciegas nunca a funcionado bien.

Saludos.
\\//_

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 28/03/2018 08:53:54

Hola

Juraría que adjunté el archivo. Lo adjunto de nuevo

Gracias

Resumen.zip(163,9 KB)

Valora esta respuesta

Comentar

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 28/03/2018 10:12:28

Hola Antoni

Me he dejado un apunte para añadir a la macro:

- Si el valor de la columna "Nuevos" de la tabla "Aulas"(filas que hay que añadir en la tabla "Datos") es negativo o texto (es decir, que no es un número positivo), la macro no debe hacer nada y saltar a la siguiente fila de la tabla "Aulas".

¡Un saludo!

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Antoni Masana (2478 intervenciones) el 28/03/2018 11:58:37

Te adjunto dos libros:
El Resumen_V2 tiene la macro sin ejecutar
El Resumen_V3 tiene la macro y los cambios después de la ejecución.

Cosas a tener en cuenta:

Hoja AULAS una vez ejecutada la macro borra la columna Suma el valor de la columna E (Nuevos), a la columna D (Núm. alumnos) y borra la columna E. Si se ejecuta por segunda vez no se modifican los datos.

Hoja DATOS: las columnas D (Alumno), E (Fecha Nacimiento) y F (Fecha Aprobado) se dejan en blanco.

Los valores de la columna D (Núm. alumnos) de la hoja AULAS no coinciden con los registros de la hoja DATOS, en todos hay una diferencia de 1 de más en las hoja AULAS en cada linea.

Saludos.

Resumen_2.zip(345,0 KB)

Valora esta respuesta

Comentar

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 28/03/2018 12:44:44

Hola Antoni

¡Muchas gracias!

Tan pronto pueda, me lo miro y te digo algo.

Un saludo y gracias por el trabajo.

Lluís

Valora esta respuesta

Comentar

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 30/03/2018 11:14:59

Hola Antoni

He estado probando tu macro en mi archivo definitivo y funciona estupendamente. He hecho un cambio:

- He eliminado el hecho que la macro sume el valor de la columna "Nuevos" al de la columna "Núm. Alumnos" y luego borre los valores de la columna "Nuevos" para llevarlo a 0. Entiendo que esto es una actualización, però en mi caso no es necesario, ya que estos valores son conultas a tablas vinculades y se actualizan automàticament del archivo origen.

Por otro lado, la parte del pegado a las nuevas filas añadidas en la Hoja "Datos" es lo que no entiendo:

- También debe copiar las columnas de "Alumno", "Fecha Nacimiento" y "Fecha Aprobado". Eso si, debe copiar las fórmulas de la fila anterior, no los valores. Esto es muy importante ya que todos los datos de este libro están vinculados a otros libros y por tanto, se rellenarían con los datos reales de forma automàtica.

Otra vez, ¡muchas gracias!

...cada vez se hace menos tedioso este trabajo.

Lluís

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Antoni Masana (2478 intervenciones) el 30/03/2018 20:47:25

En el libro que he recibido en las tres columnas a las que te refieres solo hay valores y no hay formulas o referencias para copiarlas.
Lo de borrar la columna Nuevos es porque mientras tenga datos la macro se puede ejecutar varias veces y duplicaría las inserciones.

Te copio la macro y te pongo comentarios.

Sub Añadir_Nuevos_a_Datos()

    Dim Fila_Aul As Long, Prof As String, Alum As Long, _

        Curs As String, Nuev As Long, _

        Fila_Dat As Long, Aula As String, a As Integer

    ' --- Esto es basicamente para optimizar la macro.

    '     evita el parpadeo y que recalcule las formulas cada

    '     vez que se modifica una celda

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Application.EnableEvents = False

    ActiveSheet.DisplayPageBreaks = False

    ' --- Inicio Variables y selecciona hoja Aulas

    Fila_Aul = 2

    Fila_Dat = 2: Sheets("Aulas").Select

    While Sheets("Aulas").Cells(Fila_Aul, 1) <> ""

        ' --- Toma los datos de la primera fila

        Prof = Cells(Fila_Aul, 1)

        Curs = Cells(Fila_Aul, 2)

        Aula = Cells(Fila_Aul, 3)

        Alum = Cells(Fila_Aul, 4)

        Nuev = Cells(Fila_Aul, 5)

        ' --- Pasa a la hoja de datos

        Sheets("Datos").Select

        ' --- repasa las filas mientras Profesor, Curso y Aula sean los

        '     mismos que la linea leida de la hoja Aula

        While Cells(Fila_Dat, 1) = Prof And Cells(Fila_Dat, 2) = Curs And _

              Cells(Fila_Dat, 3) = Aula And Cells(Fila_Dat, 1) <> ""

              Fila_Dat = Fila_Dat + 1

        Wend

        ' --- Si hay nuevos para añadir

        If Nuev > 0 Then

           ' --- realiza el bucle tantas veces como el valor Nuevos

           For a = 1 To Nuev

               ' Añade una linea por encima de la posicionada

               Rows(Fila_Dat & ":" & Fila_Dat).Select

               Selection.Insert Shift:=xlDown

               ' Pega los datos de Profesor, Curso y Aula

               Cells(Fila_Dat, 1) = Prof

               Cells(Fila_Dat, 2) = Curs

               Cells(Fila_Dat, 3) = Aula

               ' --- Se posiciona en la fila siguiente que coincidida con la

               '     siguiente de Aula cuando la lea

               Fila_Dat = Fila_Dat + 1

           Next

       End If

       ' --- Seleciona Hoja Aulas

       Sheets("Aulas").Select

       ' --- Las acciones que has suprimido

       Cells(Fila_Aul, 4) = Alum + Nuev

       Cells(Fila_Aul, 5) = 0

       ' --- Posiciono en fila siguiente de Aulas

       Fila_Aul = Fila_Aul + 1

    Wend

    ' --- Restaura el entorno antes de finalizar la macro

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    Application.EnableEvents = True

    ActiveSheet.DisplayPageBreaks = True

    Application.CutCopyMode = False

End Sub

Para copiar las formulas de la fila anterior añade estas cuatro lineas a la hora de insertar:

               ' --- Pega los datos de Profesor, Curso y Aula

               Cells(Fila_Dat, 1) = Prof

               Cells(Fila_Dat, 2) = Curs

               Cells(Fila_Dat, 3) = Aula

               Range("D" & Fila_Dat - 1 & ":F" & Fila_Dat - 1).Select

               Selection.Copy

               Range("D" & Fila_Dat).Select

               ActiveSheet.Paste

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 106

Val: 22

Insertar filas según el valor de una celda y copiar formula de la fila superior

Publicado por Lluís (17 intervenciones) el 31/03/2018 07:01:36

Hola Antoni

Recibido e implementado: ÉXITO TOTAL

Gracias Antoni por estas últimas 4 líneas, Ahora la acción queda completada, ahorrándome un montón de trabajo.

He encontrado muy útiles los comentarios que has añadido en cada sección de la programación de la macro. Así se entiende a la perfección.

¡Muchas gracias por todo este trabajo!

¡Saludos!

Valora esta respuesta

Comentar

Multiples conexiones externas

xlsx dañados