Sommer les cellules de fichiers Excel ouvert

Le 07/08/2017 à 13:07

Bonjour,

J'ai une petite question concernant une somme de cellules de plusieurs fichiers à effectuer.

Je m'explique: je ne souhaite pas donner un chemin à VBA pour effectuer des sommes de fichiers mais je souhaiterais que VBA effectue seulement les sommes de cellules de fichiers ouverts. Les feuilles porteront les mêmes noms dans tous les fichiers.

Exemple:

J'ai trois fichiers ouverts Marc.xlsx, Antoine.xlsx, Quentin.xlsx.

Dans ces trois fichiers, il y a les onglets 2011, 2012, 2013, 2014, 2015, 2016, 2017.

Je souhaiterais en ouvrant ma macro dans un nouveau classeur sommer les cellules C17 à C20 pour l'onglet 2017, les cellules D22 à F22 pour l'onglet 2016, et le tableau C3 à H20 pour l'onglet 2015, etc...

Pourriez-vous m'aider s'il vous plait?

Le 07/08/2017 à 14:25

Bonjour Tayger, bonjour le forum,

Tayger a écrit :

Je souhaiterais en ouvrant ma macro dans un nouveau classeur sommer les cellules C17 à C20 pour l'onglet 2017, les cellules D22 à F22 pour l'onglet 2016, et le tableau C3 à H20 pour l'onglet 2015, etc...

J'aurais écrit en lançant plutôt qu'en ouvrant et les onglets plutôt que l'onglet...

Pas très clair tout ça !... D'abord, si j'ai bien compris tu souhaiterais que la macro se trouve sur un classeur différent de ceux ouverts. Ça me parait logique mais il faudrait connaître son nom pour l'exclure de la liste. Ensuite on calcule la somme des cellules citées pour tous les classeurs ouverts et on en fait quoi de cette somme ?! On la renvoie ? Où ? On l'affiche dans un message ?

Bref, manque de précision...

Le 07/08/2017 à 15:33

Bonjour,

Je vous prie de m'excuser pour mon imprécision. On renvoie les valeurs dans le classeur différent où se trouve la macro. Par exemple quand on a sommé les cellules des onglets 2016 des autres classeurs on renvoie la somme de ces cellules au même endroit dans un onglet 2016 dans le nouveau classeur.

J'espère que c'est un peu plus clair pour vous et vous prie sincèrement de m'excuser pour ce manque de clarté.

Bien à vous,

PS: j'ai fait une erreur en envoyant en message privé, veuillez m'excuser

Le 07/08/2017 à 16:01

Re,

Le code ci-dessous n'as pas été testé (la flemme de tout recréer) . Place-le dans un fichier que tu enregistreras avec l'extension .xlsm. Ouvre les fichiers et lance le. Dis-moi ce que ne va pas...

Sub Macro1()
Dim CC As Workbook 'déclare la variable CC (Classeur de Calcul)
Dim O As Worksheet 'déclare la variable O (Onglets)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim CL As Workbook 'déclare la variable CL (CLasseur)
Dim OP(1 To 3, 1 To 2) As Variant 'déclare le tableau de variables OP (Onglet/Plage)
Dim T As Double 'déclare la variable T (Total)

Set CC = ThisWorkbook 'définit le classeur de calcul CC
'ajoute si néccessaire les 7 onglets au classeur de calcul
For I = 1 To 7 'boucle sur les valeurs de 1 à 7
    On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
    Set O = Worksheets(CStr(2010 + I)) 'définit l'onglet O de l'année 2010 + I (génère une erreur si cet onglet n'existe pas)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err.Clear 'supprime l'erreur
        Sheets.Add After:=Sheets(Sheets.Count) 'ajoute un onglet en dernière position
        ActiveSheet.Name = CStr(2010 + I) 'renomme l'onglet ajouté
    End If 'fin de la condition
Next I 'prochaine valeur de la boucle
On Error GoTo 0 'annule la gestion des erreurs

