Microsoft 365 Macro pour split de données sur onglet nommé

[Arnaud Legay]

Bonjour à toutes et tous,

Toujours totalement novice en la matière, je tâtonne et m'exerce pas à pas.

Je cherche à faire une macro qui me permettrait qu'une fois après avoir saisi "au kilomètre" (lignes par lignes) les heures de mes employés sur mon onglet appelé "MATRICE-SAISIE", cette macro me créé un onglet par employé avec le nom situé dans la colonne "B", sans dupliquer si déjà existant, et me colle les données de chacun de ces employés. Le tout sur un onglet avec le modèle de mise en forme de mon onglet "MODELE".

Deux options, selon la complexité de ma demande :
- soit le saisi tout pour le mois, et en fin de mois on split le tout en une seule fois (mais dans ce cas il faudra que je rajoute un max de lignes),
- soit (idéalement), après chaque saisie, on split au fur et à mesure et les données se répartissent dans les bons onglets de chaque employé, créé les onglets si pas existants et vide mon onglet de saisie.

A dispo si questions.

Un grand merci d'avance.

 

[vgendron]

Bonjour

En PJ une proposition par macro
1) mettre les données dans des tableaux structurés
ca evite d'avoir des formules tirées sur 300 lignes vides
à chaque saisie de donnée SOUS le tableau, celui ci s'adapte automatiquement
la nouvelle ligne de données est donc intégrée (et prise en compte) dans le tableau
les formules sont recopiées automatiquement

2) bouton "Ventiler" pour dispatcher les lignes dans les différentes feuilles

regarde le code: j'ai mis deux macros: "sub Ventiler" et "function FeuilleExiste"

 

[Arnaud Legay]

Bonjour @vgendron,
Génial, c'est tout à fait cela que je cherchais.

J'aurais cependant deux ou trois petites modif si c'est possible stp ?
1. Vider la MATRICE-SAISIE une fois que c'est ventilé,
2. J'aimerai rajouter sous le titre du modèle une cellule fusionnée qui est égale au nom/prénom de l'employé (je l'ai fait dans ta version mais cela me provoque un bug "#PROPAGATION") à chaque fois que je demande la ventilation des données
3. Masquer dans le modèle la colonne B (NOM) car une fois ventilé et que le nom apparait sous le titre, je n'ai pas besoin que cela soit répété à chaque ligne.

Un grand merci à toi pour ton aide, c'est vraiment top !

 

[sylvanu]

Bonjour Arnaud, Vgendron,
J'arrive un peu tard, mais c'est fait ... je livre.

 

[Arnaud Legay]

Bonjour, et merci @sylvanu,
Ta version se rapproche de ce que je cherche. @vgendron est plus complet (désolé).
Dans sa version je peux spliter plusieurs fois sans que les données soient écrasées, elles se suivent.
Et si jamais, je viens tout juste de lui demander quelques améliorations.

Je n'avais pas l'habitude d'utiliser ce type de forum pour demander de l'aide et c'est vraiment cool, merci beaucoup.

 

[vgendron]

salut Sylvanu

ici nouvelle version
après ventilation, la feuille de saisie est vidée
pour le nom prénom, pas sur d'avoir compris: vu qu'il n'y a que le prénom dans tes tableaux
mais, j'ai ajouté en E3

pour la colonne B: je ne sais pas encore s'il vaut mieux
1) la masquer==> est ce qu'une prochaine ventilation sera toujours opérationnelle?
2) la supprimer==> la prochaine ventilation ne sera plus opérationnelle car différents tableaux
3) ne pas la mettre dès le début..==> modifier le code au moment de la copie.

 

[vgendron]

Question: les colonnes vides C et D masquées.. c'est volontaire?

 

[job75]

Bonjour à tous,

Une autre solution, elle n'utilise pas de feuille MODELE :

Private Sub CommandButton1_Click()
Dim w As Worksheet, d As Object, P As Range, i&, nom$, j&
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'---RAZ---
For Each w In Worksheets
    If w.Name <> Me.Name Then w.Delete
Next
'---création---
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
Set P = Range("A5:K" & Range("B" & Rows.Count).End(xlUp).Row)
If P.Rows.Count < 3 Then Exit Sub
For i = 3 To P.Rows.Count
    nom = CStr(P(i, 2))
    If nom <> "" And Not d.exists(nom) Then
        d(nom) = ""
        If FilterMode Then ShowAllData 'si la feuille est filtrée
        Set w = Worksheets.Add(After:=Sheets(Sheets.Count))
        w.Name = nom 'nomme la feuille
        Cells.Copy w.Cells 'copie tout
        w.Rows("7:" & Rows.Count).Delete 'RAZ
        P.Offset(1).AutoFilter 2, nom 'filtre automatique
        P.Copy w.[A5]
    End If
