IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

somme de toutes les sheet [XL-2010]


Sujet :

Macros et VBA Excel

  1. #1
    Expert confirmé
    Homme Profil pro
    Responsable des études
    Inscrit en
    Juillet 2014
    Messages
    2 680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aude (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Santé

    Informations forums :
    Inscription : Juillet 2014
    Messages : 2 680
    Par défaut somme de toutes les sheet
    Bonjour,

    J'ai un fichier avec un nombre de sheet variable (1 par client selectionné dans un autre fichier), chaque sheet contient le même tableau.
    J'aimerais rajouter une sheet "total" avec la somme de tous mes autres tableaux.
    Existe-t-il une formule pour avoir total!A1 = Somme (A1 de toutes les autres sheets).
    Petite précision les sheets ont le nom du client et pas Sheet1 2 3...

    Merci d'avance

  2. #2
    Membre émérite
    Homme Profil pro
    Directeur
    Inscrit en
    Avril 2003
    Messages
    724
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Directeur

    Informations forums :
    Inscription : Avril 2003
    Messages : 724
    Par défaut
    Salut,

    as tu regardé la commande Consolider, dans l'onglet Données?

    Cordialement,

  3. #3
    Membre Expert Avatar de antonysansh
    Homme Profil pro
    Chargé d'études RH
    Inscrit en
    Mai 2014
    Messages
    1 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé d'études RH
    Secteur : Finance

    Informations forums :
    Inscription : Mai 2014
    Messages : 1 115
    Par défaut
    Bonjour halaster08,

    Une solution sans VBA :

    En A1 de la feuille Total : =SOMME(Feuil1:Feuil10!A1) Feuil1 est le nom de ta première feuille et Feuil10 est le nom de ta dernière feuille. (les feuilles doivent se suivre)

  4. #4
    Expert confirmé
    Homme Profil pro
    Responsable des études
    Inscrit en
    Juillet 2014
    Messages
    2 680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aude (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Santé

    Informations forums :
    Inscription : Juillet 2014
    Messages : 2 680
    Par défaut
    Bonjour halaster08,

    Une solution sans VBA :

    En A1 de la feuille Total : =SOMME(Feuil1:Feuil10!A1) Feuil1 est le nom de ta première feuille et Feuil10 est le nom de ta dernière feuille. (les feuilles doivent se suivre)
    Merci mais ça je sais le faire
    Si je poste dans la section VBA c'est pour automatiser ça a la création du fichier
    Et je le répète le nombres de feuille est variable et ne s'appelle plus Sheet1 .. SheetX d'où mon problème
    J'aimerais sommer sur toutes les feuilles sans utiliser leur noms (je peut utiliser le nombre par contre mais je ne vois pas comment)

    Salut,

    as tu regardé la commande Consolider, dans l'onglet Données?

    Cordialement,
    Non je ne l'avais pas fait, maintenant oui, mais en la passant à l'enregistreur de macro je vois qu'il utilise le nom de la feuille et ça me pose problème pour l'automatisation

    Merci de vos réponses

  5. #5
    Membre émérite
    Homme Profil pro
    Directeur
    Inscrit en
    Avril 2003
    Messages
    724
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Directeur

    Informations forums :
    Inscription : Avril 2003
    Messages : 724
    Par défaut
    Salut,

    Utilises une boucle For...Each...Next pour parcourir toutes le feuilles:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    Sub bouclerFeuilles()
        Dim sh As Worksheet
     
        For Each sh In ThisWorkbook.Worksheets
            ' fais ici ce que tu veux sur chaque sh
        Next sh
     
    End Sub

  6. #6
    Expert confirmé
    Homme Profil pro
    Responsable des études
    Inscrit en
    Juillet 2014
    Messages
    2 680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aude (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Santé

    Informations forums :
    Inscription : Juillet 2014
    Messages : 2 680
    Par défaut
    Merci, j'y avais pas pensé, c'est exactement ce qu'il me fallait.

    Voici le code final au cas où qqn serait intéressé:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    Sub Macro2()
    Dim sh As Worksheet
    For i = 7 To 23
    For j = 3 To 15
        For Each sh In ThisWorkbook.Worksheets
     
            If Not (sh.Name = "Total") Then Worksheets("Total").Cells(i, j).Value = Worksheets("Total").Cells(i, j).Value + Worksheets(sh.Name).Cells(i, j).Value
     
        Next sh
    Next j
    Next i
     
    End Sub

  7. #7
    Membre émérite
    Homme Profil pro
    Directeur
    Inscrit en
    Avril 2003
    Messages
    724
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Directeur

    Informations forums :
    Inscription : Avril 2003
    Messages : 724
    Par défaut
    Super!

    Après passage au service contrôle qualité du logiciel, ça donne ceci:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
     
    Sub Macro2()
        ' variables objet
        Dim sh As Worksheet
        Dim shTotal As Worksheet
        ' variables simples
        Dim i As Integer, j As Integer
     
        ' init
        Set shTotal = Worksheets("Total")
        ' boucle
        For i = 7 To 23
            For j = 3 To 15
                For Each sh In ThisWorkbook.Worksheets
                    If Not (sh.Name = "Total") Then
                        shTotal.Cells(i, j).Value = shTotal.Cells(i, j).Value + shTotal.Cells(i, j).Value
                    End If
                Next sh
            Next j
        Next i
    End Sub

  8. #8
    Membre Expert Avatar de antonysansh
    Homme Profil pro
    Chargé d'études RH
    Inscrit en
    Mai 2014
    Messages
    1 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé d'études RH
    Secteur : Finance

    Informations forums :
    Inscription : Mai 2014
    Messages : 1 115
    Par défaut
    Citation Envoyé par Moi
    Bonjour halaster08,

    Une solution sans VBA :

    En A1 de la feuille Total : =SOMME(Feuil1:Feuil10!A1) Feuil1 est le nom de ta première feuille et Feuil10 est le nom de ta dernière feuille. (les feuilles doivent se suivre)
    Citation Envoyé par halaster08 Voir le message
    Merci mais ça je sais le faire
    Si je poste dans la section VBA c'est pour automatiser ça a la création du fichier
    Et je le répète le nombres de feuille est variable et ne s'appelle plus Sheet1 .. SheetX d'où mon problème
    J'aimerais sommer sur toutes les feuilles sans utiliser leur noms (je peut utiliser le nombre par contre mais je ne vois pas comment)
    Tu sais le faire, ok. Moi aussi et ça colle à ta problématique.

    • Avec cette formule, tu peux insérer des feuilles et tous ira bien. A condition d'insérer entre la première et la dernière feuille de la collection de feuilles.
    • Si tu changes le nom de la première feuille (ou de la dernière), la formule s'adapte.


    Concernant les noms des feuilles désolé d'avoir utilisé Feuil1, Feuil2, ... Je n'ai pas réussi à deviner le nom de tes feuilles.


    Apres si tu veux vraiment du VBA :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    Sub MacroInutille()
        ThisWorkbook.Sheets("Total").Range("A2").FormulaR1C1 = "=SUM(PremiereFeuille:DerniereFeuille!RC)"
    End Sub
    Mais tu dois surement savoir le faire.

    PS : Je sais qu'aucune de tes feuilles ne s'appellent PremiereFeuille ou DerniereFeuille.

  9. #9
    Membre Expert Avatar de antonysansh
    Homme Profil pro
    Chargé d'études RH
    Inscrit en
    Mai 2014
    Messages
    1 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé d'études RH
    Secteur : Finance

    Informations forums :
    Inscription : Mai 2014
    Messages : 1 115
    Par défaut
    Plus sérieusement et pour optimiser le code, il est préférable de passer par des tableaux.
    Cells est un objet bien plus lourd que le simple contenu d'un tableau

    Tu peux modifier le code ainsi :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
     Sub Macro2()
        ' variables objet
        Dim shTotal As Worksheet, sh As Worksheet, tTotal(), tTemp()
        ' variables simples
        Dim i&, j&, Plage$
     
        ' init
        Set shTotal = ThisWorkbook.Worksheets("Total")
        Plage = "C7:O23"
        tTotal = shTotal.Range(Plage)
     
        ' boucle
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name = shTotal.Name Then GoTo FeuilleSuivante
                tTemp = sh.Range(Plage)
                For i = 1 To UBound(tTotal, 1)
                    For j = 1 To UBound(tTotal, 2)
                        tTotal(i, j) = tTotal(i, j) + tTemp(i, j)
                    Next j
                Next i
    FeuilleSuivante:
        Next sh
        shTotal.Range(Plage) = tTotal
    Fin:
        Set shTotal = Nothing
    End Sub

  10. #10
    Expert confirmé
    Homme Profil pro
    Responsable des études
    Inscrit en
    Juillet 2014
    Messages
    2 680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aude (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Santé

    Informations forums :
    Inscription : Juillet 2014
    Messages : 2 680
    Par défaut
    Super!

    Après passage au service contrôle qualité du logiciel, ça donne ceci:
    Merci c'est vraiment sympa d'avoir fait le boulot chiant à ma place, c'est une mauvaise habitude que j'ai prise de le faire seulement à la fin ou quand du temps.
    edit: par contre tu as glissé une erreur dans le calcul, il y a deux fois shTotal

    Tu sais le faire, ok. Moi aussi et ça colle à ta problématique.
    Non, justement ça ne colle pas du tout à ma problématique, mais c'est probablement moi qui me suis mal exprimé.
    Je souhaite que la feuille total se remplisse automatiquement après la création des autres feuilles dont le nombre et le nom vont varier
    Donc je ne connais pas le nom des feuilles, donc je ne peut pas utiliser leur nom, donc que tu l'appelle sheet1 ou premierefeuille ça ne résout en rien le problème


    Plus sérieusement et pour optimiser le code, il est préférable de passer par des tableaux.
    Cells est un objet bien plus lourd que le simple contenu d'un tableau
    Merci, je n'utilise VBA que de manière occasionelle et je n'ai encore jamais utilisé de tableaux, je vais étudier ça.


    Merci encore pour vos réponses.

    edit: il y a une chose que je ne comprend pas du tout
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    If sh.Name = shTotal.Name Then GoTo FeuilleSuivante
    c'est écrit en bon français pas défini avant (ou alors je dois changer de lunette) et pourtant ça a l'air de marché. Mais ça me perturbe
    La sheet total est toujours la dernière, il n'y a donc pas de suivant ça ne risque pas de générer d'erreur?

    Dernière chose, pourquoi "&" après la déclaration de i,j et plage?

  11. #11
    Membre Expert Avatar de antonysansh
    Homme Profil pro
    Chargé d'études RH
    Inscrit en
    Mai 2014
    Messages
    1 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé d'études RH
    Secteur : Finance

    Informations forums :
    Inscription : Mai 2014
    Messages : 1 115
    Par défaut
    c'est écrit en bon français pas défini avant (ou alors je dois changer de lunette) et pourtant ça a l'air de marché. Mais ça me perturbe
    La sheet total est toujours la dernière, il n'y a donc pas de suivant ça ne risque pas de générer d'erreur?
    Dans mon code, Fin: et FeuilleSuivante: sont des étiquettes.
    Elles servent à naviguer dans les lignes de code. Elles sont souvent utilisées pour la gestion d'erreurs.
    Dans mon cas je fais Goto FeuilleSuivante si la feuille n'est pas à traiter (feuille Total) et je place l'étiquette de sorte à passer à la feuille suivante (le Next du For each sh in ...)

    Donc pas de risque d'erreurs liées à ça car si plus de feuille à parcourir on sort de la boucle.
    Concrètement je ne fais que sauter les lignes qui ne doivent pas être exécutées.

    Le Fin: n'est pas utile mais je le place pour indiquer que la suite du code ne fait plus rien d'utile si ce n'est libérer les variables (Set shTotal = Nothing)
    Tu peux aussi remarque que je ne libère pas sh, c'est parce que la boucle For each s'en charge. En fait le next de la boucle for each libère automatiquement la variable de boucle si tous les éléments ont été bouclés.




    Dernière chose, pourquoi "&" après la déclaration de i,j et plage?
    Dim i&
    et
    Dim i As Long
    font exactement la même chose.

    Idem pour Dim Plage$ qui est la même chose que Dim Plage As String
    Suffixe Type de données
    % Integer
    & Long
    ! Single
    # Double
    @ Currency
    $ String

  12. #12
    Expert confirmé
    Homme Profil pro
    Responsable des études
    Inscrit en
    Juillet 2014
    Messages
    2 680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Aude (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable des études
    Secteur : Santé

    Informations forums :
    Inscription : Juillet 2014
    Messages : 2 680
    Par défaut
    Merci pour toutes ces explications.

  13. #13
    Membre Expert Avatar de antonysansh
    Homme Profil pro
    Chargé d'études RH
    Inscrit en
    Mai 2014
    Messages
    1 115
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : France, Nord (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Chargé d'études RH
    Secteur : Finance

    Informations forums :
    Inscription : Mai 2014
    Messages : 1 115
    Par défaut Déclaration de variables
    Je vais te montrer 3 façons de déclarer tes variables simples.

    • La façon la plus explicite :

    C'est la façon la plus claire et la plus commune.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    Sub InfoVariables1()
        Dim Ligne As Long, Colonne As Integer, Chaine As String
            MsgBox TypeName(Ligne)
            MsgBox TypeName(Colonne)
            MsgBox TypeName(Chaine)
    End Sub
    • Utilisation des suffixes :

    C'est la méthodes des fainéants :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    Sub InfoVariables2()
        Dim Ligne&, Colonne%, Chaine$
            MsgBox TypeName(Ligne)
            MsgBox TypeName(Colonne)
            MsgBox TypeName(Chaine)
    End Sub
    Voir Tableau précédent Post.

    • Utilisation des Def en tête de module :

    Très peu connue et donc peu utilisée, mais très pratique pour vite connaitre le type d'une variable en fonction de son nom (faut être discipliné) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    DefInt I
    DefLng L
    DefStr S
     
    Sub InfoVariables3()
        Dim L_Ligne, I_Colonne, S_Chaine
            MsgBox TypeName(L_Ligne)
            MsgBox TypeName(I_Colonne)
            MsgBox TypeName(S_Chaine)
    End Sub
    Les Lignes DefXxx doivent être dans la zone de déclaration du module.


    Instruction Type de données
    DefBool Boolean
    DefDbl Double
    DefInt Integer
    DefDate Date
    DefLng Long
    DefStr String
    DefCur Currency
    DefObj Object
    DefSng Single
    DefVar Variant
    DefByte Byte

  14. #14
    Membre émérite
    Homme Profil pro
    Directeur
    Inscrit en
    Avril 2003
    Messages
    724
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Directeur

    Informations forums :
    Inscription : Avril 2003
    Messages : 724
    Par défaut
    Par contre, les Goto, c'est à éviter.
    C'est pas de la bonne programmation.
    La seule utilisation, c'est dans la gestion d'erreurs.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Tester la somme de toutes les combinaisons possibles
    Par unix27 dans le forum Algorithmes et structures de données
    Réponses: 4
    Dernier message: 07/11/2014, 14h39
  2. La somme de toutes les lignes extrait d'un fichier
    Par nancy169 dans le forum kettle/PDI
    Réponses: 1
    Dernier message: 18/06/2012, 09h08
  3. Réponses: 12
    Dernier message: 05/03/2010, 14h10
  4. macrovariable=somme de toutes les colonnes
    Par SASMetrics dans le forum Macro
    Réponses: 3
    Dernier message: 27/02/2010, 01h37
  5. Réponses: 9
    Dernier message: 18/02/2008, 11h25

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo