Consolider les infos de plusieurs onglets en un seul

Bonjour,

Je me permet de vous demander conseil. Je souhaite travailler un classeur excel sur lequel nous gérons les différents projets en cours par chef de projet et avoir une vue consolidée qui se met automatiquement à jour à chaque fois qu'une ligne sur l'un des onglets est mise à jour ou nouvellement créée.

J'ai testé la fonction consolider mais ne se fait pas automatiquement .... et a chaque modification il faut refaire la manipulation.

J'ai aussi tenté la fonction Indirect mais je ne m'en suis pas sorti non plus.

Je me dis que ma seule chance serait le VBA ... mais n'y connaissant pas grand chose, j'espère que vous pourriez me conseiller et je ferais le reste.

Merci

Voici un exemple du fichier que je souhaite avoir

Re bonsoir,

J'ai trouvé un super modèle qui répond exactement à mes besoins ici : https://forum.excel-pratique.com/excel/consolidation-de-plusieurs-onglets-t31187-10.html?hilit=Consolidation%20onglets

je laisse mon post ouvert car vu mon niveau je risque d'avoir encore besoin de vous.

Cordialement

Bonsoir

Un essai

Bonsoir Banzai64,

Je suis heureux de voir que certains même à des heures tardives, même un samedi soir, sont prèts à donner un petit coup de main.

J'aurais dû dès le début transmettre mon fichier d'origine que voici.

La macro fonctionne, je suis juste étonné qu'elle renvoie un message d'erreur si sur l'un des onglet il n'y a qu'une ligne à copier "Erreur 400"

Voici mon code :

Sub Budget_conso()
'

Dim x As Long, y As Long

Sheets("MARCOM - FY12 Budget & Forecast").Cells.ClearContents
Range("A1").Value = "MARCOM - FY13 Budget & Forecast"

x = Worksheets("Cathy").Range("A" & Rows.Count).End(xlUp).Row
'si je comprend bien là on dit que la variable x est l'onglet Cathy en partant de la colonne A et jusqu'à ce qu'on arrive à une cellule vide.
Sheets("Cathy").Rows("3:" & x).Copy Sheets("MARCOM - FY12 Budget & Forecast").Range("A3")
'On copie l'onglet cathy à partir de la ligne 3 (A3)sur l'onglet "Marcom..." à partir de la ligne 3 (A3)

x = Worksheets("Gabrielle").Range("A4").End(xlDown).Row
'Même chose que plus haut sauf que là le Range est spécifié ... pourquoi ?
y = Sheets("MARCOM - FY12 Budget & Forecast").Range("A" & Rows.Count).End(xlUp).Row + 1
'là vu que l'on copiera les nouvelles lignes à la fin des lignes existantes, j'imagine que pour le coup il faut leur donner cette info d'ou la variable y.?
Sheets("Gabrielle").Rows("4:" & x).Copy Sheets("MARCOM - FY12 Budget & Forecast").Range("A" & y)
'On copie l'onglet Gabrielle à partir de la ligne 4 (A4) en evitant de recopier les libellés sur l'onglet "Marcom..." à partir de la variable y

x = Worksheets("Rida").Range("A4").End(xlDown).Row
y = Sheets("MARCOM - FY12 Budget & Forecast").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Rida").Rows("4:" & x).Copy Sheets("MARCOM - FY12 Budget & Forecast").Range("A" & y)

End Sub

Si je me posais tout de même une question, sur la version consolidée, je vais devoir calculer la somme d'une colonne et l'afficher en bas du tableau. Vais-je pouvoir rappeler la variable y pour placer le total en bas du tableau ?

merci

Bonjour

J'ai mis des commentaires dans ta macro (ils commencent tous par ' *****

Pour une consolidation automatiqueje ne crois pas possible dans l'état actuel du fichier

Moi je pense qu'une consolidation est adaptée quand tu as des formules dans une page, mais ce n'est pas le cas

Je viens de voir un peu à quoi sert la consolidation et bien je m'en faisais une toute autre idée

C'est juste mon avis

Merci ! ça fonctionne ! vraiment trés content d'avoir réussi à débloquer ça ... c'était à mes yeux le cap le plus dur !

