Recuperar valores de la hoja 1 en la hoja 2

1. No entiendo cómo es que en un balance vas a "suma/resta/multiplicación o división de varios".

2. Tienes en alguna parte de tu archivo, ¿la relación de cuentas que pertenecen a cada renglón de la hoja "Balance"?

Es decir, el renglón M40 "Ajustes periodificación y provisiones", sus cuentas son: "48000", "48200", "48710" y "48500", esta relación la tienes en alguna parte, ¿la puedes conseguir?

Teniendo la relación de cuentas por renglón, puedes ejecutar una macro y que te haga todas las sumas.

3. Tu función DameValor(), solamente te suma una vez, y por lo que veo en tu hoja "DataImport", tienes que sumar varias veces el valor "48000", entonces esa función no nos ayuda, deberás crear otra macro más completa.

4. Una opción es que utilices la fórmula:

=Sumar.Si.Conjunto

Con esa fórmula puedes sumar todos los valores de la hoja "DataImport" considerando varias condiciones.

Con la fórmula sumar, no tienes que dar enter a cada celda, la fórmula se actualiza en automático.

Por ejemplo, pon la siguiente fórmula en la hoja 2 en la fila 68

=SUMAR.SI.CONJUNTO(dataimport!F:F;dataimport!E:E;48200;dataimport!C:C;2012)+SUMAR.SI.CONJUNTO(dataimport!F:F;dataimport!E:E;48000;dataimport!C:C;2012)

Lo que hace la fórmula es sumar los valores de las cuentas 48200 y 48000 del año 2012.

Tienes que poner un sumar. Si. Conjunto por cada cuenta que quieras sumar.

Veo que no me he explicado bien y mis disculpas por ello

Los saldos de las cuentas (columna Acum) son Acumulativos, es decir, El saldo del mes 2 será la suma del saldo del mes1 + el saldo del mes2 (este dato ya viene en los datos importados) y así sucesivamente hasta el mes 12 que sería el total del año. Por lo tanto no se se suman los saldos de los distintos meses por Cta para obtener un total excepto que tengamos mas de una empresa, si queremos una línea con el saldo consolidado de todas, o mas de una delegación por empresa. si lo que queremos es el saldo total de todas las delegaciones por empresa.

Es decir el saldo total del año 2012 de la empresa1 para la 48200 será el siguiente: (el del mes 12 ya que acumula los otros meses)

Emp  Organis Mes  Ano      Cta     Acum

 1      1            12    2012  48200     100     Saldo total año 2012 delegación1

Si tuviésemos mas de una delegación(columna Organis) sumaríamos los saldos de todas las delegaciones de esa empresa 

Emp  Organis Mes  Ano      Cta     Acum

 1      1            12    2012  48200     100     Saldo total año 2012 delegación1

 1      2            12    2012  48200     100     Saldo total año 2012 delegación2

TOTAL SALDO

Emp  Organis Mes  Ano      Cta     Acum

1        1-2        12    2012  48200     200     Saldo total año 2012 empresa1  