'définit le Tableau OP des variables indexées Onglet/Plage
OP(1, 1) = "2017" 'définit le premier onglet de calcul
OP(1, 2) = "C17:C20" 'définit la plage du premier onglet de calcul
OP(2, 1) = "2016" 'définit le deuxième onglet de calcul
OP(2, 2) = "D22:F22" 'définit la plage du deuxième onglet de calcul
OP(3, 1) = "2015" 'définit le troisième onglet de calcul
OP(3, 2) = "C3:H20" 'définit la plage du troisième onglet de calcul

For I = 1 To 3 'boucle 1 : sur les 3 onglets du tableau OP
    For Each CEL In Range(OP(I, 2)) 'boucle 2 : sur toutes les cellules CEL de la plage du tableau OP
        For Each CL In Workbooks 'boucle 3 sur tous les classeur ouverts CL
            'si le nom du classeur n'est pas le nom de ce fichier, calcule le total T étant la somme
            'de la cellule ayant la même addresse que CEL dans l'onglet du tableau OP du classeur CL
            If Not CL.Name = CC.Name Then T = CL.Worksheets(OP(I, 1)).Range(CEL.Address) + T
        Next CL 'prochain classeur ouvert de la boucle 3
        'renvoie la somme T dans la cellule ayant la même adresse que CEL de l'onglet correspondant à OP(I,1) du classeur de calcul CC
        'réinitialise le total T
        CC.Worksheets(OP(I, 1)).Range(CEL.Address).Value = T: T = 0
    Next CEL 'prochaine cellule de la boucle 2
Next I 'prochaine valeur de la boucle 1 (= prochain onglet)
End Sub

Si tu dois rajouter des onglets/plages, au tableau OP pense à redéfinir la dimension de celui-ci :

Dim OP(1 to X, 1 to 2),

X étant le nombre total d'onglets utilisés. Et à redéfinir les variables indexées correspondantes. Par exemple :

Dim OP(1 to 4, 1 to 2)

OP(4, 1) = "2014"

OP(4, 2) = "A1:B10"

Pense aussi à adapter la valeur de la boucle 1 : For I= 1 to X

Le 07/08/2017 à 16:06

Merci beaucoup pour cette réponse, je vais tester et je reviens vers vous.

Le 07/08/2017 à 16:13

Re,

Une version où d'éventuelles anciennes données du classeur de calcul son effacées avant :

Sub Macro1()
Dim CC As Workbook 'déclare la variable CC (Classeur de Calcul)
Dim O As Worksheet 'déclare la variable O (Onglets)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim CL As Workbook 'déclare la variable CL (CLasseur)
Dim OP(1 To 3, 1 To 2) As Variant 'déclare le tableau de variable OP (Onglet/Plage)
Dim T As Double 'déclare la variable T (Total)

Set CC = ThisWorkbook 'définit le classeur de calcul CC
'ajoute si néccessaire les 7 onglets au classeur de calcul
For I = 1 To 7 'boucle sur les valeurs de 1 à 7
    On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
    Set O = Worksheets(CStr(2010 + I)) 'définit l'onglet de l'année 2010 + I (génère une erreur si cet onglet n'existe pas)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err.Clear 'supprime l'erreur
        Sheets.Add After:=Sheets(Sheets.Count) 'ajoute un onglet en dernière position
        ActiveSheet.Name = CStr(2010 + I) 'renomme l'onglet ajouté
    End If 'fin de la condition
Next I 'prochaine valeur de la boucle
On Error GoTo 0 'annule la gestion des erreur
OP(1, 1) = "2017" 'définit le premier onglet de calcul
OP(1, 2) = "C17:C20" 'définit la plage du premier onglet de calcul
OP(2, 1) = "2016" 'définit le deuxième onglet de calcul
OP(2, 2) = "D22:F22" 'définit la plage du deuxième onglet de calcul
OP(3, 1) = "2015" 'définit le troisième onglet de calcul
OP(3, 2) = "C3:H20" 'définit la plage du troisième onglet de calcul (NE DEVRAIT PAS SE CHEVAUCHER AVEC LA PREMIÈRE PLAGE ! ERREUR !)

'efface d'éventuelles anciennes valeurs
For I = 1 To UBound(OP, 1) 'boucle sur le nombre d'onglets du tableau OP
    CC.Worksheets(OP(I, 1)).Range(OP(I, 2)).ClearContents 'efface la plage de l'onglet de OP dans le classeur de calcul CC