Next
AutoFilterMode = False 'retire le filtre
'---classement des feuilles---
For i = 2 To Sheets.Count - 1
    For j = i + 1 To Sheets.Count
        If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i)
Next j, i
Me.Activate
End Sub

A+

 

[vgendron]

dans cette version
le modèle n'a plus la colonne "Personne"
==> le code est donc modifié pour coller les éléments de la ligne hors formules
j'ai laissé les colonnes 1 et 2 affichées pour ne pas les oublier

 

[Arnaud Legay]

Question: les colonnes vides C et D masquées.. c'est volontaire?

oui car le vais copier coller les données depuis un autre document qui a d'autres colonnes que je ne vais pas utiliser ;-)

 

[Arnaud Legay]

Bonjour à tous,

Une autre solution, elle n'utilise pas de feuille MODELE :

Private Sub CommandButton1_Click()
Dim w As Worksheet, d As Object, P As Range, i&, nom$, j&
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'---RAZ---
For Each w In Worksheets
    If w.Name <> Me.Name Then w.Delete
Next
'---création---
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
Set P = Range("A5:K" & Range("B" & Rows.Count).End(xlUp).Row)
If P.Rows.Count < 3 Then Exit Sub
For i = 3 To P.Rows.Count
    nom = CStr(P(i, 2))
    If nom <> "" And Not d.exists(nom) Then
        d(nom) = ""
        If FilterMode Then ShowAllData 'si la feuille est filtrée
        Set w = Worksheets.Add(After:=Sheets(Sheets.Count))
        w.Name = nom 'nomme la feuille
        Cells.Copy w.Cells 'copie tout
        w.Rows("7:" & Rows.Count).Delete 'RAZ
        P.Offset(1).AutoFilter 2, nom 'filtre automatique
        P.Copy w.[A5]
    End If
Next
AutoFilterMode = False 'retire le filtre
'---classement des feuilles---
For i = 2 To Sheets.Count - 1
    For j = i + 1 To Sheets.Count
        If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i)
Next j, i
Me.Activate
End Sub

A+

Bonjour @job75
Merci pour ta proposition.
J'aime bien ta version, ça simplifie effectivement, mais :
- j'aimerai que la MATRICE-SAISIE se vide à la fin de la macro,
- il y a un soucis dans les colonnes des feuilles créées :
* Dépl. et tips 2x,
* manque colonne heure début
* manque colonne lieux
* je souhaite masquer la colonne personne
* rajouter si possible sous le titre des feuilles créées en E3 le nom de la personne correspondant à la feuille
- si je relance la macro après avoir vidé la MATRICE, tout se supprime, alors que j'aimerai que les choses s'ajoute au fur et à mesure.....

Merci pour ton aide

 

[Arnaud Legay]

dans cette version
le modèle n'a plus la colonne "Personne"
==> le code est donc modifié pour coller les éléments de la ligne hors formules
j'ai laissé les colonnes 1 et 2 affichées pour ne pas les oublier

Tu es à bout touchant de ce que j'espère ;-)
Je t'ai mis en pièce jointe ton document avec les dernière modif à faire stp :
- supprimer les onglets en rouge (je ne voulais pas le faire au risque de tout chambouler),
- masquer ou supprimer les colonnes en rouge,
- fusionner/centrer la cellule en jaune sans dépasser le tableau,
- mettre la police en noir non gras plutôt qu'en blanc.
Et là je crois que c'est ok.
Un tout grand merci

 

[vgendron]

plutot que de te faire une solution clé en main je te donnes les pistes pour le faire toi meme.. on interviendra ensuite pour corriger si besoin
1) les onglets rouge : tu peux les supprimer, ils me servaient juste de sauvegarde au cas où
==> d'ailleurs, si tu regardes le code, tu verras qu'ils ne sont jamais utilisés
2) pour supprimer les colonnes en rouge
si j'ai bien compris la feuille "Matrice-Saisie" est issue d'une importation (copier coller d'un autre fichier)
le mieux est donc de supprimer les colonnes DANS le modèle
et ajuster le code pour ne copier que les données des colonnes utiles
si tu regardes le code, tu verras aussi que j'ai déjà fait le travail pour copier d'abord la colonne "Jour", et ensuite les colonnes 1 2 ...
puis la colonne Depl

==> il suffit donc de dupliquer et adapter ces lignes

3) pour fusionner, centrer==> utilise l'enregistreur de macro:
* tu lances l'enregistrement
* tu fusionnes et centre la zone que tu souhaites
* tu arretes l'enregistrement
et tu vas voir le code qui a été généré==> il suffit de l'adapter au besoin et de la placer au bon endroit dans la macro (là où le nom de la personne est mise en F3

