Discussion :

[MsVixene]

Bonjour,

je sollicite votre haute compétence en matière de macro car je souffre d'incompétence totale à ce sujet.

Je souhaite automatiser la mise en forme d'une extraction des heures des salariés.

Dans le fichier joint, nous trouvons :

- Un onglet BASE qui est extrait d'un logiciel de pointage des heures des salariés
- Un onglet SALJOUR qui est une liste sur laquelle on doit pouvoir faire des rajouts, qui sert à identifier les personnes de journée
- Un onglet MO1 qui est le résultat final souhaité

Je vais récapituler les tâches que je fais actuellement et que j'aimerais réaliser de façon automatique.

Suite à un copier-coller dans l'onglet BASE et suite à un clic sur un bouton, j'aimerais que la macro génère un onglet MO1 en ayant fait les tâches ci-dessous.

- Trier la base par ordre croissant selon la colonne A (service)
- Insérer 2 colonnes avant la colonne M
- Renommer les colonnes, y compris la colonne R
- Faire la somme en ligne, en colonne M, des colonnes D à L
- Faire la somme en ligne, en colonne R, des colonnes N à Q
- En colonne N : déplacer la valeur en colonne L vers la N, seulement pour les matricules identifiés de journée(actuellement une rechercheV en bout de tableau sur l'onglet SALJOUR + copier/coller/suppression manuelle)
- Faire le sous-total des compteurs par service (colonne A) + total général
- Mettre en forme au minimum : gris sur les sous-totaux et totaux en colonne, et si possible un quadrillage sur toute la zone active
- Vérifier et matérialiser que la somme des valeurs de la base soit bien égale aux totaux des Heures et des Jours en gris.

Je vous remercie par avance si vous avez un peu de temps à consacrer à la création de cette macro.

MS.

[Jean-Philippe André]

Bonjour,

on peut t'apprendre a pecher, mais pas pecher pour toi

Vu que les differentes etapes sont deja clairement identifiees dans ton processus, tu peux regarder ce que l'enregistreur de macro peut faire pour toi
http://fauconnier.developpez.com/tut...istreur-macro/

[MsVixene]

Bonjour,

oui je me doute...

Alors voici ce que j'ai commencé, j'ai mis quelques commentaires et questions ainsi que les étapes manquantes :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Sub Macro2()
' Macro2 Macro
    Sheets("BASE").Select
    Sheets("BASE").Copy After:=Sheets(3)
 
'Tri ordre croissant colonne A
'J'aimerais renommer le nouvel onglet : MO1
    ActiveWorkbook.Worksheets("BASE (2)").AutoFilter.Sort.SortFields.Clear
 
'Comment indiquer de trier par la colonne A et pas seulement de A1 à A191 ?
    ActiveWorkbook.Worksheets("BASE (2)").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1:A191"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("BASE (2)").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
'Insertion des 2 colonnes
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
'Somme en ligne des colonnes M et R
'Comment ne pas se limiter à M191 ?
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-9]:RC[-1])"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M191")
    Range("M2:M191").Select
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
    Range("R2").Select
    Selection.AutoFill Destination:=Range("R2:R191")
    Range("R2:R191").Select
 
'Renommer les colonnes
 
'Déplacer les valeurs selon l'onglet SALJOUR
 
