Excel : déplacer automatiquement une ligne avec une case à cocher (VBA + Power Query)

Automatisez votre suivi Excel : quand une case à cocher passe à TRUE, la ligne part vers une feuille « Completed ». Voici une mise en œuvre claire en VBA (événements), des variantes multi‑feuilles, un retour en arrière possible, et une alternative sans code via Power Query.

Déplacer automatiquement une ligne vers une autre feuille lorsqu’une case à cocher est activée

Vue d’ensemble

• Objectif : dès qu’une case à cocher (liée à une cellule) passe à TRUE, copier la ligne courante depuis la feuille de travail (« Trailer Log », « Master », etc.) vers une feuille de suivi (« Completed Trailer Log », « Completed »).
• Contexte : les cases proviennent du menu Développeur › Insérer › Contrôle de formulaire. Elles sont reliées à la cellule sous‑jacente qui renvoie TRUE/FALSE (propriété Cellule liée / LinkedCell).
• Principe : un gestionnaire d’événement Worksheet_Change écoute les modifications de la colonne de cases (ex. colonne H). À TRUE, la ligne est copiée en bas de la feuille cible.

Pré‑requis

• Excel (Windows) avec l’onglet Développeur activé.
• Cases à cocher de type Contrôle de formulaire (pas ActiveX), chacune liée à sa cellule (clic droit → Format de contrôle → onglet Contrôle → Cellule liée).
• Nom exact de la feuille de destination (ex. Completed Trailer Log) créé au préalable.

Solution VBA minimale (événements)

Collez ce code dans le module de la feuille source (ex. Trailer Log) : clic droit sur l’onglet de la feuille → Afficher le code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Columns("H")) Is Nothing Then Exit Sub   ' colonne de la case à cocher
    If Target.CountLarge > 1 Then Exit Sub                           ' une seule cellule à la fois
    If Target.Value <> True Then Exit Sub                            ' on ne déclenche qu'à TRUE

    Application.EnableEvents = False                                 ' éviter la récursion
    With ThisWorkbook.Sheets("Completed Trailer Log")                ' feuille de destination
        Rows(Target.Row).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)  ' copie à la 1ʳᵉ ligne vide
    End With
    Application.EnableEvents = True
End Sub

• À adapter : nom de la feuille destination, lettre de la colonne contenant la case, type de contrôle (Form vs ActiveX).
• La ligne reste visible sur la feuille source ; voir la section suivante pour la supprimer après la copie.

Retirer la ligne de la feuille source après la copie

Pour déplacer (et non simplement copier), ajoutez cette ligne juste après l’instruction Copy :

Rows(Target.Row).Delete

Conservez l’encadrement par Application.EnableEvents = False/True afin d’éviter une boucle d’événements.

Astuce : si vous souhaitez coller uniquement les valeurs (et pas les formules), remplacez la copie par :

With ThisWorkbook.Sheets("Completed Trailer Log")
    Dim dest As Range
    Set dest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, Cells(Target.Row, Columns.Count).End(xlToLeft).Column)
    dest.Value = Rows(Target.Row).Resize(1, dest.Columns.Count).Value
End With

Utiliser la même logique sur plusieurs feuilles d’entrée

Méthode rapide

Dupliquez la macro Worksheet_Change dans chaque feuille « feeder » (ex. Trailer Log, Master) et faites‑la pointer vers une seule feuille récapitulative (ex. Completed).

Méthode centralisée (niveau classeur)