Por todo ello se deduce que nunca vamos a sumar saldos mensuales(columna Acum de una cuenta en concreto(columna Cta) a no ser que varíe la Empresa(columna Emp) o la delegación (columna Organis)

Respuestas a sus preguntas:

1. No entiendo cómo es que en un balance vas a "suma/resta/multiplicación o división de varios".

Imaginemos que las ventas totales de la empresa1 por un valor 500 y se compone de los siguiente:

Venta de aceite   Venta de recambios  Venta de acesorios    TOTAL VENTAS

Importe    100                           300                          200                         500

 Cada tipo de venta va a una cta de venta distinta así como a una de coste por ello tenderé que sumar cada línea individual en una de resultado. 

Venta aceite      Venta recambios  Venta de acesorios   TOTAL VENTA

Cta 70000(100)  CtA 70001(300)   Cta 70002(200)        70000+70001+70002(500)

Coste aceite      Coste recambios  Coste acesorios         TOTAL COSTE

Cta 93600(30)    Cta 93601(150)    Cta 93602(100)        93600+93601+93602(300)

TOTAL MARGEN: 500-300= 200 este sería el margen bruto pero habría que restar otros costes indirectos como pudiera ser el transporte,descuentos....con lo cual tendría que restar las cuentas que tuviesen esos saldos del TOTAL VENTA

Si quisiera saber el % que representan las ventas de aceite sobre el total de ventas ya tendría que multiplicar y dividir pero bueno esas operaciones las haría sobre las propias celdas por Ejemplo

VENTAS DE ACEITE   TOTAL VENTAS                      %VENTAS ACEITE

   CELDA E8 (100)   /   CELDA E58 (500)*100 =                   20%

2. Tienes en alguna parte de tu archivo, la relación de cuentas que pertenecen a cada renglón de la hoja "Balance"?

En este momento no las tengo pero no tendría problema en añadirlas puesto que al igual que importo a la hoja Dataimport los saldos también podría traer todas las cuentas (ya analizaría como hacerlo si fuese necesario)

3. Tu función DameValor(), solamente te suma una vez, y por lo que veo en tu hoja "DataImport", tienes que sumar varias veces el valor "48000", entonces esa función no nos ayuda, deberás crear otra macro más completa.

Es que esta función estaba pensada para una única empresa. Es decir sólo se importaban a la hoja dataimport lo saldos de una única empresa con el siguiente formato

Emp Cta. Acum y las cuentas nunca se repetían, como mucho podrían aparecer nuevas ctas.

por lo tanto la función funcionaba a las mil maravillas, pero ahora por otras necesidades tenemos Emp Organis Ano Mes Cta Acum

El problema me surgió porque no se como adaptar mi función de tal forma que le pueda pedir que me devuelva el saldo(columna Acum) de una cta(columna Cta) de una empresa(columna Emp) y delegación(columna Organis) determinadas o bien el total de una empresa y sus delegaciones o el total de todas las empresas.

No es lo mismo para el usuario teclear

DameValor("70000")+ Empresa+Delegacion) o

DameValor("70000") +Empresa+TodasDelegaciones) o

DameValor("70000") + TodasEmpresas

que poner fórmulas y más formulas en todas las celdas.

Por ello he  recurrido a esta buena página de expertos de la que ya tenía referencias y donde estoy aprendiendo un montón de cosas  que desconocía sobre excel.

Espero que con esta explicación le haya aclarado un poco más el tema.                

Ahora estoy totalmente perdido, ya no entiendo qué es lo que necesitas.

Lo que más o menos entiendo, es que quieres hacer sumatorias de la primera hoja en la segunda hoja, ¿es correcto?

¿Podrías enviarme tu archivo y me explicas con colores qué es lo que quieres sumar?

Tienes una función que se llama: DameValor, es necesario que se siga utilizando esa función o puedo enviarte una solución.

Buenas tardes:

Ya he enviado el archivo con las explicaciones solicitadas

Te anexo la macro para crear balances

Sub CrearBalance()
'Por.Dante Amor
    Set h1 = Sheets("Dataimport")
    Set h2 = Sheets("Balance Formato")
    h2.[K1:L1] = h1.[A1]
    h2.[M1:N1] = h1.[B1]
    h2.[O1:P1] = h1.[C1]
    h2.[Q1:R1] = h1.[D1]
    h2.[K2] = ">=" & h2.[H5]
    h2.[L2] = "<=" & h2.[I5]
    h2.[M2] = ">=" & h2.[H8]
    h2.[N2] = "<=" & h2.[I8]
    h2.[O2] = ">=" & h2.[H11]
    h2.[P2] = "<=" & h2.[I11]
    h2.[Q2] = ">=" & h2.[H14]
    h2.[R2] = "<=" & h2.[I14]
    h2.[AA:AF].ClearContents
    h1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]
    '
    For i = 5 To h2.Range("A" & Rows.Count).End(xlUp).Row
        Set b = h2.Range("X:X").Find(h2.Cells(i, "B"), lookat:=xlWhole)
        If Not b Is Nothing Then
            cuentas = Split(h2.Cells(b.Row, "Y"), "+")
            For j = LBound(cuentas) To UBound(cuentas)
                primera = cuentas(j)
                Set r = h2.Columns("AE")
                Set b = r.Find(cuentas(j), lookat:=xlWhole)
                If Not b Is Nothing Then
                    ncell = b.Address
                    Do
                        h2.Cells(i, "E") = h2.Cells(i, "E") + h2.Cells(b.Row, "AF")
                        Set b = r.FindNext(b)
                    Loop While Not b Is Nothing And b.Address <> ncell
                End If
            Next
        End If
    Next
    MsgBox "Balance Terimando. Copia el balance a una nueva hoja."
