Automatiser le remplissage mensuellement

U

undo74

XLDnaute Nouveau
Bonjour a tous,

Je souhaite automatiser le remplissage deux tableaux via une macro.

Nous avons trois feuilles (F_Contractualisé, F_Engagement à venir et Tb)
- Dans la feuille F_Contractualisé nous avons quatre colonnes (Date DA, Capex/Opex , Total et Total /1000)
- Dans la feuille F_Engagement à venir nous avons trois colonnes (Mensualisation Date de l'emission de la DA, Capex/Opex, Total MAJ)
- Dans la feuille Tb nous avons un tableau CAPEX de 12 colonnes les mois de l’année) et cinq lignes (Budget CAPEX, Contractualisé, Engagement à venir, Somme, Reste à engager)
- Toujours dans la feuille Tb nous avons un tableau OPEX de 12 colonnes les mois de l’année) et cinq lignes (Budget OPEX, Contractualisé, Engagement à venir, Somme, Reste à engager)
La procédure chaque début du mois je dois mettre a jour les deux tableaux CAPEX et OPEX
Les lignes : (Budget CAPEX et OPEX , Somme, Reste à engager) les valeurs ne bouge pas.
Je remplir donc les lignes ( Contractualisé et Engagement à venir) en m’appuyant sur les données des deux feuilles F_Contractualisé et F_Engagement à venir.
La Méthode :
Si nous sommes en Juin
Mise a jour des lignes Contractualisé les cellule G5 : N5 et G12 : N12 des deux tableaux :
 Mise a jour cellule G5 : N5 :
Dans la feuil F_Contractualisé :
- la col Date DA : je prends tous les dates jusqu’à fin de Mai
- la col Capex/Opex : filtre sur CAPEX
- la col Total /1000 : je prends la somme dans notre cas = 4440,449
Puis je recopie la valeur dans les cellules G5 : N5 je ne touche pas aux valeur des mois précédents c'est-à-dire Janv à Avril ( C5 :F5)
 Mise a jour cellule G12 : N12:
- Idem nous avons en col Capex/Opex : filtre sur OPEX
- la col Total /1000 : la somme dans notre cas = 3439,56

Maintenant Mise a jour les lignes Engagement à venir les cellule G6 : N6 et G13 : N13 des deux tableaux :
 Mise a jour cellule G6 : N6 :
Dans la feuil F_Contractualisé :
- la col Mensualisation Date de l'emission de la DA: je prends tous les dates jusqu’à fin de Mai
- la col Capex/Opex : filtre sur CAPEX
- la col Total MAJ: je prends la somme dans notre cas = 308,52
- Puis je recopie la valeur dans la cellule G6 ensuite pour la cellule suivante c'est-à-dire H6 je rajoute dans mon filtre col Mensualisation Date de l'emission le mois Juin puis je prends la somme = 2428,557 de col Total MAJ et la même chose pour les mois suivants de I6 :N6
- je ne touche pas aux valeur des mois précédents c'est-à-dire Janv à Avril ( C6 :F6)

 Mise a jour cellule G13 : N13:
- Idem nous avons en col Capex/Opex : filtre sur OPEX
- la col Total /1000 : la somme dans notre cas = 60

Est-ce que qqun pourrait m'aider?:confused:

D'avance merci
 

Pièces jointes

  • Classeur1.xlsx
    27.6 KB · Affichages: 106
  • Classeur1.xlsx
    27.6 KB · Affichages: 94
  • Classeur1.xlsx
    27.6 KB · Affichages: 83
Trier par date Trier par votes
U

undo74

XLDnaute Nouveau
Re : Automatiser le remplissage mensuellement

Bebere à dit:
bonjour Undo
bienvenue
formules avec sous.total et SommeProd(cellules colorées)
Cliquez pour agrandir...

