Excel - Buscar y copiar datos de varias hojas a un Libro

 
Vista:

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan (4 intervenciones) el 28/05/2021 15:48:17
Buenos días, tengo un archivo en excel que consolido información estadística compuesto de 29 hojas y la gran mayoría de estas hojas tienen celdas que llenar desde la K9..... hasta la SZ..... aproximadamente, otras menos y cada unidad llena en donde le corresponde llenar siendo estas unidades 53, en estas hojas hay sumatorias dentro de ese rango de celdas.

Necesito consolidar toda la información estadística de las 53 unidades que remiten pero no deseo estar copiando y pegando en cada hoja de mi archivo manualmente ya que esto resta mucho tiempo aparte de que hay que verificar los errores para luego remitir finalmente corregida, en un plazo máximo de un día y esto me genera inconvenientes.

Que macro se implementaría para que me copie de cada hoja de los libros que remiten las unidades y que precisamente son iguales al libro que consolido la información, pero que solo copie la fila que le corresponde y claro si es que tiene información si no que no copie nada. (si se pudiera evitar que copie las celdas que contienen formulas, si no no importa)

Ejemplo:

campo a identificar que remite lambayeque campo hoja del libro que consolida la información
k9 k9
lambayeque 1 1 1 2 34 1 lambayeque
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
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (1790 intervenciones) el 28/05/2021 17:22:34
Si se puede hacer una macro que consolide la información.
Para hacerte la macro hace falta que subas el o los libros y un ejemplo y una explicación detallada de lo que tiene que hacer.

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
sin imagen de perfil
Val: 9
Ha aumentado su posición en 5 puestos en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan Francisco (41 intervenciones) el 28/05/2021 19:10:42
Ok Antony aquí el libro, cualquier duda me comentas, gracias por tu aporte. La clave para desbloquear las hojas es 2021 si en caso lo necesitas, pero están desbloqueadas.
Si se puede no tocar las celdas que tiene formulas bien, si no así nomas, gracias nuevamente
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
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (1790 intervenciones) el 31/05/2021 15:39:33
He visto el libro y no entiendo que es lo que necesitas hacer y con el ejemplo anterior no entiendo nada de lo que dices.

campo a identificar que remite lambayeque campo hoja del libro que consolida la información
k9 k9
lambayeque 1 1 1 2 34 1 lambayeque

Si no explicas que copias y donde lo pegas no puedo ayudarte.

Ten en cuento que yo no se para que son estas hojas que datos tienen ni para que sirven y mucho menos que copias y donde lo pegas, la primera no me interesa, en la segunda puedo ayudarte con el COPY-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

Buscar y copiar datos de varias hojas a un Libro

Publicado por JUAN FRANCISCO (4 intervenciones) el 31/05/2021 23:23:54
Ok, antony, mira eso que escribi era una suposicion pero te explico:
Este mismo formato lo llenan todas las unidades, cada una en su denominacion que le corresponde, ejemplo:

COMISARIA CESAR LLATAS CASTRO
COMISARIA DEL NORTE
COMISARÍA CAPITAN FAP JOSE ABELARDO QUIÑONEZ GONZALES
COMISARIA DE LA FAMILIA
COMISARIA CAMPODÓNICO
COMISARÍA EL PORVENIR
COMISARIA PAMPAGRANDE
....

ellas llenaran en su respectiva fila hasta el final en todas las hojas su información y nos la remiten para que nosotros peguemos en su respectiva fila de cada hoja la informacion que nos remitieron.

OJO. La hoja CONTROL V1, nadie la toca solo es para que verifiquen posibles errores, en esa hoja nadie llena nada y el macro que crees no lo comprometas con esa hoja, esa pasala por alto.

Asimismo, consigne desde la celda K9 por que no me percate que la F-1, F-2, F-5 y otras mas comienzan desde la M9, y otras hojas si comienzan desde la K9, L9 etc.

Cada una de las hojas en su rango de celdas existen unas autosumas de los grupos o clases que consolidan la información como lo habrás notado.

Es por ello que necesito que la macro me permita copiar de todos los libros que envian las unidades usuarias con diferentes nombres MENOS DE LA HOJA CONTROL V1, y pegarmos en mi libro que consolido la información.

Para ello a mi parecer que busque por unidad usuaria y pegue la información de la hoja de la Unidad Usuaria y de su respectiva fila hacia la hoja correspondiente del libro en donde estoy consolidando la información y si no tiene información que no pegue nada.

Gracias Antony espero haya sido un poco mas claro.





Solo deseo copiar
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
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (1790 intervenciones) el 01/06/2021 14:39:34
Vamos a ver si entiendo el funcionamiento:

Tenemos un libro a que llamare MAQUETA y 53 libros, uno por cada una de las comisarias o dependencias con los datos de estas, cada uno de estos libros tendrá una sola fila de datos por hoja.

