IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Automatisation mise en forme et calculs


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Femme Profil pro
    Contrôleur de Gestion
    Inscrit en
    Juillet 2014
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Haute Savoie (Rhône Alpes)

    Informations professionnelles :
    Activité : Contrôleur de Gestion

    Informations forums :
    Inscription : Juillet 2014
    Messages : 56
    Par défaut Automatisation mise en forme et calculs
    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.
    Fichiers attachés Fichiers attachés

  2. #2
    Rédacteur/Modérateur

    Avatar de Jean-Philippe André
    Homme Profil pro
    Architecte Power Platform, ex-Développeur VBA/C#/VB.Net
    Inscrit en
    Juillet 2007
    Messages
    14 682
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : Canada

    Informations professionnelles :
    Activité : Architecte Power Platform, ex-Développeur VBA/C#/VB.Net
    Secteur : Finance

    Informations forums :
    Inscription : Juillet 2007
    Messages : 14 682
    Par défaut
    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/
    Cycle de vie d'un bon programme :
    1/ ça fonctionne 2/ ça s'optimise 3/ ça se refactorise

    Pas de question technique par MP, je ne réponds pas

    Mes ouvrages :
    Migrer les applications VBA Access et VBA Excel vers la Power Platform
    Apprendre à programmer avec Access 2016, Access 2019 et 2021

    Apprendre à programmer avec VBA Excel
    Prise en main de Dynamics 365 Business Central

    Coffrets disponibles de mes ouvrages : https://www.editions-eni.fr/jean-philippe-andre
    Pensez à consulter la FAQ Excel et la FAQ Access

    Derniers tutos
    Excel et les paramètres régionaux
    Les fichiers Excel binaires : xlsb,

    Autres tutos

  3. #3
    Membre averti
    Femme Profil pro
    Contrôleur de Gestion
    Inscrit en
    Juillet 2014
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Haute Savoie (Rhône Alpes)

    Informations professionnelles :
    Activité : Contrôleur de Gestion

    Informations forums :
    Inscription : Juillet 2014
    Messages : 56
    Par défaut
    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

  4. #4
    Expert éminent Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Par défaut
    Citation Envoyé par MsVixene Voir le message
    '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")

  5. #5
    Membre averti
    Femme Profil pro
    Contrôleur de Gestion
    Inscrit en
    Juillet 2014
    Messages
    56
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Haute Savoie (Rhône Alpes)

    Informations professionnelles :
    Activité : Contrôleur de Gestion

    Informations forums :
    Inscription : Juillet 2014
    Messages : 56
    Par défaut
    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

  6. #6
    Expert éminent Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Par défaut
    Citation Envoyé par MsVixene Voir le message
    - 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.

Discussions similaires

  1. [XL-2007] Automatiser mise en forme cellule
    Par Feuztoff dans le forum Excel
    Réponses: 10
    Dernier message: 17/04/2014, 06h14
  2. [XL-2010] mise en forme et calcul de la date
    Par farota dans le forum Excel
    Réponses: 2
    Dernier message: 12/12/2012, 20h09
  3. [OpenOffice] « OpenOffice » et l'automatisation des tache de mise en forme
    Par RidaKilan dans le forum OpenOffice & LibreOffice
    Réponses: 2
    Dernier message: 22/02/2008, 16h05
  4. champ calculé avec mise en forme conditionnelle
    Par bobo59000 dans le forum Requêtes et SQL.
    Réponses: 6
    Dernier message: 15/10/2007, 20h22
  5. automatiser une mise en forme
    Par stefano dans le forum Excel
    Réponses: 8
    Dernier message: 17/07/2007, 10h34

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo