Scinder un fichier en plusieurs onglets selon un critère d'une colonne

[OTW]

Bonjour à tous,

Lecteur à mes heures de cet excellent forum où j'ai pu glaner quelques précieuses informations, je n'ai pas trouvé ici réponse à ma question et me lance donc dans une question.

Cf. fichier joint, j'ai un onglet avec différentes données.

La colonne "D" a une donnée qui est "Oui" ou "Non"

Je souhaiterai pouvoir scinder les "Oui" et les "non" dans deux onglets différents, et bien évidemment de manière automatique, afin de pouvoir modifier mon onglet "données" à loisir.

L'idée serait également d'avoir une méthode pour faire de même sur d'autres colonnes le cas échéant, avec plus de deux choix (Ex colonne "I", P1, P2, P3, etc....) et donc plus d'onglets créés au besoin.

D'avance merci pour vos conseils avisés.

Nota : totalement novice en VBA le cas échéant mais j'apprendrai !

Steph.

 

[vgendron]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Hello

avec ce code dans un module VBA

Sub separeOuiNon()

nb = Range("A" & Range("A:A").Count).End(xlUp).Row

For i = 2 To nb
    If UCase(Range("D" & i)) = "OUI" Then
        Rows(i).EntireRow.Copy Destination:=Sheets("OUI").Range("A" & Range("A:A").Count).End(xlUp).Offset(1, 0)
    Else
    Rows(i).EntireRow.Copy Destination:=Sheets("NON").Range("A" & Range("A:A").Count).End(xlUp).Offset(1, 0)
    End If
Next i
End Sub

Alt+F11 pour ouvrir VBA
dans l'explorateur projet à gauche, clic droit pour insérer un module
copier coller le code à droite dans la fenetre du module

et ... Créer deux onglets: OUI et NON

et voila ;-)

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Super... trop facile... quand on sait

J'essaye de comprendre le code....

Si je veux faire de même avec "x" données comme dans ma colonne "I", que dois-je modifier dans le code ?

 

[vgendron]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

oui pardon.. tu as pourtant précisé "totalement novice en VBA".

je te refais la meme. avec des commentaires

range("A:A").count

te donne le nombre maxi de lignes possible pour ta version d'excel (1048576 pour excel 2007: ou 65536 pour excel 2003)

range("A" & range("A:A").count)

c'est la dernière cellule de l'onglet

range("A" & range("A:A").count).end(xlup)

te permet de remonter jusqu'à la première ligne non vide rencontrée par Excel.. c'est donc la dernière ligne de ton tableau

range("A" & range("A:A").count).end(xlup).row

récupère donc le numéro de cette fameuse ligne: dans ton exemple 194

For i = 2 To nb

boucle de la ligne 2 à la ligne nb (194)

If UCase(Range("D" & i)) = "OUI" Then