Placez un code dans ThisWorkbook pour écouter les changements sur toutes les feuilles, et ignorez la feuille de synthèse :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo SafeExit
    If Sh.Name = "Summary" Or Sh.Name = "Completed Trailer Log" Then Exit Sub
    If Intersect(Target, Sh.Columns("H")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value <> True Then Exit Sub

    Application.EnableEvents = False
    With ThisWorkbook.Sheets("Completed Trailer Log")
        Sh.Rows(Target.Row).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
SafeExit:
    Application.EnableEvents = True
End Sub

Option avancée : ajoutez dans une colonne masquée le nom de la feuille d’origine pour faciliter un retour en arrière (voir la section « Annuler un transfert »).

Cartographier plusieurs feeds vers des cibles distinctes

Si chaque feuille source doit alimenter une cible différente, utilisez un dictionnaire :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim map As Object, destName As String
    On Error GoTo SafeExit

    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Sh.Columns("H")) Is Nothing Then Exit Sub
    If Target.Value <> True Then Exit Sub

    Set map = CreateObject("Scripting.Dictionary")
    map.Add "Trailer Log", "Completed Trailer Log"
    map.Add "Master", "Completed"

    If Not map.Exists(Sh.Name) Then Exit Sub
    destName = map(Sh.Name)

    Application.EnableEvents = False
    With ThisWorkbook.Sheets(destName)
        Sh.Rows(Target.Row).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
SafeExit:
    Application.EnableEvents = True
End Sub

Annuler un transfert en décochant la case sur la feuille cible

Sur la feuille « Completed », on souhaite que TRUE → FALSE renvoie la ligne à sa feuille d’origine et supprime l’entrée de « Completed ».

Préparer la colonne « SourceSheet »

• Lors de la copie vers « Completed », stockez le nom d’origine dans une colonne masquée, par exemple colonne Z ou une colonne nommée SourceSheet.
• Le gestionnaire d’événement de « Completed » s’en sert pour restaurer vers la bonne feuille.

Copier avec traçabilité

Adaptez la copie pour écrire le nom de la feuille source dans SourceSheet (à remplacer par votre colonne) :

' Dans le code de copie (source → Completed)
Application.EnableEvents = False
With ThisWorkbook.Sheets("Completed")
    Dim nextRow As Long
    nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    Sh.Rows(Target.Row).Copy .Rows(nextRow)
    .Cells(nextRow, "Z").Value = Sh.Name     ' Z = colonne SourceSheet
End With
Application.EnableEvents = True

Handler d’annulation sur « Completed »

Dans le module de la feuille « Completed » :

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    If Intersect(Target, Me.Columns("H")) Is Nothing Then Exit Sub    ' même colonne que la case
    If Target.CountLarge > 1 Then Exit Sub

    ' Détection du passage à FALSE
    If Target.Value = False Then
        Application.EnableEvents = False

        Dim srcSheet As Worksheet, srcName As String
        srcName = Me.Cells(Target.Row, "Z").Value                     ' Z = SourceSheet
        If Len(srcName) = 0 Then
            Set srcSheet = ThisWorkbook.Sheets("Master")              ' solution simple: défaut
        Else
            Set srcSheet = ThisWorkbook.Sheets(srcName)
        End If

        ' Copier vers l'origine (en bas), puis supprimer la ligne de Completed
        Me.Rows(Target.Row).Copy srcSheet.Cells(srcSheet.Rows.Count, 1).End(xlUp).Offset(1)
        Me.Rows(Target.Row).Delete
    End If
CleanExit:
    Application.EnableEvents = True
End Sub

Variante simple : si vous ne stockez pas l’origine, renvoyez toujours vers « Master ».

Bonnes pratiques et pièges courants

Problème	Correctif / astuce
Boucle d’événements infinie	Encadrez Copy/Delete par Application.EnableEvents = False ... True avec un On Error qui garantit la remise à True.
Boîte à cocher non liée à la cellule	Vérifiez Format de contrôle › Cellule liée. Sans lien, aucune valeur TRUE/FALSE ne remonte et l’événement ne se déclenche pas.
Lenteur sur grands volumes	Copiez les valeurs plutôt que tout (formules/formats), groupez les écritures (tableau VBA), ou basculez sur Power Query + Tables quand le temps réel n’est pas requis.
Contrôles ActiveX copiés mais non fonctionnels	Préférez les Contrôles de formulaire pour ce scénario ; ils sont plus légers et indépendants du poste.
Consolidation multi‑feuilles sans code	Utilisez Power Query pour empiler les tables, puis filtrez la colonne Dispatched.
Filtres actifs dans la feuille source	Si vous travaillez avec des tables filtrées, copiez les cellules visibles : EntireRow.SpecialCells(xlCellTypeVisible).
Feuille destination manquante	Créez‑la à la volée : If Not Exists Then Worksheets.Add.Name = "Completed" (voir utilitaire ci‑dessous).
Déplacement au mauvais endroit	Vérifiez la colonne surveillée (Me.Columns("H")) et l’orthographe exacte de la feuille cible.