End Sub

Saludos.Dante Amor

No olvides valorar la respuesta.

Buenos días Sr. Amor:

He estado probando la macro pero no me funciona o es que no hago los pasos correctos para su ejecución.

Detallo los pasos que realizo:

1.- Importo Datos: como bien sabe tengo la consulta sql ligada a la hoja Dataimport

Por lo tanto realizo un primer filtrado de esos datos seleccionando lo que quiero importar, Empresa, delegación, Ejercicio y Mes. En las propiedades de la  definición de la consulta le tengo que actualice y sobreescriba los datos existentes.

2.- Me voy a la hoja Balance Formato y relleno los parámetros  de las cuentas.(sólo informo algunas)

3.- Veo que también tengo que rellenar de nuevo los parámetros de actualización Empresa,Organis,Año,Mes no los recoge de la hoja Dataimport 

4.- Pincho en el botón Crear balance y aunque los datos en las columnas AA:AF

son correctos, no se me actualizan los valores del balance, siguen manteniendo los valores que tenían previamente  

¿Dónde puede estar el problema?

Si lo desea le vuelvo a enviar el archivo.

El cambio que he hecho en el archivo que me ha enviado ha sido modificar en parámetros de cuentas la cuenta 10000 por 11200 y pinchar en el botón crear balance.

El valor no se actualiza para la línea M60 sigue manteniendo -120200 cuando debería ser -138763

Creo que ya he dado con la solución al cambiar esta línea

For i = 5 To h2.Range("A" & Rows.Count).End(xlUp).Row

por esta

For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row

B es  la columna donde se encuentra M60.

Sólo quedaría un detalle, se pueden poner a 0 los valores de la columna Importe antes de realizar el cálculo?

Si no lo hago, es decir pongo a "0" los valores, suma al valor anterior el valor calculado

Te anexo la macro para limpiar la columna "E"

Sub CrearBalance()
'Por.Dante Amor
    Set h1 = Sheets("Dataimport")
    Set h2 = Sheets("Balance Formato")
    h2.[K1:L1] = h1.[A1]
    h2.[M1:N1] = h1.[B1]
    h2.[O1:P1] = h1.[C1]
    h2.[Q1:R1] = h1.[D1]
    h2.[K2] = ">=" & h2.[H5]
    h2.[L2] = "<=" & h2.[I5]
    h2.[M2] = ">=" & h2.[H8]
    h2.[N2] = "<=" & h2.[I8]
    h2.[O2] = ">=" & h2.[H11]
    h2.[P2] = "<=" & h2.[I11]
    h2.[Q2] = ">=" & h2.[H14]
    h2.[R2] = "<=" & h2.[I14]
    h2.[AA:AF].ClearContents
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        If h2.Cells(i, "E") <> "Importe" Then
            If Not h2.Cells(i, "E").HasFormula Then
                h2.Cells(i, "E") = 0
            End If
        End If
    Next
    h1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]
    '
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        Set b = h2.Range("X:X").Find(h2.Cells(i, "B"), lookat:=xlWhole)
        If Not b Is Nothing Then
            cuentas = Split(h2.Cells(b.Row, "Y"), "+")
            For j = LBound(cuentas) To UBound(cuentas)
                primera = cuentas(j)
                Set r = h2.Columns("AE")
                Set b = r.Find(cuentas(j), lookat:=xlWhole)
                If Not b Is Nothing Then
                    ncell = b.Address
                    Do
                        h2.Cells(i, "E") = h2.Cells(i, "E") + h2.Cells(b.Row, "AF")
                        Set b = r.FindNext(b)
                    Loop While Not b Is Nothing And b.Address <> ncell
                End If
            Next
        End If
    Next
    MsgBox "Balance Terimando. Copia el balance a una nueva hoja."