J'ai tenté de rajouter un total en bas de tableau pour la colonne J, j'explique ma logique (qui ne semble pas logique...) plus bas:

  
With Sheets("MARCOM - FY12 Budget & Forecast")
X = .Range("A" & Rows.Count).End(xlUp).Row
'J'identifie le nombre de lignes remplies et qui rentreront dans le calcul de la somme. Je me base sur la colonne A car elle sera toujours remplie si une ligne existe.
If X > 3 Then
'la fameuse verif.          
.Range("J4" & Y).End(xlUp).Row + 2) = Application.Sum(X)
'de J4 à derniére ligne (2 cellules en dessous) afficher la somme.

End If
End With

Bon biensur ça ne fonctionne pas !!! sniifff

Bonjour

Modifies la fin de ta macro comme ceci

      Y = .Range("A" & Rows.Count).End(xlUp).Row + 1
      Sheets("Rida").Rows("4:" & X).Copy .Range("A" & Y)
    End If

'J'identifie la dernière ligne
    X = .Range("A" & Rows.Count).End(xlUp).Row
    If X > 3 Then                               'la fameuse verif.
      ' Je me place 2 lignes en dessous de la dernière
      ' Je fais la somme de J4 à J...X
      .Range("J" & X + 2) = Application.Sum(Range("J4:J" & X))
    End If
  End With
End Sub

Bonjour,

Merci ! ça fonctionne a merveille

J'ai ajouter de la mise en page et une notion de vérification que le total calculé est égal au budget provisionné :

'J'identifie la dernière ligne
    X = .Range("A" & Rows.Count).End(xlUp).Row
    If X > 3 Then                               'la fameuse verif.
      ' Je me place 2 lignes en dessous de la dernière
      ' Je fais la somme de J4 à J...X
      ' Je colorie la derniere ligne ou il y aura mon total
        .Range("A:S" & X + 2).Interior.Color = RGB(174, 240, 194)
        .Range("I" & X + 2) = "Total:"
        .Range("J" & X + 2) = Application.Sum(Range("J4:J" & X))
    End If

    'Je vérifie que mon total de l'onglet compilé et mon budget provisionné collent.
    If .Range("J" & X + 2) = Sheets("DONOTDELETE").Range("J2") Then
    'Je compare donc le resultat que j'ai en bas du tableau au Budget provisionné indiqué dans l'onglet DONOTDELETE
    .Range("A2").Font.Bold = True
    .Range("A2").Font.Size = 16
    .Range("A2").Font.ColorIndex = 10
    .Range("A2").Value = "The balance is correct"
    Else
    MsgBox "Ooooops, the balance is incorrect please review amounts in the column Total Year Amount"
    .Range("A2").Value = "The balance is incorrect"
    End If

  End With
End Sub

Tout fonctionne sauf la ligne

        .Range("A:S" & X + 2).Interior.Color = RGB(174, 240, 194)

Qui me renvoie un message d'erreur.

J'ai d'autres question sur lesquelles je me penche aprés le marché du matin :

- J'ai vu sur le site qu'on peut activer des actions avant la fermeture ou l'enregistrement du fichier. Je souhaite lancer la macro consolider avant la fermeture et l'enregistrement du fichier.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Mais comment lancer la macro ?

- Comment faire en sorte qu'on ne fasse aucune modif sur la page consolidation ? j'ai dèjà expérimenté ce type de verouillage dans le passé et je crois bien que même les actions Macro sont bloquées?

- Je voudrais faire une mise en page dynamique : une ligne blanche et une ligne colorée en alternance. là je ne sais pas ...

- J'ai mis à jour une colonne "Due date" et vais essayer d'ordonner les entrées par ordre chronologique. qu'est ce que ça implique ? le bon format de date ? (mois/année ou jour/mois/année) peut être même forcer un seul format ? quelle fonction utiliser ?

Voici une timide tentative, qui ne fonctionne pas ...

'Je les classe par ordre chronologique

    X = .Range("A" & Rows.Count).End(xlUp).Row
    If X > 3 Then
    .Range("A4:S4" & X).Select
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("E4:E" & X), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A3:S" & X)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End If
    End With

J'ai un soucis evident avec la fin de mon code ... a savoir les End if et End With ... quand j'en met pas il me les demande et quand je les met il en demande d'autres....

Cette derniére question est trés importante !

Merci d'avance.

