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

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan (15 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 clara

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

Responder

Pos: 2

Val: 4.908

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (2478 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 clara

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

Comentar

Pos: 294

Val: 9

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan Francisco (42 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

DIRECTIVA-10-MAQUETA.rar(1,7 MB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Buscar y copiar datos de varias hojas a un Libro

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

Comentar

Buscar y copiar datos de varias hojas a un Libro

Publicado por JUAN FRANCISCO (15 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

Comentar

Pos: 2

Val: 4.908

Buscar y copiar datos de varias hojas a un Libro

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

Comentar

Buscar y copiar datos de varias hojas a un Libro

Publicado por Juan Francisco (15 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

Comentar

Pos: 2

Val: 4.908

Buscar y copiar datos de varias hojas a un Libro

Publicado por Antoni Masana (2478 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:

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

Comentar

Pos: 2

Val: 4.908

Buscar y copiar datos de varias hojas a un Libro

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

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

Comentar

Buscar y copiar datos de varias hojas a un Libro

Publicado por juan (15 intervenciones) el 07/06/2021 22:40:22

Gracias Antonny lo voy a probay y te comento luego

Valora esta respuesta

Comentar

Formulario busqueda

Traer datos en base a la fecha de HOY