Macro pour consolider des valeurs de plusieurs onglet

loulourav

XLDnaute Occasionnel
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 !
 

Pièces jointes

  • rapport test.xlsx
    125.2 KB · Affichages: 44
  • rapport test.xlsx
    125.2 KB · Affichages: 43
  • rapport test.xlsx
    125.2 KB · Affichages: 52

Modeste

XLDnaute Barbatruc
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:
VB:
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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 :rolleyes:
 

Pièces jointes

  • rapport test.xlsm
    118.6 KB · Affichages: 37

Modeste

XLDnaute Barbatruc
Re : Macro pour consolider des valeurs de plusieurs onglet

merci et bon appétit donc :rolleyes:
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!! :rolleyes:
 

klin89

XLDnaute Accro
Re : Macro pour consolider des valeurs de plusieurs onglet

Bonsoir loulourav, Modeste, le forum

Avec la méthode PasteSpecial :
VB:
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
 
Dernière édition:

loulourav

XLDnaute Occasionnel
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!! :rolleyes:

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

XLDnaute Barbatruc
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 :eek:

Pour cette remise à blanc, ajoute la ligne suivante juste avant le premier For:
VB:
recap.[A2].Resize(recap.UsedRange.Rows.Count, 6).ClearContents
 

loulourav

XLDnaute Occasionnel
Re : Macro pour consolider des valeurs de plusieurs onglet

ok cool !!! :D:D:D:D:D

ce qui reviens donc a faire ceci ? :

Code:
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
 
Dernière édition:

Discussions similaires

Réponses
12
Affichages
237

Statistiques des forums

Discussions
312 164
Messages
2 085 877
Membres
103 009
dernier inscrit
dede972