Fusionner automatiquement les données de plusieurs feuilles Excel dans une synthèse avec VBA

🎯 Objectif de la macro

L’objectif de cette macro est de :

1. Parcourir toutes les feuilles d’un classeur Excel (sauf la feuille "Synthèse"),

2. Ajouter le nom de chaque feuille à chaque ligne de données,

3. Copier toutes les lignes utiles vers une nouvelle feuille de synthèse,

4. Créer un tableau consolidé prêt à être analysé ou exporté.

C’est un excellent outil pour gérer des fichiers contenant :

• Des feuilles de suivi par client ou par mois,

• Des relevés d'élèves, des états de paie, ou tout autre tableau standardisé feuille par feuille.

---

🧠 Code complet et propre

vba

Option Explicit
Sub Cop_sheet_dans_une_feuille2()
    Dim ws As Worksheet, syntheseWs As Worksheet
    Dim dercol As Long, derligne As Long, derligne2 As Long
    Dim i As Long, j As Long, lr As Long
    Dim lastSheetIndex As Long

    ' Déterminer le nombre de feuilles
    lastSheetIndex = ThisWorkbook.Sheets.Count

    ' Créer la feuille de synthèse à la fin
    Set syntheseWs = ThisWorkbook.Sheets.Add(After:=Sheets(lastSheetIndex))
    syntheseWs.Name = "Synthèse"

    ' Copier les en-têtes depuis la ligne 10 de la première feuille
    With Sheets(1)
        .Range("A10:AB10").Copy Destination:=syntheseWs.Range("A1")
    End With

    ' Parcourir toutes les feuilles sauf la feuille de synthèse
    For j = 1 To lastSheetIndex
        Set ws = Sheets(j)
        If ws.Name <> "Synthèse" Then
            With ws
                ' Trouver la dernière colonne utilisée dans la ligne 10
                dercol = .Cells(10, .Columns.Count).End(xlToLeft).Column
                ' Trouver la dernière ligne selon la colonne A
                derligne = .Cells(.Rows.Count, 1).End(xlUp).Row

                ' Ajouter le nom de la feuille dans la colonne suivante (dercol + 1)
                For i = 11 To derligne
                    .Cells(i, dercol + 1).Value = .Name
                Next i

                ' Copier les données de la feuille vers la feuille Synthèse
                For i = 11 To derligne
                    lr = syntheseWs.Cells(syntheseWs.Rows.Count, 1).End(xlUp).Row + 1
                    .Rows(i).Copy Destination:=syntheseWs.Cells(lr, 1)
                Next i
            End With
        End If
    Next j
End Sub

---

🔍 Étape par étape

1. Option Explicit

• Force à déclarer toutes les variables → évite les erreurs de frappe.

2.📌 Bloc de code :

vba

Dim ws As Worksheet, syntheseWs As Worksheet
Dim dercol As Long, derligne As Long, derligne2 As Long
Dim i As Long, j As Long, lr As Long
Dim lastSheetIndex As Long

---

🔍 Explication détaillée

✅ Dim ws As Worksheet, syntheseWs As Worksheet

• ws : représente la feuille de calcul active dans la boucle. Elle change à chaque itération.

• syntheseWs : représente la feuille de synthèse créée par la macro. Toutes les données y seront copiées.

➡️ Ces deux variables permettent de manipuler des feuilles sans les sélectionner (plus rapide, plus propre).

---

✅ Dim dercol As Long

• dercol = Dernière colonne utilisée dans une feuille, souvent détectée à partir de la ligne d’en-tête (ligne 10).

• Sert à savoir où ajouter le nom de la feuille source dans la colonne suivante.

---

✅ Dim derligne As Long

• derligne = Dernière ligne de données dans une colonne de référence (souvent colonne A ou B).

• Utile pour définir la fin du tableau à copier.

---

✅ Dim derligne2 As Long

• Variante de derligne, parfois calculée selon une autre colonne.

• Peut être utile pour comparer différentes colonnes ou éviter les erreurs de format.

---

✅ Dim i As Long

• Compteur de boucle pour parcourir les lignes d’une feuille.

✅ Dim j As Long

• Compteur de boucle pour parcourir les feuilles du classeur.

✅ Dim lr As Long

• lr signifie "Last Row" : la prochaine ligne vide dans la feuille "Synthèse".

• Sert à savoir où coller la prochaine ligne copiée sans écraser les précédentes.

---

✅ Dim lastSheetIndex As Long

• Contient le nombre total de feuilles du classeur au moment de l'exécution.

• Sert de limite à la boucle For j = 1 To lastSheetIndex.

---

🧠 Résumé visuel

Variable	Type	Rôle principal
ws	Worksheet	Feuille active dans la boucle
syntheseWs	Worksheet	Feuille de destination ("Synthèse")
dercol	Long	Dernière colonne utilisée
derligne	Long	Dernière ligne de données
derligne2	Long	Ligne de contrôle supplémentaire
i	Long	Index des lignes
j	Long	Index des feuilles
lr	Long	Dernière ligne vide dans "Synthèse"
lastSheetIndex	Long	Nombre total de feuilles

.

3. Création de la feuille Synthèse

vba

Set syntheseWs = ThisWorkbook.Sheets.Add(After:=Sheets(lastSheetIndex))
syntheseWs.Name = "Synthèse"

• Crée une nouvelle feuille à la fin nommée "Synthèse".

4. Copie des en-têtes

vba

Sheets(1).Range("A10:AB10").Copy Destination:=syntheseWs.Range("A1")

• Copie les titres du tableau depuis la ligne 10 de la première feuille.

5. Boucle sur toutes les feuilles sauf "Synthèse"

vba

For j = 1 To lastSheetIndex

• Parcourt chaque feuille.

6. Détection des limites du tableau

vba

dercol = .Cells(10, .Columns.Count).End(xlToLeft).Column
derligne = .Cells(.Rows.Count, 1).End(xlUp).Row

• dercol : dernière colonne utilisée à la ligne 10.

• derligne : dernière ligne remplie de la colonne A (repère de données).

7. Ajout du nom de la feuille

vba

.Cells(i, dercol + 1).Value = .Name

• Écrit le nom de la feuille dans une colonne juste après les données.

8. Copie ligne par ligne vers la Synthèse

vba

lr = syntheseWs.Cells(syntheseWs.Rows.Count, 1).End(xlUp).Row + 1
.Rows(i).Copy Destination:=syntheseWs.Cells(lr, 1)

• Trouve la première ligne vide dans Synthèse, puis y copie la ligne.

---

🎓 Pourquoi c’est utile ?

• Gagner du temps au lieu de copier-coller feuille par feuille.

• Conserver la trace de la source des données (grâce au nom de la feuille).

• Créer un tableau unique pour des TCD, filtres, ou exports.