Next I 'prochain onglet de la boucle

For I = 1 To UBound(OP, 1) 'boucle 1 : sur le nombre d'onglets du tableau OP
    For Each CEL In Range(OP(I, 2)) 'boucle 2 : sur toutes les celleules CEL de la plage du tableau OP
        For Each CL In Workbooks 'boucle 3 sur tous les classeur ouverts CL
            'si le nom du classeur n'est pas le nom de ce fichier, calcule le total T étant la somme
            'de la cellule ayant la même addresse que CEL dans l'onglet du tableau OP du classeur CL
            If Not CL.Name = CC.Name Then T = CL.Worksheets(OP(I, 1)).Range(CEL.Address) + T
        Next CL 'prochain classeur ouvert de la boucle 3
        'renvoie la somme T dans la cellule ayant la même adresse que CEL de l'onglet correspondant à OP(I,1) du classeur de calcul CC
        'réinitialise le total T
        CC.Worksheets(OP(I, 1)).Range(CEL.Address).Value = T: T = 0
    Next CEL 'prochaine cellule de la boucle 2
Next I 'prochaine valeur de la boucle 1 (= prochain onglet)
End Sub

Si tu dois rajouter des onglets/plages, au tableau OP pense à redéfinir la dimension de celui-ci :

Dim OP(1 to X, 1 to 2),

X étant le nombre total d'onglets utilisés. Et à redéfinir les variables indexées correspondantes. Par exemple :

Dim OP(1 to 4, 1 to 2)

OP(4, 1) = "2014"

OP(4, 2) = "A1:B10"

Plus besoin d'adapter la valeur de la boucle 1 !

Le 07/08/2017 à 16:44

Excusez moi de vous redéranger. Si je dois prendre plusieurs plages à sommer dans un même onglet (disons y plages), je dois modifier le code comme il suit:

Dim OP(1 To x, 1 To y) As Variant 

'puis dans la définition des tableaux OP des variables indexées onglets/plages je fais comme il suit:

OP(1, 1) = "2017" 'définit le premier onglet de calcul
OP(1, 2) = "C17:C20" 'définit la première plage du premier onglet de calcul
OP(1, 3) = "F22:T48" 'définit la deuxième plage du deuxème onglet de calcul

Je vous remercie par avance de votre aide.

Le 07/08/2017 à 16:53

Re,

Non ! il faudrait tout recoder... Le plus simple est de définir la plage comme ça (je reprends ton exemple) :

OP(1, 2) = "C17:C20, F22:T48"

Tu peux ajouter des plages en les séparent par une virgule et faisant attention aux guillemets uniquement au début et à la fin !

Sinon, est-ce que ça marche ?!...

Le 07/08/2017 à 18:36

Et non cela ne marche pas.

Le mode débogage me dit "Erreur d'exécution 9", L'indice n'appartient pas à la selection.

Et cela surligne en jaune le bout de code suivant: T = CL.Worksheets(OP(I, 1)).Range(CEL.Address) + T

Avez-vous une idée de la cause de cette erreur?

Merci par avance

Le 07/08/2017 à 20:00

Re,

C'est l'erreur classique quand le nom de l'onglet dans le classeur est différent du non de l'onglet dans le code.

Quel est le nom du classeur CL quand ça plante ?

Quelle est la valeur de la variable OP(I, 1) quand ça plante ?

L'idéal serait que tu fournisses trois fichiers exemples... On perdrait moins de temps !

Le 07/08/2017 à 20:41

Re,

