Copie de feuille Excel et moyenne ou consolidation

**mjp06** · 17/09/2009, 21h00

Bonjour,

Je suis débutante dans les macros VBA donc voici ce que je dois réussir à faire.

Dans Excel 2003, chaque employé complète un formulaire qui est ensuite transmis au responsable. Le même formulaire est rempli plus d’une fois par chaque employé. Le nombre de formulaires complétés est différent d’un employé à l’autre.

Dans un dossier au nom de l’employé :
- J’ai un fichier Excel nommé Final pour la consolidation de tous les formulaires de cet employé.
- J’ai un dossier qui contient tous les formulaires complétés par l’employé.

1- J’aimerais avoir une macro qui fait une copie de chaque formulaire complété par l’employé et les copie dans le fichier Final en nommant les nouvelles feuilles copiées par le nom du classeur d’origine. Chaque formulaire contient une seule feuille nommée Feuil1 et le classeur est protégé par un mot de passe que je connais. J’aimerais savoir qu’est-ce qui va démarrer la macro? Est-ce que je dois mettre un CommandButton de la boite à outils contrôle et cliquez pour démarrer la macro? Merci d’être clair puisque je commence dans les macros

2 – Après la copie des formulaires de l’employé, j’ai dans mon classeur Final, la feuille Conso pour consolider les résultats et un nombre X de feuilles de formulaires copiées.

Les résultats aux questions du formulaire sont soient : 0; 1; 2; 3 ou N/A.

Je veux avoir sur la feuille Conso la moyenne des résultats des formulaires en excluant les valeurs N/A. Je pensais à quelque chose comme :
=somme(toutes cellules P156 de toutes les feuilles sauf Conso)/NB.SI(toutes cellules P156 de toutes les feuilles sauf Conso;">=0")
mais je ne sais pas comment y arriver en VBA puisque je ne veux pas le faire manuellement.

Le nombre de feuilles de formulaires va être différent d’un employé à l’autre donc j’aimerais avoir un code qui tient en compte cette variable et ne prend pas la feuille Conso puisque je veux les résultats sur la feuille Conso.

Donc sur la feuille Conso en P156 je voudrais avoir la moyenne des cellules P156 des autres feuilles en excluant les N/A.

J’aimerais savoir comment indiquer dans la macro les cellules où je veux obtenir une moyenne car ce n’est pas régulier ex : P156, P158, P162, P165…

Est-ce que tout va ce calculer tout seul à partir du moment où je vais copier les formulaires comme décrit dans le point 1?

3- Je dois faire la même chose pour chaque employé, donc j’aimerais savoir comment réinitialiser mon modèle : Avoir seulement le classeur Final avec la feuille Conso avec les cellules pour les moyennes vides.

Merci beaucoup de m’aider et d’être patient avec mes nombreuses questions.

Merci Merci Merci

mjp06

**mercatog** · 17/09/2009, 21h10

Tout est faisable!
reste à détailler certains points.
un seul fichier Final par employé ou ce fichier englobe le tout
chaque employé a un dossier où sont enregistrés ses formulaires?
tes P156, P158, P162, P165 sont elles les dernières lignes de tes formulaires?

je propose de partir pas à pas mais sûrement

**mjp06** · 17/09/2009, 21h40

Bonjour mercatog,

Il y aura un fichier Final par employé enregistré dans le fichier au nom de l’employé et qui contient aussi un dossier avec tous ses formulaires.

Les cellules P156, P158, P162, P165… sont les cellules des formulaires mais je veux remettre la moyenne dans la même cellule mais sur la feuille Conso.

Donc sur la feuille Conso, la cellule P156 va donner la moyenne de toutes les cellules P156 des formulaires en excluant les N/A.

Merci beaucoup pour ton aide,

Mjp06

**mercatog** · 18/09/2009, 01h26