Bonjour

Quelques réponses dans le fichier

sheytane a écrit :

- J'ai vu sur le site qu'on peut activer des actions avant la fermeture ou l'enregistrement du fichier. Je souhaite lancer la macro consolider avant la fermeture et l'enregistrement du fichier.

Voir la macro Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

sheytane a écrit :

- Comment faire en sorte qu'on ne fasse aucune modif sur la page consolidation ? j'ai dèjà expérimenté ce type de verouillage dans le passé et je crois bien que même les actions Macro sont bloquées?

Déprotèges la page au début du code et protèges à la fin du code (fait dans ce fichier)

sheytane a écrit :

- Je voudrais faire une mise en page dynamique : une ligne blanche et une ligne colorée en alternance. là je ne sais pas ...

Une Mise En Forme Conditionnelle fait ça très bien

sheytane a écrit :

- J'ai mis à jour une colonne "Due date" et vais essayer d'ordonner les entrées par ordre chronologique

Je n'ai pas vue cette colonne, mais en principe un simple tri devrait faire l'affaire

A tester

Bonjour,

De retour du marché ! merci encore pour ton support.

Qu'appelles-tu mise en forme conditionnelle ? tu veux dire hors du VBA ? (même fonction qu'on utilise lorsqu'on veut mettre une cellule d'une couleur selon sa valeur ?) J'investigue alors

Edit: Je viens de voir dans la mise en forme conditionelle cette fonction :

=MOD(ROW();2)=0

Pourrais-tu me dire ce que ça veut dire ?

Oui excuse moi j'avais mis à jour la colonne Date sur la mauvaise version (voici la version MAJ)

Bonjour

=MOD(ROW();2)=0

Indique si le numéro de ligne est pair

Je t'ai modifié la MEFC

Pour le tri voir dans le fichier

Merci ! Je penses que j'ai toutes les réponses et mon fichier tourne bien !

C'est ma première journée avec VBA .... ça ouvre un peu les yeux sur la puissance de la chose !!!

Bonjour,

Je travaille mon fichier ! mais j'ai un soucis. J'ai un champ en bas de tableau qui calcule le Gap entre mon budget provisionné et mon total tableau ... quand le gap est positif ça s'affiche mais quand c'est négatif le champs est vide ... vous avez une idée ?

Merci

Rida

Bonjour

Changes la couleur de tes lignes

Petite remarque : Les cellules qui sont dans la page de consolidation doivent être précédées du . (point)

Pas grave dans ce cas car le bouton est dans la bonne page

Je me sent trés con là ! Merci

Je ne comprend pas pourquoi le resultat négatif s'affiche en Orange ? j'ai pourtant mis le code suivant pour fixer la mise en forme :

        .Range("A" & X + 2 & ":S" & X + 4).Interior.Color = RGB(250, 51, 0)
        .Range("I" & X + 2 & ":J" & X + 4).Font.ColorIndex = 2
        .Range("I" & X + 2 & ":J" & X + 4).Font.Bold = True

Bonsoir

Regardes ton format de cellule

Comme cette cellule à du servir à la copie de ligne, le format à lui aussi était copié

Maintenant que le nombre de lignes copiées est moins important la cellule lors de l'effacement garde son format

Changes le format

essayes

        .Range("I" & X + 2) = "Total Budget:"
        .Range("I" & X + 3) = "Forecasted Budget:"
        .Range("I" & X + 4) = "Gap:"
        .Range("J" & X + 2) = Application.Sum(.Range("J4:J" & X))
        .Range("J" & X + 3) = Sheets("DONOTDELETE").Range("H2")
        .Range("J" & X + 4) = Sheets("DONOTDELETE").Range("H2") - Application.Sum(.Range("J4:J" & X))
        .Range("J" & X + 4).NumberFormat = "#,##0 $"

Merci Banzai !!

Je suis assez content ...J'enrichi au fur et à mesure mon document ! j'ai entre autres réussi a faire une somme d'une colonne avec une condition:

    
    'Je calcule les frais ISS
    .Range("J" & X + 5).Font.Color = RGB(250, 51, 0)
    .Range("I" & X + 5).Font.Color = RGB(250, 51, 0)
    .Range("I" & X + 5) = "Facilitators fees:"
    .Range("J" & X + 5) = WorksheetFunction.SumIf(.Range("D" & X), "Markup", .Range("J" & X))