End Sub

Buenos días:

Hasta aquí, todo OK, calcula correctamente.

El problema es que si vuelvo a importar datos a través de la conexión a sql y vuelvo a informar en la hoja Balance Formato el filtro que quiero aplicar en parámetros de actualización las columnas AA:AF se quedan en blanco, o sea no me recupera de la hoja dataimport.

¿Alguna sugerencia?

Buenos días:

Por más que intento adivinar de dónde puede venir el error no consigo localizarlo

¿Podría ser que la actualización machaque algo que impida la recuperación de datos desde el botón Crear Balance?

Le envío imágenes de como queda la hoja Dataimport una vez actualizada y como queda la hoja Balance Formato al pinchar en "Crear Balance".

En la definición de la consulta tengo marcado que actualice al abrir y quitar los datos antes de guardar.

Espero sus comentarios, mientras seguiré intentando ver donde puede estar el problema.

cambia esta l{inea

h1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]

por estas

u = h1.Range("A" & Rows.Count).End(xlUp).Row
h1.Range("A1:F" & u).AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]

A partir del momento que actualizo Dataimport, relleno parámetros en Balance Formato pierde los datos al pinchar en el botón "Crear Balance".

Adjunto código completo por si he cometido algún error

Sub CrearBalance()
'Por.Dante Amor
    Set h1 = Sheets("Dataimport")
    Set h2 = Sheets("Balance Formato")
    h2.[K1:L1] = h1.[A1]
    h2.[M1:N1] = h1.[B1]
    h2.[O1:P1] = h1.[C1]
    h2.[Q1:R1] = h1.[D1]
    h2.[K2] = ">=" & h2.[H5]
    h2.[L2] = "<=" & h2.[I5]
    h2.[M2] = ">=" & h2.[H8]
    h2.[N2] = "<=" & h2.[I8]
    h2.[O2] = ">=" & h2.[H11]
    h2.[P2] = "<=" & h2.[I11]
    h2.[Q2] = ">=" & h2.[H14]
    h2.[R2] = "<=" & h2.[I14]
    h2.[AA:AF].ClearContents
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        If h2.Cells(i, "E") <> "Importe" Then
            If Not h2.Cells(i, "E").HasFormula Then
                h2.Cells(i, "E") = 0
            End If
        End If
    Next
    u = h1.Range("A" & Rows.Count).End(xlUp).Row
    h1.Range("A1:F" & u).AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]
    '
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        Set b = h2.Range("X:X").Find(h2.Cells(i, "B"), lookat:=xlWhole)
        If Not b Is Nothing Then
            cuentas = Split(h2.Cells(b.Row, "Y"), "+")
            For j = LBound(cuentas) To UBound(cuentas)
                primera = cuentas(j)
                Set r = h2.Columns("AE")
                Set b = r.Find(cuentas(j), lookat:=xlWhole)
                If Not b Is Nothing Then
                    ncell = b.Address
                    Do
                        h2.Cells(i, "E") = h2.Cells(i, "E") + h2.Cells(b.Row, "AF")
                        Set b = r.FindNext(b)
                    Loop While Not b Is Nothing And b.Address <> ncell
                End If
            Next
        End If
    Next
    MsgBox "Balance Terimando. Copia el balance a una nueva hoja."
End Sub

Tal vez los datos que estás poniendo como parámetros no corresponden a ninguno de los registros.

Prueba solamente con un año, por ejemplo.

Ya me acordé, tus datos en la hoja datimort son texto y la macro busca números.

Prueba con esto