Finalement je me suis tapé ton boulot (c'est un comble) !... J'ai créé 3 fichiers avec des valeurs dans les plages stipulées et j'ai eu la même erreur que toi. Mais cela ne vient pas du code mais du fait que j'ai un autre classeur ouvert mais masqué, le classeur des macros personnelles PERSONNAL.XLSB, qui n'a pas les onglets des variables du tableau OP et par conséquent, fait planter le code... Je l'ai donc modifié pour qu'il n'accepte que les fichiers .xlsx ouverts :

Sub Macro1()
Dim CC As Workbook 'déclare la variable CC (Classeur de Calcul)
Dim O As Worksheet 'déclare la variable O (Onglets)
Dim CEL As Range 'déclare la variable CEL (CELlule)
Dim CL As Workbook 'déclare la variable CL (CLasseur)
Dim OP(1 To 3, 1 To 2) As Variant 'déclare le tableau de variable OP (Onglet/Plage)
Dim T As Double 'déclare la variable T (Total)

Set CC = ThisWorkbook 'définit le classeur de calcul CC
'ajoute si néccessaire les 7 onglets au classeur de calcul
For I = 1 To 7 'boucle sur les valeurs de 1 à 7
    On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
    Set O = Worksheets(CStr(2010 + I)) 'définit l'onglet de l'année 2010 + I (génère une erreur si cet onglet n'existe pas)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err.Clear 'supprime l'erreur
        Sheets.Add After:=Sheets(Sheets.Count) 'ajoute un onglet en dernière position
        ActiveSheet.Name = CStr(2010 + I) 'renomme l'onglet ajouté
    End If 'fin de la condition
Next I 'prochaine valeur de la boucle
On Error GoTo 0 'annule la gestion des erreur
OP(1, 1) = "2017" 'définit le premier onglet de calcul
OP(1, 2) = "C17:C20" 'définit la plage du premier onglet de calcul
OP(2, 1) = "2016" 'définit le deuxième onglet de calcul
OP(2, 2) = "D22:F22" 'définit la plage du deuxième onglet de calcul
OP(3, 1) = "2015" 'définit le troisième onglet de calcul
OP(3, 2) = "C3:H20" 'définit la plage du troisième onglet de calcul

'efface d'éventuelles anciennes valeurs
For I = 1 To UBound(OP, 1) 'boucle sur le nombre d'onglets du tableau OP
    CC.Worksheets(OP(I, 1)).Range(OP(I, 2)).ClearContents 'efface la plage de l'onglet de OP dans le classeur de calcul CC
Next I 'prochain onglet de la boucle

For I = 1 To UBound(OP, 1) 'boucle 1 : sur le nombre d'onglets du tableau OP
    For Each CEL In Range(OP(I, 2)) 'boucle 2 : sur toutes les celleules CEL de la plage du tableau OP
        For Each CL In Workbooks 'boucle 3 sur tous les classeur ouverts CL
            'si l'extension du classeur est ".xlsx", calcule le total T étant la somme de chaque cellule
            'ayant la même addresse que CEL dans l'onglet du tableau OP du classeur CL
            If Right(CL.Name, 5) = ".xlsx" Then T = CL.Worksheets(OP(I, 1)).Range(CEL.Address) + T
        Next CL 'prochain classeur ouvert de la boucle 3
        'renvoie la somme T dans la cellule ayant la même adresse que CEL de l'onglet correspondant à OP(I,1) du classeur de calcul CC
        'réinitialise le total T
        CC.Worksheets(OP(I, 1)).Range(CEL.Address).Value = T: T = 0
    Next CEL 'prochaine cellule de la boucle 2
Next I 'prochaine valeur de la boucle 1 (= prochain onglet)
End Sub

Là, plus de problèmes. Mais, dans le ruban Affichage, clique sur le bouton Afficher pour voir la liste des fichiers ouverts mais masqués. Il faut que ces fichiers n'apparaissent pas dans la boucle For Each CL in Workbooks.

Le 08/08/2017 à 00:44

Bonjour,

Bonjour Thautheme, xlsb ou xlsm ?

L'un ou l'autre, il n'aurait pas IsAddin=True des fois ? Ce qui les rends invisibles et accessoirement permettrai de les détecter.

eric

Le 08/08/2017 à 01:59

Bonsoir (ou bonjour) le fil, le forum,

Je ne connaissais pas cette propriété IsAddin (merci pour le tuyau !...), mais dans mon cas ça ne fonctionne pas car mon classeur des macros personnelles PERSONAL.XLSB renvoie [Faux]...

Le 09/08/2017 à 17:17

Désolé pour ce petit contretemps, finalement c'est juste que le fichier n'était pas enregistré en format .xlsm

Rechercher des sujets similaires à "sommer fichiers ouvert"