Macro pour consolider des valeurs de plusieurs onglet

[loulourav]

Bonjour à tous,

J'ai un fichier mensuel avec un onglet pour chaque jour du mois, à la fin de ce fichier j'ai besoin de faire une récap des infos de chaque onglet via un TCD, les données doivent être sous forme de colonne.
il est nécessaire que les lignes du tableau de saisie soit transposées en colonne dans l'onglet Récap.

je vous laisse mon fichier test avec 2 jours + la récap que j'ai fait à la main
j'ai laissé qlqs commentaires qui je l'espère seront assez explicite !
NB : le nombre de colonne (secteur) est susceptible de varier d'un onglet à l'autre ...

PS : au passage si vous avez une autre astuce pour le calendrier en B2 je suis preneur, de temps en temps il devient devient démesuré et un autre apparaît en haut de la feuille ...

merci vivement de votre aide !

 

[Modeste]

Re : Macro pour consolider des valeurs de plusieurs onglet

Bonjour loulourav,

Une "astuce" pour ton "contrôle calendrier", il y en a peut-être, mais ... tu n'as jamais qu'une date à saisir dans chaque feuille alors pourquoi ne pas simplement ... t'en passer

Pour le code suivant (à coller dans un module standard), j'ai considéré que:

• la feuille récap était toujours en dernière position
• il n'y aura jamais de feuilles "parasites" (en dehors de récap et de tes relevés par jour)
• dans chaque feuille, la date figurera en B2
• les "titres" seront toujours en ligne 5 (le premier secteur en colonne 2, le dernier à gauche d'une cellule qui contiendra le mot "Total")
• le nombre et l'emplacement des lignes des tableaux seront identiques dans chaque feuille

Si ces conditions sont systématiquement réunies, teste ce qui suit:

Sub conso()
Application.ScreenUpdating = False
Set recap = Sheets("récap")
For f = 1 To Sheets.Count - 1
    nbcol = Application.Match("Total", Sheets(f).[A5:AZ5], 0)
    recap.Cells(Rows.Count, 1).End(xlUp)(2).Resize(nbcol - 2, 1) = Sheets(f).[B2]
    recap.Cells(Rows.Count, 2).End(xlUp)(2).Resize(nbcol - 2, 4) = Application.Transpose(Sheets(f).[B5].Resize(4, nbcol - 2))
    For col = 2 To nbcol - 1
        recap.Cells(Rows.Count, 6).End(xlUp)(2) = Application.Sum(Sheets(f).Cells(9, col).Resize(5, 1))
    Next col
Next f
Application.ScreenUpdating = True
End Sub

 

[loulourav]

Re : Macro pour consolider des valeurs de plusieurs onglet

bonjour modeste

merci pour ta proposition ! je vais tester cela rapidement !!

Pour le calendrier c'est simplement parceque se seront des personnes différentes qui chaque jour renseigneront le tableau, je voudrais minimiser le risque d'erreur de saisie des dates (du moins dans leur format).


pour ce qui est de la structure du fichier :
la feuille récap tjs en dernier => OK
il n'y aura jamais de feuille parasites => c'est à dire des feuilles qui ne contiennent pas le tableau de saisie ? si c'est ca OK
Date en B2 => OK
les "titres" seront toujours en ligne 5 (le premier secteur en colonne 2, le dernier à gauche d'une cellule qui contiendra le mot "Total") => OK , par contre je ne veux pas reporter la colonne TOTAL dans la récap

le nombre et l'emplacement des lignes des tableaux seront identiques dans chaque feuille => OK seules les colonnes sont susceptibles de varier

merci beaucoup

a+

 

[Modeste]

Re : Macro pour consolider des valeurs de plusieurs onglet

je vais tester cela rapidement !!

Ben, du coup, j'étais devant mon écran un peu avant midi à attendre le résultat de ton test ... je crois que je vais aller dîner, finalement!

Pour éviter les erreurs d'encodage (encore faudrait-il voir lesquelles!?) une simple "Validation des données" ne suffirait-elle pas?

 

[loulourav]

Re : Macro pour consolider des valeurs de plusieurs onglet

oups désolé !!! effectivement j'aurai pu m'abstenir de "rapidement" ...

je viens de tester et ca a l'air de fonctionner plutôt bien, il faut cependant que je contrôle plus les valeurs pour être sûr.
j'ai refait le fichier avec la macro et un bouton sur l'onglet Récap, au fur et à mesure de l'alimentation du fichier le rafraichissement de l'onglet récap se fait-il simplement à la suite des données présente ou il y a un une RAZ ?

merci et bon appétit donc

 

[Modeste]

Re : Macro pour consolider des valeurs de plusieurs onglet

merci et bon appétit donc

Burps (si je puis me permettre!) et re-bonjour,

il faut cependant que je contrôle plus les valeurs pour être sûr

Je confirme que c'est prudent, en effet!

j'ai refait le fichier avec la macro et un bouton sur l'onglet Récap, au fur et à mesure de l'alimentation du fichier le rafraichissement de l'onglet récap se fait-il simplement à la suite des données présente ou il y a un une RAZ ?

... Euh ... en tout cas, il n'y a pas de RAZ dans le code proposé ... à chaque exécution, toutes les feuilles (de la 2e à l'avant-dernière) sont consolidées dans "récap". Si tu exécutes le code plusieurs fois ... Il va finir par y avoir du monde!!

 

[klin89]

Re : Macro pour consolider des valeurs de plusieurs onglet

Bonsoir loulourav, Modeste, le forum

Avec la méthode PasteSpecial :

Sub Transpose1()
Dim ws As Worksheet, n As Long, x As Byte
    Application.ScreenUpdating = False
    Sheets("récap").Cells.Clear
    n = 2
    For Each ws In Worksheets
        If ws.Name <> "récap" Then
            With ws.Range("a5").CurrentRegion
                x = .Columns.Count - 2
                With .Offset(, 1).Resize(, x)
                    .Copy
                End With
            End With
            With Sheets("récap")
                .Cells(n, 1).Resize(x).Value = ws.Range("B2").Value
                .Cells(n, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
                n = .Range("A" & Rows.Count).End(xlUp).Row + 1
            End With
        End If
    Next
    With Sheets("récap").Cells(1)
        .Resize(, 10).Value = [{"Dates","Secteurs","Total","Présents","Dispo","Bloqués A","Bloqués B","Bloqués C","Réservés 24h<","Réservés >24h"}]
        With .CurrentRegion
            With .Rows(1)
                .Interior.ColorIndex = 44
                .BorderAround Weight:=xlThin
            End With
            .Font.Name = "calibri"
            .VerticalAlignment = xlCenter
            .HorizontalAlignment = xlCenter
            .Borders(xlInsideVertical).Weight = xlThin
            .BorderAround Weight:=xlThin
            '.Columns.AutoFit
        End With
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

klin89

 

[loulourav]

Re : Macro pour consolider des valeurs de plusieurs onglet

Bonjour modeste, klin89, le forum,

... Euh ... en tout cas, il n'y a pas de RAZ dans le code proposé ... à chaque exécution, toutes les feuilles (de la 2e à l'avant-dernière) sont consolidées dans "récap". Si tu exécutes le code plusieurs fois ... Il va finir par y avoir du monde!!

ok, ce dont je voudrai être sûr c'est si j'actualise par exemple ma récap aujourd'hui j'aurai les données du 1er janv au 26 janvier, puis si le 1er février je réactualise l'onglet récap j'aurais les données du 1er janvier au 31 janvier, et pas les données du 1er janvier au 26 janvier puis en dessous les données du 1er janvier au 31 janvier ... ???

Merci Klin89 je vais tester ta proposition !!!

merci beaucoup
a+

 

[Modeste]

Re : Macro pour consolider des valeurs de plusieurs onglet

Bonsoir le fil,

ok, ce dont je voudrai être sûr c'est si j'actualise par exemple ma récap aujourd'hui j'aurai les données du 1er janv au 26 janvier, puis si le 1er février je réactualise l'onglet récap j'aurais les données du 1er janvier au 31 janvier, et pas les données du 1er janvier au 26 janvier puis en dessous les données du 1er janvier au 31 janvier ... ???

Aaaah mais si, puisqu'il n'y a pas de RAZ

Pour cette remise à blanc, ajoute la ligne suivante juste avant le premier For:

recap.[A2].Resize(recap.UsedRange.Rows.Count, 6).ClearContents

 

[loulourav]

Re : Macro pour consolider des valeurs de plusieurs onglet

ok cool !!!

ce qui reviens donc a faire ceci ? :

Sub conso()
Application.ScreenUpdating = False
Set recap = Sheets("récap")
recap.[A2].Resize(recap.UsedRange.Rows.Count, 6).ClearContents
For f = 1 To Sheets.Count - 1
    nbcol = Application.Match("Total", Sheets(f).[A5:AZ5], 0)
    recap.Cells(Rows.Count, 1).End(xlUp)(2).Resize(nbcol - 2, 1) = Sheets(f).[B2]
    recap.Cells(Rows.Count, 2).End(xlUp)(2).Resize(nbcol - 2, 4) = Application.Transpose(Sheets(f).[B5].Resize(4, nbcol - 2))
    For col = 2 To nbcol - 1
        recap.Cells(Rows.Count, 6).End(xlUp)(2) = Application.Sum(Sheets(f).Cells(9, col).Resize(5, 1))
    Next col
Next f
Application.ScreenUpdating = True
End Sub

je test à fond vos 2 propositions demain !!!
merci modeste et klin89 !!!

bonne soirée

 

[loulourav]

Re : Macro pour consolider des valeurs de plusieurs onglet

bonsoir à tous,

je suis désolé je n'ai pas eu le temps de tester .... promis je m'y colle courant de semaine prochaine !!!

merci en tout cas du boulot fournit
super sympa !
a+