Procedimiento: copiar cada una de las filas de los 53 libros en el libro MAQUETA en la fila correspondiente.

Ahora las preguntas:
- ¿El formato de los 54 libros es el mismo?
- ¿Donde debe localizar la macro los 53 libros?
- ¿La MAQUETA está en el mismo directorio que los otros 53 libros?
- ¿Hay otros libros?
- Las hojas a copiar son todas las que el nombre empieza por F- (He contado 28), la hoja MEDIDAS DE ROTECCION ¿Esta tambien?

Si me pudieses enviar archivos de para las pruebas me iría muy bien. Si pueden ser las 53 mejor.

Si no quieres subirlas al foro puedes enviarlas a mi correo: [email protected]

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

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan Francisco (4 intervenciones) el 01/06/2021 16:08:49
Si, el formato de los 53 libros es el mismo.

La macro debe copiar la información de todas las hojas de los libros de las unidades al libro MAQUETA, de esta forma se copiara toda la información de las hojas del libro unidad a la hoja MAQUETA en donde voy a consolidar la información.

Si, la maqueta de los 52 libros esta en el mismo directorio

Las hojas a copiar son todas las que comienzan con F-1, MEDIDAS DE PROTECCION, menos la hoja que tiene el nombre de (CONTROL V1)

ok remito a tu correo los archivos para prueba.

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
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (1790 intervenciones) el 02/06/2021 15:39:54
En los ficheros que me has enviado veo que no tienen las hojas F-48, F-49 y F-51, si no las tiene las ignora y sigue con la siguiente.

El problema es la hoja MEDIDAS DE CONTROL que esta en el primer libero no existe en los que has enviados.
En cambio existe la hoja F-45 Medidas de Proteccion y su formato es diferente, no coinciden las filas y ni las columnas.

Cuando abre uno de estos archivos:

1
2
3
4
5
6
7
8
9
CUADRO DIRECTIVA 10 ABRIL CPNP TUCUME.xlsx
CUADRO DIRECTIVA10_CPNP_MORROPE_ABRIL_2021.xlsx
CUADROS_DIRECTIVA 10 CPNP_MOTIPE_ABRIL_2021.xlsx
CUADROS_DIRECTIVA_10_ABRIL_2021_LAMBAYEQUE.xlsx
DIRECTIV_10 com_monsefu_ABRIL_2021.xlsx
DIRECTIVA 10 ABRIL DEL AÑO 2021 CPNP Cayalti.xlsx
DIRECTIVA 10 MES ABRIL 2021_CPNP PATAPO.xlsx
DIRECTIVA_13_ABRIL_2021_CPNP PAMPA GRANDE.xlsx
FORMATO_DIRECTIVA_10_CPNP_LA_VICTTORIA_ABRIL_2021.xlsx

¿Que fila debe copiar?

Lo voy a seguir el siguiente criterio:

Definición para seguir la explicación:
Orig Libro del que se leen los datos
Dest Libro maqueta o informe resumen donde se escriben los datos
Abre el

1. Abro el Orig.
2. Con la tabla de hojas a tratar.
2.1. busco si existe la hoja en el Orig.
2.2. en la columna I y desde la fila 9 hasta el Total o celda vacía el el Orig le la comisaria
2.3. Verifico que exista en el libro Dest.
2.4. Si existe
2.4.1 Recorro la fila para ver si tiene datos en el libro Orig
2.4.2 Si hay datos copia la fila sin las celdas de fórmulas del libro Orig al libro Dest
3. Una vez recorridas todas las hojas cierra el libro Orig
4. Vuelve al punto 1

Ignora si el libro a tratar es el mismo que el que esta ejecutando la macro..

Veo que en el primer libro hay 53 comisarias y en las hojas he contado hasta 103 departamentos.
Hay alguna fórmula redundante.

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
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (1790 intervenciones) el 02/06/2021 19:55:51
Esta es la macro.

Pruebala porque con los pocos datos que hay no puedo verificar si funciona. Intentaré rellenar las celdas con datos ficticios y hare alguna prueba mas.

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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
Option Explicit
 
' </> --------------------------------------------------------------------- </>
' </> ---&---  Busca los diferenes libros y me descarto
' </> --------------------------------------------------------------------- </>
 
Sub Leer()
    Dim Yo As String, Ruta As String, File As String
 
    Yo = ActiveSheet.Parent.Name
 
    Ruta = "c:\TMP\MAQUETA\"
    File = Dir(Ruta + "*.XL*")
 
    While File <> ""
        If File <> Yo Then Call Tratar_Fichero(Yo, Ruta, File)
        File = Dir()
    Wend
End Sub
 
' </> --------------------------------------------------------------------- </>
' </> ---&---   Abre el fichero y busca si existe cada una de las hojas
' </> --------------------------------------------------------------------- </>
 