'Sous total par service
'Comment sélectionner la plage active et pas seulement A1:R20 ?
    Range("A1:R20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True
    ActiveWindow.SmallScroll Down:=-180
 
'Mettre en forme
 
End Sub

[Menhir]

'J'aimerais renommer le nouvel onglet : MO1

Utilise la propriété Name de la Worksheet.
Par exemple :

Code :

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

Worksheets("ancien nom").Name = "Nouveau nom"

'Insertion des 2 colonnes
Columns("M:M").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

L'enregistreur automatique colle des Select partout mais ce n'est pas forcément une bonne chose et il est conseiller d'un peu nettoyer le code après.
Par exemple, le code ci-dessus peut s'écrire :

Code :

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

1
2
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'Comment ne pas se limiter à M191 ?

Quelle serait la limite ?

Pour info, dans le même esprit que ci-dessus, tu peux simplifier ton code ainsi :

Code :

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

1
2
3
4
    Range("M2:M191").FormulaLocal = "=SOMME(D2:L2)"
    Range("M2").Copy Range("M3:M191")
    Range("R2").FormulaLocal = "=SOMME(R2:Q2)"
    Range("R2").Copy Range("R3:R191")

[MsVixene]

Alors,

j'ai :
- ajouté la ligne pour renommer l'onglet. C'est OK
- remplacé les deux lignes pour insérer les deux colonnes. C'est OK
- remplacé les lignes pour sommer les valeurs en ligne (j'ai modifié la deuxième somme pour qu'elle prenne de O2 à R2). J'aimerais dire, à la place de M191, "Copy Range(jusqu'à la dernière valeur de la colonne") car il peut y avoir plus de lignes que cette base-ci.

- ajouté des lignes pour renommer les colonnes
- commencé la mise en forme avec l'enregistreur, mais ça fait beaucoup de lignes. Peut-on réduire ? Je souhaite seulement centrer les titres et mettre le retour à la ligne.

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Sub Macro2()
' Macro2 Macro
 
'Copiage de la BASE sur nouvel onglet
    Sheets("BASE").Select
    Sheets("BASE").Copy After:=Sheets(3)
'Tri ordre croissant colonne A + Onglet renommé
    ActiveWorkbook.Worksheets("BASE (2)").AutoFilter.Sort.SortFields.Clear
    Worksheets("BASE (2)").Name = "MO1"
 
'Comment indiquer de trier par la colonne A et pas seulement de A1 à A191 ?
    ActiveWorkbook.Worksheets("MO1").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1:A191"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("MO1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
'Insertion des 2 colonnes
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
'Somme en ligne des colonnes M et R
'Comment ne pas se limiter à M191 ?
    Range("M2:M191").FormulaLocal = "=SOMME(D2:L2)"
    Range("M2").Copy Range("M3:M191")
    Range("R2").FormulaLocal = "=SOMME(O2:R2)"
    Range("R2").Copy Range("R3:R191")
 
'Renommer les colonnes
Range("A1") = "Service"
Range("B1") = "Matricule"
Range("C1") = "Nom Prénom"
Range("D1") = "Compteur 22 RCHS"
Range("E1") = "Compteur 23 RCDI"
Range("F1") = "Compteur 24 RCTP"
Range("G1") = "Compteur 25 RCJF"
Range("H1") = "Compteur 63 HARE"
Range("I1") = "Compteur 68 Reliquat"
Range("J1") = "Compteur 69 PRHH"
Range("K1") = "Compteur  32 H Récup"
Range("L1") = " Compteur 47 CC"
Range("M1") = "Total HEURES"
Range("N1") = "Compteur 47 Jours"
Range("O1") = "Compteur  44 CP-1"
Range("P1") = "Compteur 46 CP"
Range("Q1") = "Compteur  50 CET"
Range("R1") = "Total JOURS"
 
'Déplacer les valeurs selon l'onglet SALJOUR
 
'Sous total par service
'Comment sélectionner la plage active et pas seulement A1:R20 ?
    Range("A1:R20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, _
        8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18), Replace:=True, PageBreaks:=False, _
        SummaryBelowData:=True
    ActiveWindow.SmallScroll Down:=-180
 
'Mettre en forme
    'Centrer et retour à la ligne des colonnes
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
End Sub

[Menhir]

- remplacé les lignes pour sommer les valeurs en ligne (j'ai modifié la deuxième somme pour qu'elle prenne de O2 à R2). J'aimerais dire, à la place de M191, "Copy Range(jusqu'à la dernière valeur de la colonne") car il peut y avoir plus de lignes que cette base-ci.

En supposant que la colonne qui serve de référence pour la présence de données soit la D.

Code :

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

1
2
3
4
    Range("M2:M191").FormulaLocal = "=SOMME(D2:L2)"
    Range("R2").FormulaLocal = "=SOMME(O2:R2)"
    Range("M2").Copy Range("M3:M" & Range("D2").End(xlDown).Row)
    Range("R2").Copy Range("R3:R" & Range("D2").End(xlDown).Row)

- commencé la mise en forme avec l'enregistreur, mais ça fait beaucoup de lignes. Peut-on réduire ? Je souhaite seulement centrer les titres et mettre le retour à la ligne.

L'enregistreur automatique de macro peut être d'une aide précieuse MAIS il ne faut pas lui faire confiance les yeux fermés : il faut comprendre et corriger le code qu'il fournit.

Pour ça, il y a un autre outil très précieux : l'aide VBA.

Si tu places le curseur sur un mot de ton code et que tu tapes sur la touche F1, l'aide s'ouvrira pour décrire l'objet en question.
Dans ton cas, ça te permettra de voir quelles sont les lignes inutiles du code de mise en forme.

[MsVixene]

Finalement, on dit de mettre la somme en M2 et en R2, que l'on applique jusqu'à la dernière ligne, c'est ça ?

Code :

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

1
2
3
4
    Range("M2").FormulaLocal = "=SOMME(D2:L2)"
    Range("R2").FormulaLocal = "=SOMME(O2:R2)"
    Range("M2").Copy Range("M3:M" & Range("D2").End(xlDown).Row)
    Range("R2").Copy Range("R3:R" & Range("D2").End(xlDown).Row)

OK j'ai réduit un peu les lignes de l'enregistreur, j'ai ajouté du gris sur les titres + quadrillage sur CurrentRegion

- Comment je peux lui dire d'ajouter aussi du gris sur les totaux ? Sachant qu'ils peuvent être placés sur différentes lignes si ma base n'est pas la même.

Mon plus gros problème, c'est de déplacer les valeurs de la colonne L vers la colonne N seulement pour les matricules qui sont dans l'onglet SALJOUR...

[Menhir]

- Comment je peux lui dire d'ajouter aussi du gris sur les totaux ? Sachant qu'ils peuvent être placés sur différentes lignes si ma base n'est pas la même.

"différentes lignes", c'est un peu vague...
Il faudrait d'abord que tu détermines quels éléments permettent de localiser ces lignes.

- Comment sélectionner la plage active pour mettre un cadrillage ? (l'enregistreur ne sait pas traduire ctrl+* )

Le quadrillage d'une cellule se fait par un objet nommé Borders du Range spécifié.
https://msdn.microsoft.com/fr-fr/lib.../ff822605.aspx

Mon plus gros problème, c'est de déplacer les valeurs de la colonne L vers la colonne N seulement pour les matricules qui sont dans l'onglet SALJOUR...

Qu'est-ce qui caractérise un matricule par rapport à une autre cellule ?

[MsVixene]

"différentes lignes", c'est un peu vague...
Il faudrait d'abord que tu détermines quels éléments permettent de localiser ces lignes.

Si jamais il y a une nouvelle personne dans la base, ca fera une ligne de plus.
On identifie une ligne de plus via un matricule supplémentaire en colonne B. (Mais toutes les cellules de la ligne du matricule ont une valeur lors de l'extraction)

Le quadrillage d'une cellule se fait par un objet nommé Borders du Range spécifié.
https://msdn.microsoft.com/fr-fr/lib.../ff822605.aspx

Désolée j'ai édité le message après, c'est tout bon pour le quadrillage.

Qu'est-ce qui caractérise un matricule par rapport à une autre cellule ?

je ne comprends pas la question :S
J'aimerais faire comme une combinaison de rechercheV avec condition en disant en colonne N :
si le matricule fait partie de la liste de l'onglet SALJOUR, alors couper/coller la valeur de L à N.
(Concrètement, les salariés de journée ont un compteur 47 en jour et pas en heures, je souhaite les séparer)

EDIT :
Je n'arrive pas à faire ma condition qui dit que s'il y a "total" dans la colonne A, mettre la ligne en gris.
J'ai réussi seulement à dire en recopiant, que s'il y a "total" dans A5, mettre en forme A5.

Code :

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

1
2
3
4
5
6
7
8
    'Lignes TOTAUX en gris
    With Range("A:A").Select
 
If UCase(Range("A5").Value) & " " Like "*TOTAL*" Then
Range("A5").Interior.ColorIndex = 3
Else: Range("A5").Interior.ColorIndex = xlNone
End If
End With

[MsVixene]

N'a-t-on pas le droit de remonter un sujet pour avancer ?

[Jean-Philippe André]

Bonjour,

tu t'es crue sur doctissimo ou hfr

En 3 semaines, tu n'as pas avance du tout ?

A quel niveau continues-tu d'avoir un blocage ?

[Menhir]

N'a-t-on pas le droit de remonter un sujet pour avancer ?

Demande-toi pourquoi il n'y a pas eu de réponses et ce que tu pourrais améliorer dans ton message pour y remédier.
En tout cas, ce n'est certainement pas un message comme ça qui ferra évoluer les chose.
Sincèrement, un message comme ça, ça donne l'impression d'une personne qui attend que ça lui tombe tout chaud dans le bec.

[MsVixene]

tu t'es crue sur doctissimo ou hfr

Ahah non, j'étais en vacances et je reprends le sujet seulement maintenant

Ce qui m'embête le plus, c'est de déplacer la valeur de L à N si le matricule fait partie de la liste de l'onglet SALJOUR.

De façon plus secondaire, j'aimerais mettre en gris les lignes de TOTAUX mais je n'arrive à mettre en forme seulement la cellule qui contient "total".

[MsVixene]

Demande-toi pourquoi il n'y a pas eu de réponses et ce que tu pourrais améliorer dans ton message pour y remédier.
En tout cas, ce n'est certainement pas un message comme ça qui ferra évoluer les chose.
Sincèrement, un message comme ça, ça donne l'impression d'une personne qui attend que ça lui tombe tout chaud dans le bec.

J'ai posé cette question car le message que j'ai fait hier a été supprimé...C'est tout

[MsVixene]

Si j'essaie d'avancer par petit bout, je n'arrive à dire que la chose suivante :

"si une cellule de la colonne A contient "Total LG16", alors colorer la cellule en rouge".

Sauf que j'aimerais : "si une cellule de la colonne A contient "Total", alors colorer la ligne en rouge"

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
Sub Color()
 
Dim Valeur As Integer
 
Valeur = Sheets("MO1").Range("A" & Rows.Count).End(xlUp).Row
 
For i = 1 To Valeur
 
    If Sheets("MO1").Range("A" & i).Value = "Total LG16" Then
 
        Sheets("MO1").Range("A" & i).Interior.ColorIndex = 3
 
    End If
 
Next i
End Sub

[Menhir]

Première remarque : pour faire simplement ce que fait ce code, je te conseille de passer par les mises en forme conditionnelles (Ruban Accueil > Mise en Forme conditionnelle ) plutôt que par du VBA : ce sera plus simple, plus souple et plus rapide à réaliser.

Pour ce qui est de ta condition, si tu veux vérifier que cette chaine est contenue dans l'autre :

Code :

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

    If InStr(Sheets("MO1").Range("A" & i).Value, "Total") > 0 Then

https://msdn.microsoft.com/fr-fr/lib.../gg264811.aspx

Mais si "Total" est systématiquement en début de chaine, un Left() devrait suffire.
https://msdn.microsoft.com/fr-fr/lib.../gg251556.aspx

[MsVixene]

Je ne souhaite pas passer par la mise en forme conditionnelle classique sans VBA car l'onglet est généré via la macro.

J'ai réussi à colorer la ligne comme je veux mais je suis pour l'instant obligée de préciser le contenu comme "Total AQ14". (Si jamais il y a un nouveau code, ça ne marchera plus)
Le left() pourrait régler ça mais je n'arrive pas à l'intégrer.
J'ai un vrai soucis de lecture ^^

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
Sub Color()
 
Dim Valeur As Integer
 
Valeur = Sheets("MO1").Range("A" & Rows.Count).End(xlUp).Row
 
For i = 1 To Valeur
 
    If Sheets("MO1").Range("A" & i).Value = "Total AQ14" Then
 
        Sheets("MO1").Range(Cells(i, 1), Cells(i, 18)).Interior.ColorIndex = 3
    End If
    If Sheets("MO1").Range("A" & i).Value = "Total LG16" Then
 
        Sheets("MO1").Range(Cells(i, 1), Cells(i, 18)).Interior.ColorIndex = 3
    End If
Next i
End sub

[Menhir]

J'ai réussi à colorer la ligne comme je veux mais je suis pour l'instant obligée de préciser le contenu comme "Total AQ14". (Si jamais il y a un nouveau code, ça ne marchera plus)

Je t'ai mis un code tout écrit dans mon précédent message.

Le left() pourrait régler ça mais je n'arrive pas à l'intégrer.
J'ai un vrai soucis de lecture ^^

C'est quoi le "soucis" ? Tu arrives à lire les messages du forum mais pas ceux de l'aide Microsoft ?
Etrange pathologie...
Tu ne serais pas en train de te moquer (pour ne pas utiliser une autre expression...) ?

[MsVixene]

Mais non roh ! Du calme, je fais de mon mieux et ce n'est pas facile de comprendre quand on n'a pas de base. J'ai du mal à sortir du recopiage/magouillage.

j'ai réussi à intégrer le left() c'est tout bon :

Code :

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

1
2
3
4
 
    If Left(Sheets("MO1").Range("A" & i).Value, 5) = "Total" Then
 
        Sheets("MO1").Range(Cells(i, 1), Cells(i, 18)).Interior.ColorIndex = 4

Merci pour ce point.

Avez-vous une piste pour la suite : si le matricule fait partie de la liste de l'onglet SALJOUR, alors couper/coller la valeur de la colonne L à N ?

[Jean-Philippe André]

Salut,

pour determiner si une valeur fait partie d'une liste, deja sous Excel tu as l'habitude de passer par une formule ou autre chose ?

On va partir sur ta methode dans Excel pour l'adapter a VBA.