Variante orientée « Tables » (ListObject)

Si vos données sont des Tables (Ctrl+T), ciblez la colonne par son nom plutôt que par la lettre ; c’est plus robuste aux insertions de colonnes.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lo As ListObject, dispCol As ListColumn
    On Error GoTo ExitHere

    Set lo = Me.ListObjects("tblTrailers")                          ' nom de votre Table
    Set dispCol = lo.ListColumns("Dispatched")                       ' nom de la colonne booléenne
    If Intersect(Target, dispCol.DataBodyRange) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Or Target.Value <> True Then Exit Sub

    Application.EnableEvents = False
    Dim completed As Worksheet
    Set completed = ThisWorkbook.Sheets("Completed")
    Me.Rows(Target.Row).Copy completed.Cells(completed.Rows.Count, 1).End(xlUp).Offset(1)
ExitHere:
    Application.EnableEvents = True
End Sub

Variante sans VBA (Excel 365)

Si la feuille n’a pas besoin d’interactivité temps réel, Power Query offre une consolidation fiable et maintenable.

1. Convertissez chaque liste en Table (Ctrl+T) et ajoutez une colonne [Dispatched] booléenne.
2. Onglet Données → Obtenir des données → À partir d’une table/plage pour chaque table (elles deviennent des requêtes).
3. Dans l’éditeur Power Query : Accueil › Combiner › Ajouter des requêtes pour empiler toutes les tables.
4. Filtrez la colonne [Dispatched] sur TRUE, puis Accueil › Fermer & Charger vers la feuille « Completed ».
5. Rafraîchissez à la demande (Données › Actualiser) ou automatiquement à l’ouverture du classeur (Propriétés de la requête).

Avantages Power Query : reproductibilité, audit des étapes, aucune macro. Limite : pas de déclenchement instantané au clic de la case ; vous devez rafraîchir.

Bloc « tout‑en‑un » réutilisable

Pour éviter de répéter la logique dans plusieurs gestionnaires d’événements, placez ces utilitaires dans un module standard (Alt+F11 → Insertion › Module) et appelez‑les depuis vos Worksheet_Change / Workbook_SheetChange.

' --- Module: RowMover.bas ---

Public Sub MoveRow(ByVal shSource As Worksheet, ByVal rowIndex As Long, _
                   ByVal destSheetName As String, _
                   Optional ByVal deleteSource As Boolean = False, _
                   Optional ByVal valuesOnly As Boolean = False, _
                   Optional ByVal sourceSheetColInDest As Variant)
    Dim wsDest As Worksheet, nextDest As Range, lastCol As Long

    Set wsDest = EnsureSheet(destSheetName)
    lastCol = shSource.Cells(rowIndex, shSource.Columns.Count).End(xlToLeft).Column
    Set nextDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, lastCol)

    If valuesOnly Then
        nextDest.Value = shSource.Rows(rowIndex).Resize(1, lastCol).Value
    Else
        shSource.Rows(rowIndex).Resize(1, lastCol).Copy nextDest
    End If

    ' Option: tracer la feuille d'origine dans une colonne de la destination
    If Not IsMissing(sourceSheetColInDest) Then
        wsDest.Cells(nextDest.Row, sourceSheetColInDest).Value = shSource.Name
    End If

    If deleteSource Then shSource.Rows(rowIndex).Delete
