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

   
Vista:

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

Publicado por Lluís (6 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 claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Antoni Masana

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

Publicado por Antoni Masana (829 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 claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

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

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

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

Gracias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

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

Publicado por Lluís (6 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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana

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

Publicado por Antoni Masana (829 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.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

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

Publicado por Lluís (6 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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

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

Publicado por Lluís (6 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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana

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

Publicado por Antoni Masana (829 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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:

1
2
3
4
5
6
7
8
9
10
               ' --- 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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

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

Publicado por Lluís (6 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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Revisar política de publicidad