Je vais devoir refaire le même exercice (copier des lignes) mais cette fois il va y avoir une condition (uniquement les lignes dont la valeur des colonnes D est "Markup"

Qu'utiliser comme code ? est-ce Find ?

    X = Sheets("Cathy").Range("D" & Rows.Count).End(xlUp).Row
Set c = Sheets("Cathy").Find("Markup", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Sheets("Cathy").Rows("3:" & X).Copy Sheets("LionBridge").Range("A3")
Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If

J'ai bien tenté ... Mais mon code n'a pas l'air de vouloir marcher

Merci pour vos conseils

Bonjour

Peut-être un filtre serait plus rapide

Mais si le nombre de ligne à traiter n'est pas si important cette méthode est valable

Option Explicit

Sub test()
Dim C As Range
Dim FirstAddress As String

  With Sheets("Cathy")
    Set C = .Columns("D").Find(what:="Markup", LookIn:=xlValues, lookat:=xlWhole)
    If Not C Is Nothing Then
      FirstAddress = C.Address
      Do
        .Rows(C.Row).Copy Sheets("LionBridge").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Set C = .Columns("D").FindNext(C)
      Loop While Not C Is Nothing And C.Address <> FirstAddress
    End If
  End With
End Sub

Merci Banzai,

Le bouton activant la macro n'est pas sur ma feuille LionBridge, mais sur une autre feuille (Celle ou je consolide toutes les lignes) y a-t-il un impact sur mon code ? dois-je mettre cette macro sur un nouveau module ? et donc peut-être faire un nouveau bouton pour activer la macro ?

D'autre part je vais devoir faire cette recherche sur plusieurs onglets. donc, mon code doit-il etre le suivant :

Option Explicit

Sub test()
Dim C As Range
Dim FirstAddress As String

  With Sheets("Cathy")
    Set C = .Columns("D").Find(what:="Markup", LookIn:=xlValues, lookat:=xlWhole)
    If Not C Is Nothing Then
      FirstAddress = C.Address
      Do
        .Rows(C.Row).Copy Sheets("LionBridge").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Set C = .Columns("D").FindNext(C)
      Loop While Not C Is Nothing And C.Address <> FirstAddress
    End If
  End With

  With Sheets("Gabrielle")
    Set C = .Columns("D").Find(what:="Markup", LookIn:=xlValues, lookat:=xlWhole)
    If Not C Is Nothing Then
      FirstAddress = C.Address
      Do
        .Rows(C.Row).Copy Sheets("LionBridge").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Set C = .Columns("D").FindNext(C)
      Loop While Not C Is Nothing And C.Address <> FirstAddress
    End If
  End With

End Sub

J'ai d'autre part essayer d'appliquer une mise en forme conditionelle sur mon tableau consolidé. à savoir mettre les lignes avec l'attribut "Markup" en colonne D avec une police Grise.

Je suis parti de l'exemple donné sur le site ici : https://www.excel-pratique.com/fr/cours/excel_mises_en_forme_conditionnelles_exemples2.php

le code donné est

=CHERCHE($B$9;$D2;1)

$B$9 étant la cellule ou se trouve la condition

$D2 la cellule a partir de laquelle la recherche est faite

1 ... aucune idée

J'ai donc essayé de l'appliquer à mon fichier

=FIND("Markup";$D4;1)

Et j'applique tout cela à

=$A$4:$T$100

ça marche au premier coup ! quand je fait apply... mais aprés une consolidation ça s'efface... aucune idée pourkoi et quand je réouvre la fenetre de la regle le code a changé ce qui etait $D4 devient $D8 et ce qui etait $D$4 devient $D$8

Même chose pour la somme des "Markup" de la colonne J avec le code suivant, il n'additionne rien il prend la valeur de la derniére ligne Markup :

    'Je calcule les frais ISS
    .Range("J" & X + 5).Font.Color = RGB(250, 51, 0)
    .Range("I" & X + 5).Font.Color = RGB(250, 51, 0)
    .Range("I" & X + 5) = "Facilitators fees:"
    .Range("J" & X + 5) = WorksheetFunction.SumIf(.Range("D" & X), "Markup", .Range("J" & X))

Merci