Bonjour Bebere,
Merci pour votre aide.
Malheureusement c'est pas le résultat attendu :(
J'ai bosser sur le sujet via des Macros mais j'ai des soucis au niveau de la Maj.
En pj un nouveau fichier avec des Macros.
Pouvez-vous svp me corrigé le pb ?:confused:
Merci pour infiniment de votre aide.:eek:
 

Pièces jointes

  • Classeur1.xlsm
    515.3 KB · Affichages: 70
  • Classeur1.xlsm
    515.3 KB · Affichages: 76
  • Classeur1.xlsm
    515.3 KB · Affichages: 73
Vote positif 0 Vote négatif
B

Bebere

XLDnaute Barbatruc
Re : Automatiser le remplissage mensuellement

bonjour Undo
le dernier fichier mis donne une erreur de format(dates)
le code trop compliqué
essaye ce code
donne le même résultat que le filtre

Code: 
Public Sub x()
    Dim mois As Long, an As Long, DerL As Long, nom As String, MySum1 As Double, MySum2 As Double, Col As Byte
    an = 2014: mois = 6: Col = mois + 2

    'noms définis
    With Worksheets("F_Contractualisé")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAc"
        .Range("B2:B" & DerL).Name = "ColBc"
        .Range("D2:D" & DerL).Name = "ColDc"
    End With

    'noms définis
    With Worksheets("F_Engagement à venir")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAe"
        .Range("B2:B" & DerL).Name = "ColBe"
        .Range("C2:C" & DerL).Name = "ColCe"
    End With

    nom = "CAPEX"
    'Contractualisé
    a = "sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))"
    MySum1 = Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement
    MySum2 = Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & mois & ")*(colbe=""" & nom & """)*(colce))")

    With Worksheets("Tb")
        .Range(.Cells(5, Col), .Cells(5, 14)).Value = MySum1'à adapter
        .Range(.Cells(6, Col), .Cells(6, 14)).Value = MySum2'à adapter
    End With

    nom = "OPEX"
    'Contractualisé
    MySum1 = Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement
    MySum2 = Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & mois & ")*(colbe=""" & nom & """)*(colce))")

    With Worksheets("Tb")
        .Range(.Cells(12, Col), .Cells(12, 14)).Value = MySum1'à adapter
        .Range(.Cells(13, Col), .Cells(13, 14)).Value = MySum2'à adapter
    End With

End Sub
 
Vote positif 0 Vote négatif
U

undo74

XLDnaute Nouveau
Re : Automatiser le remplissage mensuellement

Bebere à dit:
bonjour Undo
le dernier fichier mis donne une erreur de format(dates)
le code trop compliqué
essaye ce code
donne le même résultat que le filtre

Code: 
Public Sub x()
    Dim mois As Long, an As Long, DerL As Long, nom As String, MySum1 As Double, MySum2 As Double, Col As Byte
    an = 2014: mois = 6: Col = mois + 2

    'noms définis
    With Worksheets("F_Contractualisé")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAc"
        .Range("B2:B" & DerL).Name = "ColBc"
        .Range("D2:D" & DerL).Name = "ColDc"
    End With

    'noms définis
    With Worksheets("F_Engagement à venir")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAe"
        .Range("B2:B" & DerL).Name = "ColBe"
        .Range("C2:C" & DerL).Name = "ColCe"
    End With

    nom = "CAPEX"
    'Contractualisé
    a = "sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))"
    MySum1 = Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement
    MySum2 = Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & mois & ")*(colbe=""" & nom & """)*(colce))")

    With Worksheets("Tb")
        .Range(.Cells(5, Col), .Cells(5, 14)).Value = MySum1'à adapter
        .Range(.Cells(6, Col), .Cells(6, 14)).Value = MySum2'à adapter
    End With

    nom = "OPEX"
    'Contractualisé
    MySum1 = Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & mois & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement
    MySum2 = Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & mois & ")*(colbe=""" & nom & """)*(colce))")

    With Worksheets("Tb")
        .Range(.Cells(12, Col), .Cells(12, 14)).Value = MySum1'à adapter
        .Range(.Cells(13, Col), .Cells(13, 14)).Value = MySum2'à adapter
    End With

End Sub
Cliquez pour agrandir...

Bonjour Bebere,
Avant Tout Merci pour votre aide;)
J'ai fais un test de votre code avec le premier fichier j'ai bien des informations mais pas les bonnes :( c'est peut être que ma demande n'est pas vraiment clair:confused:
le résultat que je souhaite est dans le fichier dans l'onglet Tb.
si je lance votre macro je dermarre bien a la date souhaite dans votre ton : mois = 5: Col = mois + 2
ici tout va de très bien.
le soucis c'est que nous n'avons pas la sommes de l'historique c'est a dire dans la ligne Contractualisé dans la cellule de Mai (rappel que nous sommes en Juin je fais la Maj N-1) j'obtiens avec votre code 3534,892 alors de je dois avoir 4440,449 -> 3534,892 + Mai (122,76) + 2013 (782,80).
sinon pour la ligne Engagement à venir je pense que c'est la même chose. j'ai pas la sommes a chaque Mois des mois précédent. avec le code toujours en Mai 154,194 alors de je dois avoir 308,52-> Jan (0)+Fev(0)+Mars(26,3)+Avril(127,894)+Mai(154,326).
Voila grand merci de votre aide.:eek:
 
Vote positif 0 Vote négatif
B

Bebere

XLDnaute Barbatruc
Re : Automatiser le remplissage mensuellement

Undo
voilà le bon
Code: 
Public Sub x()
    Dim mois As Long, an As Long, DerL As Long, nom As String, MySum1 As Double, MySum2 As Double, MySumc As Double, MySume As Double, Col As Byte, m As Byte
    an = 2014: mois = 6: Col = mois + 2

    'noms définis
    With Worksheets("F_Contractualisé")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAc"
        .Range("B2:B" & DerL).Name = "ColBc"
        .Range("D2:D" & DerL).Name = "ColDc"
    End With

    'noms définis
    With Worksheets("F_Engagement à venir")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAe"
        .Range("B2:B" & DerL).Name = "ColBe"
        .Range("C2:C" & DerL).Name = "ColCe"
    End With

    nom = "CAPEX"
    'Contractualisé 2013
    MySumc = Evaluate("sumproduct((year(colac)<" & an & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement 2013
    MySume = Evaluate("sumproduct((year(colae)<" & an & ")*(colbe=""" & nom & """)*(colce))")
    For m = mois - 1 To 12
    MySum1 = 0: MySum2 = 0
        MySum1 = MySumc + Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & m & ")*(colbc=""" & nom & """)*(coldc))")
        MySum2 = MySume + Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & m & ")*(colbe=""" & nom & """)*(colce))")

        With Worksheets("Tb")
          .Cells(5, m + 2).Value = MySum1 '  .Range(.Cells(5, Col), .Cells(5, 14)).Value = MySum1    'à adapter
          .Cells(6, m + 2).Value = MySum2 '   .Range(.Cells(6, Col), .Cells(6, 14)).Value = MySum2    'à adapter
        End With
    Next m

    nom = "OPEX"
    MySumc = 0: MySume = 0
    'Contractualisé 2013
    MySumc = Evaluate("sumproduct((year(colac)<" & an & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement 2013
    MySume = Evaluate("sumproduct((year(colae)<" & an & ")*(colbe=""" & nom & """)*(colce))")

    For m = mois - 1 To 12
    MySum1 = 0: MySum2 = 0
        MySum1 = MySumc + Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & m & ")*(colbc=""" & nom & """)*(coldc))")
        MySum2 = MySume + Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & m & ")*(colbe=""" & nom & """)*(colce))")

        With Worksheets("Tb")
           .Cells(12, m + 2).Value = MySum1 ' .Range(.Cells(12, Col), .Cells(12, 14)).Value = MySum1    'à adapter
            .Cells(13, m + 2).Value = MySum2 '.Range(.Cells(13, Col), .Cells(13, 14)).Value = MySum2    'à adapter
        End With
    Next m

End Sub
 
Vote positif 0 Vote négatif
U

undo74

XLDnaute Nouveau
Re : Automatiser le remplissage mensuellement

Bebere à dit:
Undo
voilà le bon
Code: 
Public Sub x()
    Dim mois As Long, an As Long, DerL As Long, nom As String, MySum1 As Double, MySum2 As Double, MySumc As Double, MySume As Double, Col As Byte, m As Byte
    an = 2014: mois = 6: Col = mois + 2

    'noms définis
    With Worksheets("F_Contractualisé")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAc"
        .Range("B2:B" & DerL).Name = "ColBc"
        .Range("D2:D" & DerL).Name = "ColDc"
    End With

    'noms définis
    With Worksheets("F_Engagement à venir")
        DerL = .Range("A65536").End(xlUp).Row
        .Range("A2:A" & DerL).Name = "ColAe"
        .Range("B2:B" & DerL).Name = "ColBe"
        .Range("C2:C" & DerL).Name = "ColCe"
    End With

    nom = "CAPEX"
    'Contractualisé 2013
    MySumc = Evaluate("sumproduct((year(colac)<" & an & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement 2013
    MySume = Evaluate("sumproduct((year(colae)<" & an & ")*(colbe=""" & nom & """)*(colce))")
    For m = mois - 1 To 12
    MySum1 = 0: MySum2 = 0
        MySum1 = MySumc + Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & m & ")*(colbc=""" & nom & """)*(coldc))")
        MySum2 = MySume + Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & m & ")*(colbe=""" & nom & """)*(colce))")

        With Worksheets("Tb")
          .Cells(5, m + 2).Value = MySum1 '  .Range(.Cells(5, Col), .Cells(5, 14)).Value = MySum1    'à adapter
          .Cells(6, m + 2).Value = MySum2 '   .Range(.Cells(6, Col), .Cells(6, 14)).Value = MySum2    'à adapter
        End With
    Next m

    nom = "OPEX"
    MySumc = 0: MySume = 0
    'Contractualisé 2013
    MySumc = Evaluate("sumproduct((year(colac)<" & an & ")*(colbc=""" & nom & """)*(coldc))")
    'Engagement 2013
    MySume = Evaluate("sumproduct((year(colae)<" & an & ")*(colbe=""" & nom & """)*(colce))")

    For m = mois - 1 To 12
    MySum1 = 0: MySum2 = 0
        MySum1 = MySumc + Evaluate("sumproduct((year(colac)=" & an & ")*(month(colac)<" & m & ")*(colbc=""" & nom & """)*(coldc))")
        MySum2 = MySume + Evaluate("sumproduct((year(colae)=" & an & ")*(month(colae)<" & m & ")*(colbe=""" & nom & """)*(colce))")

        With Worksheets("Tb")
           .Cells(12, m + 2).Value = MySum1 ' .Range(.Cells(12, Col), .Cells(12, 14)).Value = MySum1    'à adapter
            .Cells(13, m + 2).Value = MySum2 '.Range(.Cells(13, Col), .Cells(13, 14)).Value = MySum2    'à adapter
        End With
    Next m

End Sub
Cliquez pour agrandir...

Re Bebere,
C'est super le code fonction très bien mais j'ai toujours un petit souci il ya un décalage sur un mois logiquement je dois avoir au mois de mai pour tableau CAPEX : Contractualisé 4440,449 et Engagement à venir 308,52 pour le tableau OPEX:Contractualisé 3 439,56 et Engagement à venir 60
le traitement :
chaque début du mois je mets a jour le mois écoulé c'est-à-dire nous sommes au mois de juin je lance la maj à partir de Mai.
En Pj le fichier avec votre code et le résultat que je souhaite obtenir.

Grand Merci pour votre aide précieux.:)
 

Pièces jointes

  • ClasseurUNDO Macro_Bebere.zip
    37.3 KB · Affichages: 44
Vote positif 0 Vote négatif
B

Bebere

XLDnaute Barbatruc
Re : Automatiser le remplissage mensuellement

undo
obtenu
3 439,56 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 €
à obtenir
3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 €
les autres sont bons
 
Vote positif 0 Vote négatif
U

undo74

XLDnaute Nouveau
Re : Automatiser le remplissage mensuellement

Bebere à dit:
undo
obtenu
3 439,56 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 €
à obtenir
3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 €
les autres sont bons
Cliquez pour agrandir...
re Bebere,
Je suis désolé je ne comprend pas ci -dessous la capture écran j'espères c'est plus clair:eek:
capture.jpg
Merci d'avance pour votre aide:D
 

Pièces jointes

  • capture.jpg
    capture.jpg
    100.4 KB · Affichages: 207
  • capture.jpg
    capture.jpg
    100.4 KB · Affichages: 78
Dernière édition:
Vote positif 0 Vote négatif
B

Bebere

XLDnaute Barbatruc
Re : Automatiser le remplissage mensuellement

Undo
avec la correction apportée le résultat est bon pour 3 lignes
exeptée la ligne(obtenu 3 439,56 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 € 3 442,46 €)
tu es sûr de la ligne à obtenir 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 € 3 439,56 €
 
Vote positif 0 Vote négatif
U

undo74

XLDnaute Nouveau
Re : Automatiser le remplissage mensuellement

Bebere à dit:
bonjour Undo
résultat obtenu bon
vérifié avec filtre
Cliquez pour agrandir...
Bonjour Bebere,
C'est SUPER exactement le résultat que je souhaite Chapeau bas;)
Sans abusé il possible avoir une boite de dialogue pour saisir le Mois:

Public Sub x()
Dim mois As Long, an As Long, DerL As Long, nom As String, MySum1 As Double, MySum2 As Double, MySumc As Double, MySume As Double, m As Byte
an = 2014: mois = 6: Col = mois + 2
Merci encore pour ton aide:D
 
Vote positif 0 Vote négatif

Discussions similaires

N
  • Résolu(e)
XL 2016 tableau de vente/gestion des stocks boutique : quelles formules ?!
Réponses
12
Affichages
1 K
Gégé-45550
Gégé-45550
J
  • Question
XL 2016 Prévision de stock selon la consommation et les arrivages
Réponses
2
Affichages
3 K
jkatten
J
U
  • Question
Simplification du code et évolution
Réponses
1
Affichages
559
camarchepas
camarchepas
L
  • Question
XL 2013 Code Macro "effacer des cellules (garder les lignes) en fonction du mois en cours"
2
Réponses
22
Affichages
7 K
LaREUNION
L
D
  • Question
automatiser le calcul des heures supplémentaires
2
Réponses
15
Affichages
48 K
Victor21
Victor21
R
  • Question
Problème confection macro: Assembler les feuilles et mise à jour.
Réponses
3
Affichages
2 K
Rollingcream33
R
super_newbie_pro
  • Question
Aide de caisse ; automatiser les calculs suivant des catégories, la date et...
2 3
Réponses
44
Affichages
24 K
super_newbie_pro
super_newbie_pro
S
  • Question
macro avec de multiples conditions (alors que je sais que je n'ai pas le niveau)
Réponses
14
Affichages
2 K
syl
S
E
  • Question
Problème exercice VBA
Réponses
2
Affichages
2 K
kjin
K
M
[REF] Wiki 4 de MichelXld (Fonctions, Evenements, Dates et calendriers, Flash , Ooo)
Réponses
0
Affichages
34 K
MichelXld
M

Membres actuellement en ligne

13 membres et 104 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 104
Messages
2 085 332
Membres
102 864
dernier inscrit
abderrashmaen