Bonjour,
Ci-joint un premier pas simplifié mais qui comporte déjà quelques pistes
voila, j'ai crée des formulaires identiques remplis, en plus du fichier Final
j'ai mis le tout dans un même dossier
à partir du fichier Final, je lance la procédure qui consolide les données issus des formulaires (j'ai simplifié la disposition des formulaires et de la feuille Conso) la logique reste la même.
j'ai essayé de commenter le code

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Private Sub Consol_Click()
Dim awbk As Workbook, wbk As Workbook
Dim sht As Worksheet, ws As Worksheet
Dim Chemin As String, Fichier As String
Dim i As Long, LastLig As Long
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
Set awbk = ThisWorkbook                                                     'Le fichier Final
Set sht = awbk.Sheets("Conso")                                              'sht la feuille Conso
 
Chemin = awbk.Path & "\"                                                    'Le dossier des fichiers formulaire et du fichier final
 
Fichier = Dir(Chemin & "*.xls")                                          'Les fichier excel de ce dossier
 
Do While Len(Fichier) > 0
    If Fichier <> awbk.Name Then                                            'Pour passer le fichier Final
        Set wbk = Workbooks.Open(Chemin & Fichier)                          'On ouvre le fichier formulaire
 
        For Each ws In awbk.Worksheets                                      'Teste si la feuille du fichier formulaire est déjà existant
            If ws.Name = Left(wbk.Name, Len(wbk.Name) - 4) Then
                ws.Delete                                                   'Si elle existe, elle sera supprimée
                Exit For
            End If
        Next ws
 
        wbk.Sheets("Feuil1").Copy After:=awbk.Sheets(awbk.Sheets.Count)     'On copie la feuil1 dans notre fichier final
        ActiveSheet.Name = Left(wbk.Name, Len(wbk.Name) - 4)                'On renomme la feuille
        wbk.Close                                                           'On ferme le fichier formulaire
        Set wbk = Nothing
    End If
    Fichier = Dir()                                                         'On passe au fichier formulaire suivant
Loop
sht.Select
LastLig = sht.Range("A65536").End(xlUp).Row
 
'On boucle sur les feuilles importée et on insère la moyenne (les N/A ne sont pas pris en considération)
 
For i = 1 To LastLig Step 2
    sht.Range("B" & i).Formula = "=average('" & Sheets(2).Name & ":" & Sheets(Sheets.Count).Name & "'!B" & i & ")"
Next i
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
End Sub

Cordialement

**mjp06** · 21/09/2009, 17h35

Merci Mercatog pour le code, mais je ne peux pas ouvrir le fichier Final du fichier attaché car mon anti-virus l’a supprimé. J’ai seulement accès aux fichiers de formulaire.

J’ai fait un test en copiant le code qui tu as écrit sur le forum dans mon fichier Excel en inscrivant le chemin du dossier au lieu du \, mais il ne se passe rien.

J’aimerais savoir qu’est-ce qui démarre la macro.

J’aimerais savoir aussi où je dois coller le code puisque je n’ai pas accès à ton fichier Final.

Merci beaucoup de m’aider

mjp06

**mercatog** · 21/09/2009, 17h39

Bonjour, enfin te voilà
un clique sur un bouton nommé Consol

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Chemin = awbk.Path & "\"

indique que le chemin complet est le dossier où ton fichier excel actuel est sauvegardé
sinon tu peux faire ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Chemin = "C:\Le chemin de ton dossier\"

**mjp06** · 22/09/2009, 16h58

Merci beaucoup

J’ai créé le bouton. Lorsque je clique dessus, les feuilles des formulaires sont copiées et renommées. Parfais!

Cependant, la moyenne me pose problème. Elle apparaît à toutes les deux lignes de la colonne B.

Pour l’instant, j’obtiens le résultat #### qui dit que j’ai un problème puisque je divise par 0 puisqu’il n’y a pas de résultats sur cette ligne dans les formulaires.

J’aimerais pouvoir lui dire de calculer la moyenne des formulaires pour les lignes B70 à B300 quand les formulaires contiennent des résultats et de ne rien écrire si il n’y a pas de résultats dans les formulaires.

Merci mille fois,

Mjp06

**mercatog** · 22/09/2009, 17h08

Une ligne sur deux était comme essai puisque tu ne donnais pas la disposition de tes données

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Dim awbk As Workbook, wbk As Workbook
Dim sht As Worksheet, ws As Worksheet
Dim Chemin As String, Fichier As String
Dim i As Long, LastLig As Long
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
Set awbk = ThisWorkbook                                                     'Le fichier Final
Set sht = awbk.Sheets("Conso")                                              'sht la feuille Conso
 
Chemin = awbk.Path & "\"                                                    'Le dossier des fichiers formulaire et du fichier final
 
Fichier = Dir(Chemin & "*.xls")                                             'Les fichier excel de ce dossier
 
Do While Len(Fichier) > 0
    If Fichier <> awbk.Name Then                                            'Pour passer le fichier Final
        Set wbk = Workbooks.Open(Chemin & Fichier)                          'On ouvre le fichier formulaire
 
        For Each ws In awbk.Worksheets                                      'Teste si la feuille du fichier formulaire est déjà existant
            If ws.Name = Left(wbk.Name, Len(wbk.Name) - 4) Then
                ws.Delete                                                   'Si elle existe, elle sera supprimée
                Exit For
            End If
        Next ws
 
        wbk.Sheets("Feuil1").Copy After:=awbk.Sheets(awbk.Sheets.Count)     'On copie la feuil1 dans notre fichier final
        ActiveSheet.Name = Left(wbk.Name, Len(wbk.Name) - 4)                'On renomme la feuille
        wbk.Close                                                           'On ferme le fichier formulaire
        Set wbk = Nothing
    End If
    Fichier = Dir()                                                         'On passe au fichier formulaire suivant
Loop
sht.Select
LastLig = sht.Range("A65536").End(xlUp).Row
 
'On boucle sur les feuilles importé et on insère la moyenne (les N/A ne sont pas pris en considération)
 
For i = 70 To LastLig   'ou 300 au cas où le nombre de lignes est fixe
    sht.Range("B" & i).Formula = "=average('" & Sheets(2).Name & ":" & Sheets(Sheets.Count).Name & "'!B" & i & ")"
    If IsError(sht.Range("B" & i).Value) Then sht.Range("B" & i).ClearContents
Next i
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True

**mjp06** · 22/09/2009, 17h44

Est-ce que la partie suivante du code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Then sht.Range("B" & i).ClearContents

peut causer problème si des cellules de la colonne B sont fusionnées avec d'autres?

Exemple : B70 est fusionné avec C70 donc la cellule s'appelle toujours B70

Lorsqu'il y a des cellules fusionnées, elles le sont dans la feuille final et les feuilles de formulaires.

Est-ce qu'on peut changer une partie du code pour tenir compte des cellules fusionnées?

Merci,

mjp06

**mercatog** · 22/09/2009, 17h52

Les cellules fusionnées sont souvent une source de maux de tête en vba.
as tu essayé le code?
quel résultat il donne?
s'il y a erreur quelque part, tu devrais la reporter au forum pour plus de détails.

**mjp06** · 22/09/2009, 17h55

Oui, j'ai changer le code pour celui qui commence à la ligne 70 et lorsque je clique sur le bouton pour démarrer le code j'obtiens :

Erreur d'exécution 1004
Impossible de modifier une cellule fusionnée.

**mercatog** · 22/09/2009, 18h10

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    If IsError(sht.Range("B" & i).Value) Then sht.Range("B" & i & ":C" & i).ClearContents

change la ligne comme ci-dessus donc

**mjp06** · 30/09/2009, 20h27

Merci beaucoup, j'ai fait le changement.

Je viens aussi de faire le changement pour Excel 2007...

Les feuilles sont copiées parfaitement, mais il me manque des moyennes vers la fin du document dans le Conso.

Est-ce que ça peut être dû à la partie du code qui définit la valeur de LastLig alors qu'il y avait seulement 65536 lignes en 2003 alors qu'en 2007 il y en a beaucoup plus?

Merci,

mjp06

**mercatog** · 30/09/2009, 21h29

Oui si ta feuille comporte plus que 65536 lignes de données.

j'en doute
sinon tu peux utiliser ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

LastLig = sht.Cells(sht.Columns(1).Cells.Count,1).End(xlUp).Row

**mjp06** · 30/09/2009, 21h49

Non j'ai seulement 400 lignes.

J'ai changé la ligne de code mais je n'ai pas de différence dans la feuille Conso...

Merci,

mjp06

**mercatog** · 30/09/2009, 21h59

Je n'ai pas d'autre moyen pour vérifier; je n'ai pas ton fichier final
j'ai devant moi le fichier en 2003 et il fonctionne
si tu peux remettre ton fichier (après conversion en 2003); je peux jeter un coup d'oeil

**mjp06** · 06/10/2009, 16h15

Bon alors finalement j'ai enlevé le terme LastLig et j'ai remplacé par 250 et ça fonctionne.

Toutes les moyennes sont compilés sur la feuille Conso.

Cependant, j'aimerais savoir si on peut ajouter dans la formule la prise en charge des N/A.

Présentement, lorsqu'on demande la moyenne des feuilles on obtient une cellule vide dans la feuille Conso lorsque la ligne contient seulement des N/A.

Exemple:
Formulaire1 B70 = N/A
Formulaire2 B70 = N/A
Formulaire3 B70 = N/A
Donc Conso B70 = Cellule vide

Est-ce qu'on peut dire que si le résultat de B70 (exemple) de toutes les feuilles sauf Conso est égal à N/A ont écrit N/A à la ligne B70 du Conso?

Le tout sans mettre en péril le calcul des moyennes des feuilles?

Merci beaucoup,

mjp06

**mercatog** · 06/10/2009, 16h33

remaplace

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 If IsError(sht.Range("B" & i).Value) Then sht.Range("B" & i & ":C" & i).ClearContents

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 If IsError(sht.Range("B" & i).Value) Then sht.Range("B" & i & ":C" & i).Value="N/A"

Edit

**mjp06** · 06/10/2009, 17h03

Et bien on avait écrit ClearContents parce que je n'ai pas des données sur toutes les lignes de la colonne B. Donc si je change pour Value = N/A, je me retrouve avec des centaines de N/A que je ne veux pas. Cependant, certaines questions ont comme résultat N/A et ceux là je les veux.

Si on laisse le code de départ comme avant avec ClearContents, est-ce qu'on peut ajouter une autre partie qui dirait si toutes les lignes B70 des formulaires sont égales à N/A donc écrire N/A. Donc on ne briserait pas la moyenne et les lignes vides resteraient vides.

Merci beaucoup,

mjp06

**mercatog** · 06/10/2009, 17h08

Je crois que nous ne parlons pas de la même chose,
je récapitule
dans ta feuille Conso, tu as la moyenne de toutes tes feuilles formulaires.
Quand toutes les feuilles formulaires ont une valeur N/A; la formule de calcul de la moyenne donne "Div/0" (erreur)
la ClearContents, efface cette "Div/0"!
maintenant tu veux à la place, "N/A"; c'est ce que j'avais proposé dans mon précédent post.
Ou, je n'ai pas bien compris...

Copie de feuille Excel et moyenne ou consolidation

Macros et VBA Excel

Discussions similaires

Partager

Partager