si la valeur en colonne D ligne i = OUI (UCASE permet de s'affranchir des problèmes de majuscules - minuscules)
alors

rows(i).entirerow.copy

copie de toute la ligne
et collage direct (destination:=)
dans la feuille OUI à la première ligne vide du tableau
meme principe que toute à l'heure. à partir du bas; on remonte jusqu'à la première ligne NON vide..
avec un offset de 1 ligne pour retomber sur la première ligne vide

sinon (si c'est pas OUI, alors c'est NON)
meme commande, mais dans la feuille NON

pour la suite.
tu peux repartir des onglets OUI et NON en testant le contenu de la colonne I

If UCase(Range("I" & i)) = "P4" Then

ou alors dès l'onglet de base tu fais un test sur la colonne D ET la colonne I

If (UCase(Range("D" & i)) = "OUI") and (UCase(Range("I" & i)) = "P4") Then

 

[vgendron]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

sinon, il y a aussi surement plus simple..
dans l'onglet de base, tu actives les filtres que tu souhaites
puis tu fais un copier coller du tableau filtré dans un nouvel onglet

ou encore. TCD.. mais ca.. c'est pas pour moi.. rien que le mot me fait peur ;-)

 

[vgendron]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

exemple
Tu actives les filtres OUI sur la colonne D, et P1 sur la colonne I

tu as donc 41 lignes restantes
pour les sélectionner en VBA
avant la ligne de code suivante, il faudrait pouvoir definir le nom de l'onglet (NomFiltre) dans lequel tu vas copier le résultat du filtre

Range("A1").CurrentRegion.Copy Destination:=Sheets(Nomfiltre)

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

J'ingurgite tout ça et essaye de le réaliser....
merci !

 

[Staple1600]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Bonsoir à tous

Je recycle une précédente de "mes" propositions dans un fil de septembre 2014
Ici la macro SplitData crée autant d'onglets qu'il y a de de de Pilote Dossier (soit 5 onglets dans cet exemple)
Reste çà traiter les OUI/NON
Faut-il imaginer quil faille des onglets sur le schéma ci-dessous ?
P1_OUI et P1_NON, P2_OUI et P2_NON et etc ... ?

Sub SplitData()
'EDITION version modifiée pour copier la ligne d'entête
'code initial d'Alex P. ->-> stackoverflow
   Dim DataMarkers(), Names As Range, name As Range, n As Long, i As Long

    Set Names = Range(Cells(2, "I"), Cells(Rows.Count, "I").End(xlUp))
    n = 0
    DeleteWorksheets

    For Each name In Names
        If name.Offset(1, 0) <> name Then
            ReDim Preserve DataMarkers(n)
            DataMarkers(n) = name.Row
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = name
            n = n + 1
        End If
    Next name
'
   For i = 0 To UBound(DataMarkers)
        If i = 0 Then
        Worksheets(1).Range("A1:I1").Copy Destination:=Worksheets(i + 2).Range("A1")
        Worksheets(1).Range("A2:I" & DataMarkers(i)).Copy Destination:=Worksheets(i + 2).Range("A2")
        Else
        Worksheets(1).Range("A1:I1").Copy Destination:=Worksheets(i + 2).Range("A1")
        Worksheets(1).Range("A" & (DataMarkers(i - 1) + 1) & ":R" & DataMarkers(i)).Copy Destination:=Worksheets(i + 2).Range("A2")
        End If
    Next i
End Sub

Sub DeleteWorksheets()
    Dim ws As Worksheet, activeShtIndex As Long, i As Long
    activeShtIndex = ActiveSheet.Index
    Application.DisplayAlerts = False
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        If i <> activeShtIndex Then
            Worksheets(i).Delete
        End If
    Next i
    Application.DisplayAlerts = True
End Sub

 

[vgendron]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Hello Stapple

Justement, mon idée

avant la ligne de code suivante, il faudrait pouvoir definir le nom de l'onglet (NomFiltre) dans lequel tu vas copier le résultat du filtre

était de créer autant d'onglets sur le schéma dont tu parles..

il "suffirait" de récuperer toutes les entetes des filtres actifs.. de les concaténer, et d'en faire le NomFiltre..
d'où la question que je me pose. est il possible par VBA d'aller chercher tous ces fameux filtres actifs.. je suppose que oui..

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

oulaaaa pour un néophyte tel que moi...
sur la proposition de vgendron, j'avais deux autres questions si je peux abuser un peu… la réponse de staple doit y répondre partiellement :

1° Comment rajouter d’autres choix de scission ?
Ici, cela fonctionne avec Oui et Non.
Pour la colonne « I », j’ai « P1 », « P2 », « P3 », etc…
En créant autant d’onglets que de « Px », quel code rajouter ?
J’ai bêtement rajouté, après avoir changé la colonne dans les autres données, une condition telle que ci-dessous dans le texte :


1. Sub separeOuiNon()

nb = Range("A" & Range("A:A").Count).End(xlUp).Row

For i = 2 To nb
If UCase(Range("I" & i)) = "P1" Then
Rows(i).EntireRow.Copy Destination:=Sheets("P1").Range("A" & Range("A:A").Count).End(xlUp).Offset(1, 0)
If UCase(Range("I" & i)) = "P2" Then
Rows(i).EntireRow.Copy Destination:=Sheets("P2").Range("A" & Range("A:A").Count).End(xlUp).Offset(1, 0)

Else
Rows(i).EntireRow.Copy Destination:=Sheets("P3").Range("A" & Range("A:A").Count).End(xlUp).Offset(1, 0)
End If
Next i
End Sub

Ça ne fonctionne pas (évidemment me dira le connaisseur)

2° Mon onglet de base évolue au fil des jours et est issu d’une base de données.
L’idée est de recopier celle-ci dans cet onglet et de pouvoir refaire les tris automatiques.
En dehors de rouvrir VBA et de faire F5, je n’ai pas trouvé plus « pratique ».
Une fonction directe dans le fichier Excel lui-même existe-t-elle ?
Merci (encore)

 

[Staple1600]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Bonsoir à tous

OTW

Pour la colonne « I », j’ai « P1 », « P2 », « P3 », etc…
En créant autant d’onglets que de « Px », quel code rajouter ?

Apparemment tu n'as pas testé ma proposition, non ?
Car le code VBA que j'ai précédemment soumis fait justement cela ...

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

non non staple, pas testé... en transit à l'aéroport et vois ces nouveaux messages...
je teste tout cela
merci

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

vive l'avion, j'ai pu tester :- )
Ça fonctionne très bien, merci Staple1600.
Il serait intéressant d’avoir en effet une subdivision entre la colonne « I » P1, P2, etc… et la colonne « D » oui / non
Par ailleurs, sur le schéma 1 de tri sur la colonne des P1, P2, etc… cela fonctionne uniquement si j’ai une suite de P : 1, 2, 3, etc…
J’avais mis pour l’exemple des « P » mais mon tableau doit fonctionner avec des noms et prénoms (ex : Marie D, Pierre B, Fred M, etc…) et il n’y a alors plus de suite
Soit il est possible de le faire dès qu’un nom différent est rencontré, soit je connais les noms par avance et peux rajouter ceux-ci dans le code
Un bouton (objet) est-il possible sur la page des données pour exécuter la macro SplitData ou doit on passer par les boites de dialogue ?

 

[Staple1600]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

Re

Un bouton (objet) est-il possible sur la page des données pour exécuter la macro SplitData ou doit on passer par les boites de dialogue ?

Oui c'est possible
Comme il est possible que tu trouves dans les archives du forum des exemples de lancement de macros par le biais de bouton.
Pour y accéder afin de t'en inspirer, il suffit de cliquer sur le loupe en haut à droite de l'écran

 

[OTW]

Re : Scinder un fichier en plusieurs onglets selon un critère d'une colonne

c'est bien noté pour les boutons. c'est en forgeant, etc.... ;-)

Par contre, pour faire un double tri, je sèche (comme indiqué en introduction, je ne connais pas VBA)

Egalement, je n'ai pas trouvé pour que le tri se fasse non par une suite (P1, P2, P3), mais avec des données non cohérentes entre elles (Pierre, Jacques, Maie, Christelle, etc...)