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