4) pour la police noir/blanc:
en cliquant sur la table, l'onglet création / mettre sous forme de table: tu peux choisir le style que tu souhaites
et si ca ne suffit pas, tu modifies dans le modèle==> ce sera recopié

 

[Arnaud Legay]

plutot que de te faire une solution clé en main je te donnes les pistes pour le faire toi meme.. on interviendra ensuite pour corriger si besoin
1) les onglets rouge : tu peux les supprimer, ils me servaient juste de sauvegarde au cas où
==> d'ailleurs, si tu regardes le code, tu verras qu'ils ne sont jamais utilisés
2) pour supprimer les colonnes en rouge
si j'ai bien compris la feuille "Matrice-Saisie" est issue d'une importation (copier coller d'un autre fichier)
le mieux est donc de supprimer les colonnes DANS le modèle
et ajuster le code pour ne copier que les données des colonnes utiles
si tu regardes le code, tu verras aussi que j'ai déjà fait le travail pour copier d'abord la colonne "Jour", et ensuite les colonnes 1 2 ...
puis la colonne Depl

==> il suffit donc de dupliquer et adapter ces lignes

3) pour fusionner, centrer==> utilise l'enregistreur de macro:
* tu lances l'enregistrement
* tu fusionnes et centre la zone que tu souhaites
* tu arretes l'enregistrement
et tu vas voir le code qui a été généré==> il suffit de l'adapter au besoin et de la placer au bon endroit dans la macro (là où le nom de la personne est mise en F3

4) pour la police noir/blanc:
en cliquant sur la table, l'onglet création / mettre sous forme de table: tu peux choisir le style que tu souhaites
et si ca ne suffit pas, tu modifies dans le modèle==> ce sera recopié

Ok pas de souci, je vais faire tout cela, je ne voulais pas une formule clé en main, mais ne maitrisant pas encore vba, je ne voulais pas risquer de tout planter.
C'est parfait et merci pour tout.
Je vais faire mes essais et reviendrai vers toi si je rencontre des problème.
Super boulot et excellents conseils. merci

 

[job75]

- il y a un soucis dans les colonnes des feuilles créées

Ah oui je n'avais pas vu, il faut afficher les colonnes C et D avant de copier, fichier (2) :

Private Sub CommandButton1_Click()
Dim w As Worksheet, d As Object, P As Range, i&, nom$, j&
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'---RAZ---
For Each w In Worksheets
    If w.Name <> Me.Name Then w.Delete
Next
'---création---
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
Set P = Range("A5:K" & Range("B" & Rows.Count).End(xlUp).Row)
If P.Rows.Count < 3 Then Exit Sub
Columns("C:D").Hidden = False 'affiche
For i = 3 To P.Rows.Count
    nom = CStr(P(i, 2))
    If nom <> "" And Not d.exists(nom) Then
        d(nom) = ""
        If FilterMode Then ShowAllData 'si la feuille est filtrée
        Set w = Worksheets.Add(After:=Sheets(Sheets.Count))
        w.Name = nom 'nomme la feuille
        Cells.Copy w.Cells 'copie tout
        w.Rows("7:" & Rows.Count).Delete 'RAZ
        P.Offset(1).AutoFilter 2, nom 'filtre automatique
        P.Copy w.[A5]
        w.Columns("B:D").Hidden = True 'masque
        w.Columns(1).ColumnWidth = 29 'pour le logo
    End If
Next
Columns("C:D").Hidden = True 'masque
AutoFilterMode = False 'retire le filtre
'---classement des feuilles---
For i = 2 To Sheets.Count - 1
    For j = i + 1 To Sheets.Count
        If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i)
Next j, i
Me.Activate
End Sub

Edit : ajouté w.Columns(1).ColumnWidth = 29 'pour le logo

- j'aimerai que la MATRICE-SAISIE se vide à la fin de la macro,

Cette solution n'est pas faite pour ça et d'ailleurs pourquoi le faire ?