Sub CrearBalance()
'Por.Dante Amor
    Set h1 = Sheets("Dataimport")
    Set h2 = Sheets("Balance Formato")
    h2.[K1:L1] = h1.[A1]
    h2.[M1:N1] = h1.[B1]
    h2.[O1:P1] = h1.[C1]
    h2.[Q1:R1] = h1.[D1]
    h2.[K2] = ">=" & h2.[H5]
    h2.[L2] = "<=" & h2.[I5]
    h2.[M2] = ">=" & h2.[H8]
    h2.[N2] = "<=" & h2.[I8]
    h2.[O2] = ">=" & h2.[H11]
    h2.[P2] = "<=" & h2.[I11]
    h2.[Q2] = ">=" & h2.[H14]
    h2.[R2] = "<=" & h2.[I14]
    h2.[AA:AF].ClearContents
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        If h2.Cells(i, "E") <> "Importe" Then
            If Not h2.Cells(i, "E").HasFormula Then
                h2.Cells(i, "E") = 0
            End If
        End If
    Next
    h2.[A1] = 1
    h2.[A1].Copy
    h1.Range("A1:F" & u).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    u = h1.Range("A" & Rows.Count).End(xlUp).Row
    h1.Range("A1:F" & u).AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]
    '
    For i = 5 To h2.Range("B" & Rows.Count).End(xlUp).Row
        Set b = h2.Range("X:X").Find(h2.Cells(i, "B"), lookat:=xlWhole)
        If Not b Is Nothing Then
            cuentas = Split(h2.Cells(b.Row, "Y"), "+")
            For j = LBound(cuentas) To UBound(cuentas)
                primera = cuentas(j)
                Set r = h2.Columns("AE")
                Set b = r.Find(cuentas(j), lookat:=xlWhole)
                If Not b Is Nothing Then
                    ncell = b.Address
                    Do
                        h2.Cells(i, "E") = h2.Cells(i, "E") + h2.Cells(b.Row, "AF")
                        Set b = r.FindNext(b)
                    Loop While Not b Is Nothing And b.Address <> ncell
                End If
            Next
        End If
    Next
    MsgBox "Balance Terimando. Copia el balance a una nueva hoja."
End Sub

Buenas tardes:

He comprobado los datos que llegan de sql y todas las celdas tienen formato "general"

He reemplazado con la macro nueva y me da el error:

Se ha producido error 1004 en tiempo de ejecución

Error definido por la aplicación o el objeto

Si le doy a depurar se pone en amarillo la siguiente línea:

h1.Range("A1:F" & u).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Después de esto

h2.[A1].Copy

Pon

h1.select

después de esto

h1.Range("A1:F" & u).AdvancedFilter xlFilterCopy, h2.[K1:R2], h2.[AA1:AF1]

pon 

h2.select

Ahora me he perdido 

En la anterior consulta esa línea era 

h1.Range("A1:F" & u).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

¿Prefiere que le envíe el archivo tal cual está para que lo compruebe? 

Esto es lo que me aparece:

Mis disculpas pero no lo había copiado bien, lo he puesto donde me decía pero sigue dando error

Adjunto imagen

Buenas tardes:

He hecho lo siguiente:

La hoja Dataimport la he dejado con los datos que tenía.

He creado una nueva hoja y la he llamado Datos Iniciales

He lanzado la actualización de datos de SQL en esa hoja

He copiado los datos del renglón A2 : marcandolos y botón derecho "copiar"

He pegado en la Hoja Dataimport y he pegado en A2

Me voy a la Hoja Balance Formato - Relleno parámetros y pincho en Crear Balance

y no recupera la línea pegada en Dataimport, pero sí las restantes

Yo no lo entiendo por más vueltas que le doy.

Solamente revisa que los datos de origen sea números y que estén en un formato numérico, ya que la macro hace el filtro de rangos de 1 a 1, y no puede hacer un filtro de números si tienes texto. En el archivo que me enviaste los datos eran textos, cambiar los datos por número y haz la prueba.

A mí me funciona la macro, las pruebas que hiciste con el archivo que te envié, también funcionaron, el problema está que cuando cargas nueva información viene como texto, solamente cámbiala a número.

Envíame un nuevo archivo con el que estás probando y reviso la macro para que haga la conversión de texto a número.

Recuerda poner tu nombre "antonio ramos" en el asunto del correo

Enviado