Discussion :

[Elumastebit]

Bonsoir à toutes et à tous,

Je sollicite votre aide une fois de plus car là, je bloque complètement.

Je dispose d'un classeur Excel contenant une dizaine de feuilles, une par client.
Chaque feuille contient le même tableau, seul le contenu des cellules changent en fonction des commandes du client (feuille1 client1; feuille2 client2; etc.).

Dans une dernière feuille, je souhaiterait disposer du même tableau mais récapitulatif de tous les autres. Avec un filtre je pourrais alors sélectionner toutes les commandes pour une date choisie.

Ce tableau doit être mis à jour automatiquement dès qu'une ligne est ajoutée sur l'un des tableaux clients.

En espérant avoir été clair et en attendant votre aide avec impatience, Merci!

Elumastebit

[Pierre Fauconnier]

Bonsoir

Sans vba, pas de salut, à mon avis...

Avec VBA et un peu de mise en place...

1. Nommer les feuilles contenant des données clients de façon à pouvoir les identifier par code (par exemple, Cli_xxx)
2. Nommer la feuille récapitulative

Placer le code suivant dans un module standard

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Consolidation()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
 
    Set shCible = Worksheets("Recap")
 
    ' Vidange de la feuile récap
    shCible.Range(shCible.Cells(2, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    For Each shSource In Worksheets
        If Left(shSource.Name, 4) = "Cli_" Then _
            shSource.Range(shSource.Range("a2"), shSource.Cells.SpecialCells(xlCellTypeLastCell)).Copy Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(2)
    Next
End Sub

Ce code fonctionne pour autant que les feuilles aient une structure identique, car c'est l'entièreté de la plage utilisée de la feuille qui est copiée dans le récapitulatif...

Il est préférable d'éviter la mise à jour du récapitulatif à chaque changement dans une feuille. J'ai choisi de réaliser la mise à jour lors de l'activation de la feuille récapitulative, en plaçant ce code dans le module de la feuille

Code :

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

1
2
3
Private Sub Worksheet_Activate()
    Consolidation
End Sub

[Elumastebit]

Bonjour,

Merci Pierre ça marche impeccable, j'ai juste modifié quelques valeurs vu que les données de mon tableau ne commençait pas en a2.

J'aimerai également créer un autre récapitulatif mais cette fois si, seules les comandes clients non traitées seraient affichées dans ce tableau. Pour cela il suffit que les cellules "AIi" de chaque feuille soient vides ca donnerait un truc du genre:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Consolidation2()
    Dim shSource As Worksheet
    Dim shCible2 As Worksheet
 
    Set shCible2 = Worksheets("En cours")
    
    ' Vidange de la feuile En cours
    shCible2.Range(shCible2.Cells(6, 1), shCible2.Cells(Rows.Count, Columns.Count)).ClearContents
    
    For Each shSource In Worksheets
     If Left(shSource.Name, 3) = "Cl_"  et si colonne AI=vide Then _
            shSource.Range(shSource.Range("a6"), shSource.Cells.SpecialCells(xlCellTypeLastCell)).Copy Destination:=shCible2.Range("a" & Rows.Count).End(xlUp)(3)
    Next
End Sub

Pouvez-vous me dépannez svp?

Merci encore pour votre aide.

Elumastebit

Re,

J'ai essyé en rajoutant:

Code :

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

1
2
3
4
For Each shSource In Worksheets
        If Left(shSource.Name, 3) = "Cl_" And shSource.Columns(AI) = "" Then _
            shSource.Range(shSource.Range("a6"), shSource.Cells.SpecialCells(xlCellTypeLastCell)).Copy Destination:=shCible2.Range("a" & Rows.Count).End(xlUp)(3)
    Next

Résultat erreur 1004 <erreur définie par l'application ou par l'objet>

Si quelqu'un à une idée.

Merci.

Elumastebit

[Pierre Fauconnier]

Bonsoir

Ma première solution fonctionne lorsque l'on peut prendre toutes les données sans devoir utiliser un critère.

A partir du moment où un critère doit être appliqué, on peut appliquer deux solutions:
1. Passer les données ligne par ligne en appliquant un critère (coûteux en temps car il faut boucler sur toutes les lignes de chaque feuille et transférer ligne par ligne.

2. Passer tout en bloc comme dans ma première solution, puis éliminer après avec un tri. Voici un code qui fait cela (je n'ai pas adapté à tes données, reviens si pb)

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
Sub ConsolidationCommandesNonTraitees()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
    Dim Plage As Range
    Set shCible = Worksheets("Recap")
 
    ' Vidange de la feuile récap
    shCible.Range(shCible.Cells(2, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    ' Copie des données en bloc
    For Each shSource In Worksheets
        If Left(shSource.Name, 4) = "Cli_" Then _
            shSource.Range(shSource.Range("a2"), shSource.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
                Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(2)
    Next
 
    ' Tri et suppression des commandes traitées dans la feuille récap
    Set Plage = shRecap.Range("a1:d" & shRecap.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("d1"), order1:=xlAscending, header:=xlYes
    shRecap.Rows("2:" & shRecap.Range("d" & Rows.Count).End(xlUp).Row).Delete
End Sub

[Elumastebit]

Erreur d'éxécution 424 Objet requis et le débogueur pointe sur la ligne:

Code :

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

 Set Plage = shRecap.Range("a1:d" & shRecap.Range("a" & Rows.Count).End(xlUp).Row)

Au passage de la souris sur "set plage" il me met: "plage=nothing"

Alors ok il y a rien dans la plage mais...?

Merci

[Pierre Fauconnier]

Désolé, au temps pour moi. C'est une erreur de recopie de code...

Voici les lignes correctes

Code :

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

1
2
3
4
    ' Tri et suppression des commandes traitées dans la feuille récap
    Set Plage = shCible.Range("a1:d" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("d1"), order1:=xlAscending, header:=xlYes
    shCible.Rows("2:" & shCible.Range("d" & Rows.Count).End(xlUp).Row).Delete

[Elumastebit]

Il ya une chose que je ne comprend pas dans ton code: où ce situe le critère de tri (pour moi il s'agit du contenu des cellule de la colonne AI)?

[Pierre Fauconnier]

Je n'ai pas la structure de ton fichier, donc il faut que tu adaptes.

Tu m'as parlé d'une cellule vide qui était l'indice... Dans mon code, cette cellule est la colonne D. A toi de modifier avec AI, comme par exemple dans le code suivant

Code :

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

1
2
3
4
    ' Tri et suppression des commandes traitées dans la feuille récap
    Set Plage = shCible.Range("a1:ai" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("ai1"), order1:=xlAscending, header:=xlYes
    shCible.Rows("2:" & shCible.Range("ai" & Rows.Count).End(xlUp).Row).Delete

On trie sur le critère avec Plage.Sort qui place en bas du fichier les lignes dont la cellule en AI est vide. Après, on supprime de la ligne 2 jusqu'à la ligne au dessus de la première cellule vide en AI
Mais dans ce code, je m'arrête à AI pour trier. Tu dois donc comprendre le code et l'adapter à ton cas, en étendant éventuellement la plage sur la ligne Set Plage... Sans ton fichier, je ne saurais pas faire un code qui fonctionne chez toi

[Elumastebit]

Décidément pas bien doué, je vais expliciter plus car si j'arrive à comprendre quelques parties, je bloque sur certains paramètres.

Les tableaux de chaque worksheet ont les mêmes entêtes et sont comprises sur les cellules de A1 à AJ5; les données commencent donc de A1 à AJi

Je souhaite qu'apparaissent sur la feuille récap, les lignes des feuilles "cli_" dont la colonne AJ est vide, et en plus dont les cellules de la colonne M sont renseignées "Non" (désolé j'avais oublié ce critère )

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
Sub ConsolidationCommandesNonTraitees()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
    Dim Plage As Range
    Set shCible = Worksheets("Recap")
 
    ' Vidange de la feuile récap
    shCible.Range(shCible.Cells(6, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    ' Copie des données en bloc
    For Each shSource In Worksheets
        If Left(shSource.Name, 4) = "Cli_" Then _
            shSource.Range(shSource.Range("a6"), shSource.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
                Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(3)
    Next
 
    ' Tri et suppression des commandes traitées dans la feuille récap
    Set Plage = shCible.Range("a6:aj" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("ai6"), order1:=xlAscending, Header:=xlYes
    shCible.Rows("2:" & shCible.Range("ai" & Rows.Count).End(xlUp).Row).Delete
 
End Sub

Merci une fois de plus!

Elumastebit

[Pierre Fauconnier]

Tu dis que les données commencent en A1 de tes feuilles, mais dans le code, tu fais démarrer la plage copiée en A6. Je suppose donc que les 5 premières lignes sont des entêtes ou autres fioritures, mais que les données à copier commencent en A6.



Pour copier les cellules, puisque tu connais ta dernière colonne, utilise plutôt ceci (c'est plus limpide quand tu reviendras dessus par la suite)

Code :

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

1
2
            shSource.Range(shSource.Range("a6:aj" & shSource.Range("a" & Rows.Count).Row)).Copy _
                Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(3)

Au passage, je ne comprends pas pourquoi le (xlUp)(3) au lieu de (xlUp)(2)qui va laisser un blanc pour séparer les données de chaque feuille, mais qui va de toute façon être mélangé lors du tri...

Après, au vu de tes deux critères, si il n'y a vraiment que ceux-là, on peut encore éviter de boucler en triant d'abord sur M en décroissant puis sur AJ en croissant. On devra alors supprimer de la ligne 6 à la ligne qui, en AJ, précède la première cellule vide.

Le code complet devrait être celui-ci

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
Sub ConsolidationCommandesNonTraitees()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
    Dim Plage As Range
 
    Set shCible = Worksheets("Recap")
 
    ' Vidange de la feuile récap
    shCible.Range(shCible.Cells(2, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    ' Copie des données en bloc
    For Each shSource In Worksheets
        If Left(shSource.Name, 4) = "Cli_" Then _
            shSource.Range(shSource.Range("a6:aj" & shSource.Range("a" & Rows.Count).Row)).Copy _
                Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(2)
    Next
 
    ' Tri et suppression des commandes traitées dans la feuille récap
    Set Plage = shCible.Range("a5:aj" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("m5"), order1:=xlDescending, key2:=Plage.Range("aj5"), order2:=xlAscending, header:=xlYes
    shCible.Rows("6:" & shCible.Range("aj5").End(xlDown).Row).Delete
End Sub

[Elumastebit]

Bonjour à tous, bonjour Pierre,

Oui tu as raison je me suis trompé, les données commencent en A6. (concernant le (xlup)(3) je trouve ça pas mal puisque ça laisse une ligne vide entre mes clients (meilleure lisibilité du tableau).

Sinon en collant ton code j'ai une erreur 1004: la méthode range de l'objet _worksheet à échoué

Voici la ligne pointée:

Code :

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

1
2
shSource.Range(shSource.Range("a6:aj" & shSource.Range("a" & Rows.Count).Row)).Copy _
                Destination:=shCible.Range("a" & Rows.Count).End(xlUp)(2)

Une fois de plus je ne comprend pas bien...tu n'effectue pas un tri du genre "si mi<>non alors supprimer" pour la colonne M comment il va savoir qu'il faut conserver que les lignes possédant non en colonne M?

Merci pour ton aide.

Elumastebit

[ouskel'n'or]

Et avec la méthode classique

Code :

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

1
2
shSource.Range("a6:aj" & shSource.Range("a65536").End(xlUp).Row).Copy _
shCible.Range("a" & shCible.Range("a65536").End(xlUp).Row + 1)

Pour un espace entre les copies, mets + 2
Bonne journée

[Elumastebit]

Avec cette méthode, les données sont collées en A2 au lieu de A6 et le tri n'est pas effectué puisque j'ai une ligne avec un "oui" en colonne M et plusieurs lignes avec une date entrée dans la colonne AI alors que que je devrai avoir que du "non" en M et rien "" en AI

De plus les ligne ne sont pas tirées par client et je ne comprend même pas comment elles sont triées.

Merci.

[Elumastebit]

Bonjour,

En réalité le code ne bloque pas. J'ai seulement des problèmes au niveau du copier coller et du tri.

En ce qui concerne la copie et le collage des données, ceci s'effectue à partir de la cellule a4, alors que mes données commence en a6 et que mon code (si je l'ai bien compris...) copie à partir de a6

Sur le collage mes données sont collées à partir de a2 alors que je souhaite sont collage à partir de a6 également, mais je ne vois pas où est ce paramètre dans le code

Et enfin sur le tri sur la colonne m ne marche pas puisque j'ai des lignes avec le paramètres "oui" qui apparaissent alors que je ne devrais avoir que les lignes avec le paramètre non. De même des lignes avec les celllules remplies de la colonne ai ne devraient pas apparaitre or elles y sont.

Voici mon 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
Sub Encours()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
    Dim Plage As Range
 
    Set shCible = Worksheets("encours")
 
    ' Vidange de la feuile encours
    shCible.Range(shCible.Cells(6, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    ' Copie des données en bloc
    For Each shSource In Worksheets
        If Left(shSource.Name, 1) = " " Then _
            shSource.Range("a6:aj" & shSource.Range("a65536").End(xlUp).Row).Copy _
shCible.Range("a" & shCible.Range("a65536").End(xlUp).Row + 1)
    Next
 
    ' Tri et suppression des commandes traitées dans la feuille encours
    Set Plage = shCible.Range("a5:aj" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("m5"), order1:=xlDescending, key2:=Plage.Range("ai5"), order2:=xlAscending, header:=xlYes
    shCible.Rows("6:" & shCible.Range("aj5").End(xlDown).Row).Delete
End Sub

Voila si vous arrivez à me débloquer ce serait génial.

Merci encore à vous.

Elumastebit

[ouskel'n'or]

Pour être certain de copier toutes les lignes de la plage de données je te conseille

Code :

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

1
2
3
4
5
    For Each shSource In Worksheets
        If Left(shSource.Name, 1) = " " Then _
            shSource.Range("A6:" & Split(shSource.UsedRange.Address, ":")(1)).Copy _
            shCible.Range("a6")
    Next

Mais tu peux ne corriger que la destination (a6)
Bonne journée

[Elumastebit]

Bonjour ouskel'n'or,

Du coup je n'ai plus aucune données qui apparait.

:S

Elumastebit

[ouskel'n'or]

En ne corrigeant que "A6" ?
Alors je vérifierais ce que contient la feuille source...

[Elumastebit]

J'ai remplacé mon morceau de code (la partie "copier") par le tien et je n'ai plus aucune donnée qui apparait. Quant à mes feuilles sources elles contiennent toujours des données...

[ouskel'n'or]

Montre ton code tel qu'il est

[Elumastebit]

Voila donc 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
Sub Encours()
    Dim shSource As Worksheet
    Dim shCible As Worksheet
    Dim Plage As Range
 
    Set shCible = Worksheets("encours")
 
    ' Vidange de la feuile encours
    shCible.Range(shCible.Cells(6, 1), shCible.Cells(Rows.Count, Columns.Count)).ClearContents
 
    ' Copie des données en bloc
    For Each shSource In Worksheets
        If Left(shSource.Name, 1) = " " Then _
            shSource.Range("A6:" & Split(shSource.UsedRange.Address, ":")(1)).Copy _
            shCible.Range("a6")
    Next
 
    ' Tri et suppression des commandes traitées dans la feuille encours
    Set Plage = shCible.Range("a5:aj" & shCible.Range("a" & Rows.Count).End(xlUp).Row)
    Plage.Sort key1:=Plage.Range("m5"), order1:=xlDescending, key2:=Plage.Range("ai5"), order2:=xlAscending, header:=xlYes
    shCible.Rows("6:" & shCible.Range("aj5").End(xlDown).Row).Delete
End Sub

Merci