Sub Tratar_Fichero(Yo, Ruta, File)
    Dim Datos(29) As String, a As Byte, b As Byte, _
        Desde As Integer, Hasta As Integer, Hoja As String
 
    Datos(1) = "12:517:F-1 ": Datos(10) = "11:120:F-9 ": Datos(19) = "11: 35:F-29"
    Datos(2) = "11:121:F-1A": Datos(11) = "11: 19:F-13": Datos(20) = "11: 40:F-30"
    Datos(3) = "11:530:F2  ": Datos(12) = "11: 17:F-14": Datos(21) = "11: 77:F-33"
    Datos(4) = "11:165:F-2A": Datos(13) = "11:519:F21 ": Datos(22) = "11: 51:F-34"
    Datos(5) = "11: 62:F-3 ": Datos(14) = "11:155:F-23": Datos(23) = "11: 96:F-40"
    Datos(6) = "11: 70:F-4 ": Datos(15) = "11:140:F24 ": Datos(24) = "11: 27:F-41"
    Datos(7) = "11:120:F-5 ": Datos(16) = "11:266:F-25": Datos(25) = "11: 86:F-44"
    Datos(8) = "11:123:F-6 ": Datos(17) = "11:193:F-26"
    Datos(9) = "11: 63:F-7 ": Datos(18) = "11: 44:F-28"
 
    Datos(26) = " 2: 22:MEDIDAS DE PROTECCION"
    Datos(27) = " 8: 10:F-48"
    Datos(28) = " 8: 12:F-49"
    Datos(29) = " 8:  9:F-51"
 
 
    Workbooks.Open Filename:=Ruta & File, Origin:=xlWindows
 
    For a = 5 To 5 ' 1 To 29
        Desde = Val(Mid$(Datos(a), 1, 2))
        Hasta = Val(Mid$(Datos(a), 4, 3))
        Hoja = RTrim(Mid$(Datos(a), 8))
 
        For b = 1 To Sheets.Count
            If Sheets(b).Name = Hoja Then
                'Windows(Yo).Sheets(Hoja).Select
                Call Dependencias(Yo, Hoja, Desde, Hasta)
            End If
        Next
    Next
 
    Windows(File).Close
End Sub
 
' </> --------------------------------------------------------------------- </>
' </> ---&---   Trata las dependencias. Verifica que exista en el destino
' </> ---&---   O sea que la tenga en mi libro.
' </> ---&---
' </> ---&---   Verifica si hay datos en la fila y si hay solo copia las
' </> ---&---   celdas con datos ignora las formulas.
' </> ---&---
' </> ---&---   Se supone que las columnas de los dos libros coinciden.
' </> --------------------------------------------------------------------- </>
 
Sub Dependencias(Yo, Hoja, Desde, Hasta)
    Dim Fila As Integer, Hay_Datos As Boolean, Col As Integer, _
        Dest As Integer
 
    Sheets(Hoja).Select
 
    Fila = 9
    While Cells(Fila, "I") <> ""
        Dest = Existe_Origen(Yo, Cells(Fila, "i"))
        If Dest > 0 Then
            Hay_Datos = False
            For Col = Desde To Hasta
                If Cells(Fila, Col).Formula = "" Then
                    If Cells(Fila, Col) <> Empty Then
                       Hay_Datos = True
                       Exit For
                    End If
                End If
            Next
 
            If Hay_Datos Then
                For Col = Desde To Hasta
                    If Cells(Fila, Col).Formula = "" Then
                        If Cells(Fila, Col) <> Empty Then
                            Windows(Yo).Sheets(Hoja).Cells(Dest, Col) = Cells(Fila, Col)
                        End If
                    End If
                Next
            End If
        End If
        Fila = Fila + 1
    Wend
End Sub
 
' </> --------------------------------------------------------------------- </>
' </> ---&---   Verifica y devuelve la linea de la dependencia en el libro
' </> ---&---   origen.
' </> --------------------------------------------------------------------- </>
 
Function Existe_Origen(Yo, Texto)
    Dim Fila As Integer
 
    Existe_Origen = 0
    With Windows(Yo).ActiveSheet
        Fila = 9
        While .Cells(Fila, "I") <> Texto And .Cells(Fila, "I") <> ""
            Fila = Fila + 1
        Wend
        If .Cells(Fila, "I") = Texto Then Existe_Origen = Fila
    End With
End Function
 
' </> --------------------------------------------------------------------- </>
' </> ---&---                                                       ---&--- </>
' </> ---&---   FIN MACRO                                           ---&--- </>
' </> ---&---                                                       ---&--- </>
' </> --------------------------------------------------------------------- </>


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

Buscar y copiar datos de varias hojas a un Libro

Publicado por juan (4 intervenciones) el 07/06/2021 22:40:22
Gracias Antonny lo voy a probay y te comento luego
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