End Sub

Public Function EnsureSheet(ByVal name As String) As Worksheet
    On Error Resume Next
    Set EnsureSheet = ThisWorkbook.Worksheets(name)
    On Error GoTo 0
    If EnsureSheet Is Nothing Then
        Set EnsureSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        EnsureSheet.Name = name
    End If
End Function

Exemple d’appel dans un événement feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Columns("H")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Or Target.Value <> True Then Exit Sub

    On Error GoTo ExitHere
    Application.EnableEvents = False
    MoveRow Me, Target.Row, "Completed", True, True, "Z"   ' supprime la source, colle valeurs, trace en Z
ExitHere:
    Application.EnableEvents = True
End Sub

Sélectionner le bon type de contrôle

Type	Avantages	Inconvénients	Recommandation
Contrôle de formulaire	Léger, portable, simple, lié à une cellule	Moins de propriétés que ActiveX	Idéal pour piloter un Worksheet_Change basé sur TRUE/FALSE
Contrôle ActiveX	Événements propres, plus de propriétés	Sensibilité aux versions/patchs, redistribution plus délicate	À éviter pour un simple déclenchement booléen

Diagnostics & fiabilisation

• Journaliser les actions (ex. écrire date/heure et action dans une feuille « Log »).
• Bloquer l’écran lors d’opérations multiples : Application.ScreenUpdating = False… True.
• Protéger la feuille en autorisant les modifications de la colonne des cases uniquement.
• Tester sur copie du classeur avec des données factices avant de passer en production.

FAQ rapide

Faut‑il activer les macros ? Oui, les variantes VBA reposent sur les événements. En alternative, Power Query fonctionne sans macro mais ne réagit pas instantanément.

Peut‑on déplacer vers plusieurs cibles selon le statut ? Oui : utilisez une table de correspondance (ex. TRUE → Completed, FALSE → Master) dans l’événement.

Et si plusieurs cases existent sur la ligne ? Filtrez la colonne précise via Intersect(Target, Columns("H")) ou ciblez par nom en mode Table ListObject.

Variante sans VBA (Excel 365) — pas à pas détaillé

1. Sur chaque feuille source, sélectionnez la plage de données → Ctrl+T pour créer une Table nommée (ex. tblTrailerLog, tblMaster).
2. Ajoutez une colonne Dispatched de type booléen. Les cases à cocher Excel (formulaires) peuvent écrire TRUE/FALSE dans cette colonne.
3. Onglet Données → À partir d’une table/plage (Power Query) pour exposer chaque Table comme requête.
4. Dans Power Query : Accueil → Combiner → Ajouter des requêtes pour empiler les différentes tables.
5. Appliquez un filtre sur Dispatched = TRUE, puis Fermer & Charger vers une nouvelle feuille nommée Completed.
6. Réglages d’actualisation : Propriétés de la requête → rafraîchir à l’ouverture et/ou toutes les X minutes selon le besoin.

Résumé opérationnel

• Copie : Worksheet_Change déclenché sur la colonne de la case à cocher.
• Suppression : Rows(Target.Row).Delete juste après la copie.
• Multi‑feuilles : dupliquer la routine ou centraliser dans Workbook_SheetChange.
• Annulation : second handler sur la feuille cible (+ colonne cachée si nécessaire).
• Alternative no‑code : Tables + Power Query.

---

Exemples complets prêts à l’emploi

Exemple A — Feuille unique, déplacement avec suppression

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Columns("H")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Or Target.Value <> True Then Exit Sub

```
On Error GoTo ExitHere
Application.EnableEvents = False

With ThisWorkbook.Sheets("Completed Trailer Log")
    Dim lastCol As Long, dest As Range
    lastCol = Me.Cells(Target.Row, Me.Columns.Count).End(xlToLeft).Column
    Set dest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, lastCol)
    dest.Value = Me.Rows(Target.Row).Resize(1, lastCol).Value   ' valeurs seules (rapide)
End With

Me.Rows(Target.Row).Delete
```

