XL 2013 Planning - alléger des cellules par du VBA

[ANTONY34200]

Bonjour le forum,
j'ai fait un petit fichier excel, afin de pouvoir plannifier des animations en magasin, en fournissant du personnel.

J'aimerais allégé par du VBA mes formules de l'onglet "Planning Animateur - annuel".
et j'aimerais pouvoir créer un onglet "Récap par semaine", qui récapitulerais par numéros de semaine, les jours, le magasin et le personnel. je ne sais pas si c'est faisable ...

Je vous joints mon fichier, merci d'avance pour le coup de main.

 

[Robert]

Bonjour Antony le Setoy, bonjour le forum,

Essaie avec l'événementielle Change ci-dessous à placer dans le composant Feuil2(Planning Animateur - annuel) :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim I As Byte 'déclare la variable I (Incrément)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim COL As Integer 'déclare la variable COL (COLonne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CR As Integer 'déclare la variable CR (Colonne de Référence)
Dim LR As Integer 'déclare la variable LR (Ligne de Référence)

If Target.Address <> "$D$1" Then Exit Sub 'si la changement a lieu ailleurs qu'en D1, sort de la procédure
For I = 1 To 12 'boucle 1 : sur les 12 mois
    Set O = Worksheets(CStr(I)) 'définit l'onglet O
    COL = O.Rows(6).Find(Target.Value, , xlValues, xlWhole).Column 'définit la colonne COL (recherche l'animateur dans la ligne 6 de l'onglet O)
    Set PL = O.Range(O.Cells(7, COL), O.Cells(37, COL)) 'définit la plage PL (tous les jours du mois)
    Select Case I 'agit en fonction de I
        Case 1 To 6 'de 1 à 6
            CR = 4 * I 'définit la colonne de référence (I x 4)
            LR = 3 'définit la ligne de référence
        Case 7 To 12 'de 7 à 12
            CR = (4 * I) - 24 'définit la colonne de référence (I x 4) - 24
            LR = 36 'définit la ligne de référence
    End Select 'fin de l'action en fonction de I
    For LI = 1 To PL.Rows.Count 'boucle 2 : sur toutes les lignes LI de la plage PL
        'si la donnée ligne LI colonne 1 de PL n'est pas vide, renvoie sa valeur dans la cellule ligne : LR + LI -1, colonne CR du planning
        If PL(LI, 1) <> "" Then Me.Cells(LR + LI - 1, CR).Value = PL(LI, 1).Value
    Next LI 'prochaine ligne de la boucle 2
Next I 'prochain mois de la boucle 1
End Sub

Le code agira chaque fois que tu changeras l'animateur en D1:Z1...

 

[ANTONY34200]

Bonjour Robert,
le code fonctionne, mais. Si je change le nom du personnel en cellule d1:z1, rien ne se passe ...

 

[Robert]

Re,

Ça fonctionne mais la séparation noire que tu as faite entre les mois de mars/avril et septembre/octobre fous le souk avec en plus des colonnes masquées...
Le code modifié :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim I As Byte 'déclare la variable I (Incrément)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim COL As Integer 'déclare la variable COL (COLonne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CR As Integer 'déclare la variable CR (Colonne de Référence)
Dim LR As Integer 'déclare la variable LR (Ligne de Référence)

If Target.Address <> "$D$1" Then Exit Sub 'si la changement a lieu ailleurs qu'en D1, sort de la procédure
For I = 1 To 12 'boucle 1 : sur les 12 mois
    Set O = Worksheets(CStr(I)) 'définit l'onglet O
    COL = O.Rows(6).Find(Target.Value, , xlValues, xlWhole).Column 'définit la colonne COL (recherche l'animateur dans la ligne 6 de l'onglet O)
    Set PL = O.Range(O.Cells(7, COL), O.Cells(37, COL)) 'définit la plage PL (tous les jours du mois)
    Select Case I 'agit en fonction de I
        Case 1
            CR = 4 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 2
            CR = 8 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 3
            CR = 12 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 4
            CR = 18 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 5
            CR = 22 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 6
            CR = 26 'définit la colonne de référence
            LR = 3 'définit la ligne de référence
        Case 7
            CR = 4 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
        Case 8
            CR = 8 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
        Case 9
            CR = 12 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
        Case 10
            CR = 18 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
        Case 11
            CR = 22 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
        Case 12
            CR = 26 'définit la colonne de référence
            LR = 36 'définit la ligne de référence
    End Select 'fin de l'action en fonction de I
    For LI = 1 To PL.Rows.Count 'boucle 2 : sur toutes les lignes LI de la plage PL
        'si la donnée ligne LI colonne 1 de PL n'est pas vide, renvoie sa valeur dans la cellule ligne : LR + LI -1, colonne CR du planning
        If PL(LI, 1) <> "" Then Me.Cells(LR + LI - 1, CR).Value = PL(LI, 1).Value
    Next LI 'prochaine ligne de la boucle 2
Next I 'prochain mois de la boucle 1
End Sub

Sélectionne l'Animateur 5 et tu verras apparaître Robert en janvier, février, mai et août...

 

[vgendron]

Hello le fil

je me suis amusé un peu.
1) j'ai crée un mois template vide
2) un bouton dans l'onglet paramètre permet d'ajouter autant de colonnes que d'animateurs dans le template vide et ensuite créer les 12 mois
3) quand tu changes l'animateur dans le planning annuel, une macro récupère les infos dans chaque mois pour les copier coller==>plus de formule