ExitHere:
Application.EnableEvents = True
End Sub 

Exemple B — Centralisé, traçabilité + annulation possible

' ThisWorkbook
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Completed" Then Exit Sub
    If Intersect(Target, Sh.Columns("H")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Or Target.Value <> True Then Exit Sub

```
On Error GoTo ExitHere
Application.EnableEvents = False

Dim wsDest As Worksheet, nextRow As Long, lastCol As Long
Set wsDest = EnsureSheet("Completed")
lastCol = Sh.Cells(Target.Row, Sh.Columns.Count).End(xlToLeft).Column

nextRow = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
wsDest.Cells(nextRow, 1).Resize(1, lastCol).Value = Sh.Rows(Target.Row).Resize(1, lastCol).Value
wsDest.Cells(nextRow, "Z").Value = Sh.Name ' SourceSheet

Sh.Rows(Target.Row).Delete
```

ExitHere:
Application.EnableEvents = True
End Sub

' Feuille "Completed"
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Columns("H")) Is Nothing Then Exit Sub
If Target.CountLarge > 1 Then Exit Sub

```
On Error GoTo ExitHere
Application.EnableEvents = False

If Target.Value = False Then
    Dim origin As String
    origin = Me.Cells(Target.Row, "Z").Value
    If Len(origin) = 0 Then origin = "Master"

    Dim w As Worksheet
    Set w = ThisWorkbook.Sheets(origin)

    Dim lastCol As Long
    lastCol = Me.Cells(Target.Row, Me.Columns.Count).End(xlToLeft).Column

    w.Cells(w.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, lastCol).Value = _
        Me.Rows(Target.Row).Resize(1, lastCol).Value

    Me.Rows(Target.Row).Delete
End If
```

ExitHere:
Application.EnableEvents = True
End Sub

' Module standard
Public Function EnsureSheet(ByVal nm As String) As Worksheet
On Error Resume Next
Set EnsureSheet = ThisWorkbook.Worksheets(nm)
On Error GoTo 0
If EnsureSheet Is Nothing Then
Set EnsureSheet = ThisWorkbook.Worksheets.Add
EnsureSheet.Name = nm
End If
End Function 

Exemple C — Tables (ListObject), colonne nommée

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lo As ListObject, col As ListColumn
    On Error GoTo Out
    Set lo = Me.ListObjects("tblDispatch")
    Set col = lo.ListColumns("Dispatched")

```
If Intersect(Target, col.DataBodyRange) Is Nothing Then GoTo Out
If Target.CountLarge > 1 Or Target.Value <> True Then GoTo Out

Application.EnableEvents = False
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Completed")
Me.Rows(Target.Row).Copy ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1)
```

Out:
Application.EnableEvents = True
End Sub 

---

Checklist de mise en service

• Les cases à cocher sont‑elles bien liées ? (Clic droit → Format de contrôle › Cellule liée).
• La colonne surveillée correspond‑elle à vos cases (ex. H) ?
• La feuille destination existe‑t‑elle et son nom est‑il exact ?
• Le code réactive toujours Application.EnableEvents = True (même en cas d’erreur) ?
• En cas de Tables, le nom de la table/colonne est‑il correct ?

Performance et maintenance

• Valeurs vs formules : coller les valeurs est plus rapide et évite les références brisées.
• Éviter les Select/Activate : utilisez les objets Range directement.
• Modularisation : déporter la logique de copie/suppression dans un module réutilisable (cf. RowMover.bas).
• Évolutivité : pour >50 k lignes, privilégiez Power Query et/ou un modèle de données.

---

Avec ces recettes, vous pouvez déclencher des flux complétés en un clic de case, fiabiliser la traçabilité, et garder un filet de sécurité pour revenir en arrière. Le tout, sans clickodrome et sans scripts labyrinthiques.