PS: c'est vrai que mettre des lignes masquées qui ont des formules ne servant pas à grand chose: ca fout le boxon et complexifie les codes

Si à l'avenir, tu as l'intention d'ajouter ou supprimer des animateurs, il suffirait d'une macro qui ajoute ou supprime une colonne dans tous les mois

 

[ANTONY34200]

Merci pour vos réponses
Robert,
la version que tu me propose ne supprime pas les informations de l'animateur 1, quand je sélectionne l'animateur 5 ... dommage ...

Vgendron,
la version que tu me proposes se rapproche un peu plus de se que je souhaitais ...
sauf que je ne comprends pas l'onglet template Mois ...
et que dans l'onglet paramètres, la liste des MT est destinée à être changé 1 fois. animateur 1 va prendre le vrai nom de l'animateur ... nous avons 25 animateurs tout au long de l'année, mais il se peux qu en cours d'année nous en rajoutions 2 ou 3 dans la liste, ils prendraient la place de animateur 26, animateur 27 ...
Là, quand j'ai mis le nom d'1 animateur à la place de animateur 1, sur les onglets 1, 2, 3... et template Mois rien ne se passe ... par contre dans l'onglet Planning Animateur - Annuel, dans la barre de défilement le vrai nom à bien été pris et du coup une fenêtre debogae s'ouvre

 

[vgendron]

Je me suis dit que
la liste des animateurs (nombre et noms) n'était pas connue au départ
je me suis aussi dit que tu allais sans doute changer Animateur 1 2 3 .. par leurs vrais noms avant de créer ton planning
d'ou la macro qui génère un template mois avec tous les animateurs (et leurs vrais noms)
ensuite.. vu le commentaire en ROUGE au dessus de la liste...la seule modif qui pourrait avoir lieu, c'est ajouter ou supprimer un animateur==> d'ou mon idée d'une macro qui ajoute/supprime dans chaque onglet (1 a 12)

à mon avis, ce n'est pas du tout une bonne idée d'avoir une liste de 40 animateurs (1 à 40) et à coté une autre liste qui associe le nom de l'animateur à un vrai nom.. et en plus modifier en cours de route.. je prédis de nombreux problèmes de pertes d'infos.
en gros. entre la liste colonnes L-M et la liste colonnes H-I: il faut choisir.

une fois que ta liste d'animateur est correctement définie (nombre et noms), tu cliques sur le bouton pour coller tous les noms dans l'onglet "template mois")
et ce template mois est ensuite copié collé 12 fois

si tu génères les 12 mois et qu'ENSUITE, tu modifies un nom d'animateur.. le code va planter puisqu'il ne va pas trouver l'animateur que tu viens de modifier

 

[vgendron]

ci jointe une nouvelle version qui permet de changer les noms dans la liste

 

[ANTONY34200]

Vgendron, c'est super,
oui normalement la liste d'animateur h-i ne devrait pas exister ... c’était un test que j'ai oublié d'effacer ... c'est effectivement la liste L-M qui sera la bonne ... concernant le nombre de 40 animateurs, était juste pour tester ... cette liste sera actualisé au fil du temps ... l'ajout sera seulement possible pas la suppression ...

Autre chose, plus complexe ... est il possible de faire une feuille avec une liste déroulante, avec les N° de semaine, de faire un récapitulatif Jour / Magasin / Animateur.
et sur la meme idée que le planning annuel par animateur, faire un récap par magasin ? sachant qu'un MEME magasin pour avoir CRF PARIS - GP et CRF PARIS SAPY, mais prendre en compte uniquement CRF paris ... je sais pas si je m'exprime bien ...

 

[Robert]

Bonjour le fil, bonjour le forum,

Robert,
la version que tu me propose ne supprime pas les informations de l'animateur 1, quand je sélectionne l'animateur 5 ... dommage ...

Désolé Antony, j'ai perdu ma boule de cristal sur la corniche et tes explications étaient tellement claires... Mais je te le concède, c'était on ne peut plus logique....
Le début du code modifié :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim PAE As Range 'déclare la variable PAE (Plage À Effacer)'<--- ajout ici
Dim I As Byte 'déclare la variable I (Incrément)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim COL As Integer 'déclare la variable COL (COLonne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CR As Integer 'déclare la variable CR (Colonne de Référence)
Dim LR As Integer 'déclare la variable LR (Ligne de Référence)

If Target.Address <> "$D$1" Then Exit Sub 'si la changement a lieu ailleurs qu'en D1, sort de la procédure

'début de l'ajout
Set PAE = Application.Union(Range("D3:D33"), Range("H3:H33"), Range("L3:L33"), Range("R3:R33"), Range("V3:V33"), Range("Z3:Z33"), _
    Range("D36:D66"), Range("H36:H66"), Range("L36:L66"), Range("R36:R66"), Range("V36:V66"), Range("Z36:Z66"))
PAE.ClearContents
'fin de l'ajout