consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT, etc

[AurélienP]

j'ai réalisé une maquette pour suivre les effectifs de plusieurs région...

chaque maquette est composée de 6 onglets (dont une synthèse sur laquelle est compilée l'ensemble des données dont j'ai besoin) les structures sont rigouresement les même pour chacune des maquettes.

j'ai créé une maquette de récap qui reprend les 5 onglets synthèse des 5 régions.

je voudrais synthétiser les données (=> récupérer les données de chaque onglet et ainsi créer un tableau unique avec toutes mes infos)

mon problème, c'est que le nombre de lignes est variable, et je souhaiterai "coller" les données des villes en dessous les unes des autres (sans vide) et les trier par ordre alphabétique (les noms!)

je ne maîtrise pas assez les fonction INDEX, EQUIV, INDIRECT et DECALER... je suis certain que l'on peut le faire sous Excel et non VB...

avec la fonction décaler j'ai réussi à mettre en place une extraction de plage variable, mais je n'arrive pas répeter cela dans chaque onglet en automatique, et faire qu'il se trie et se mettent à la suite....

j'ai aussi récupéré une formule qui va chercherlmes données dans plusieurs feuilles mais je n'arrive pas à la paramétrer pour qu'elle me prenne une plage variable...

Aidez moi, et surtout expliquez moi !!! je ne veux pas mourir idiot !!!!

je vous joins un fichier

 

[Compte Supprimé 979]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour AurélienP

suis certain que l'on peut le faire sous Excel et non VB...

Certainement, mais ton fichier va devenir une usine à gaz ... bonjour le temps de calcul

Le VBA n'est pas inaccessible ...

A+

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Merci de ta réponse,
Ok pour le VBA, mais je voudrais comprendre ce que vous me proposez. j'aurais surement besoin de le dupliquer.

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

un petit up...

merci d'avance aux âmes charitables qui pourront me filer un petit coup de pouce

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

j'ai avancé sur mon problème, en utilisant cette formule (issue d'une maquette de Mr BOISGONTIER)

j'arrive à extraire les lignes d'une seule feuille ou certaines lignes de plusieurs feuilles

=SI(LIGNES($1:$1)<Nbf;INDIRECT("'"&INDEX(Nf;LIGNES($1:7))&"'!"&ADRESSE(LIGNES($1:7);COLONNES($A:A)));"")
avec Nbf correpsondant à =CNUM(LIRE.CLASSEUR(4)&INDIRECT("iv65000"))
et Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

mon problème serait de faire varier les plages extraites (sur les feuilles) et faire que les données extraites s'imputent les unes après les autres... avec un tri par nom....

vous avez des idées ???

 

[job75]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour AurélienP, Bruno le forum,

Bien bizarre ce fichier, QUI l'a fabriqué ???

Dans les colonnes "Nom" il y a des cellules "vides" qui contiennent un espace !

Et dans les autres colonnes il y a des "VRAI" ou des "0" masqués par MFC !

Pour les voir sélectionner toutes les cellules d'une feuille...

Heureusement avec VBA on déjoue tous les pièges ac (Alt+F11 => Module1) :

Sub Consolider()
Dim lig As Long, w As Worksheet, h As Long
Feuil1.Activate 'CodeName de "Consolidation Synthèse"
Application.ScreenUpdating = False 'fige l'écran
Rows("5:" & Rows.Count).Delete 'vidage
lig = 5 '1ère ligne à remplir
For Each w In Worksheets
  If w.Name <> ActiveSheet.Name Then
    h = w.Cells(Rows.Count, 2).End(xlUp).Row - 4
    If h > 0 Then
      w.[5:5].Resize(h).Copy Cells(lig, 1)
      lig = lig + h
    End If
  End If
Next
If lig = 5 Then Exit Sub 'si aucun nom
With [5:5].Resize(lig - 5)
  .Sort [B5], Header:=xlNo 'tri sur colonne B
  '---épuration---
  .Columns(2).Replace " ", "", LookAt:=xlWhole
  On Error Resume Next
  .Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Le code n'est pas bien difficile à comprendre, éventuellement voir l'aide VBA sur certains mots.

C'est le type même de problème à régler par VBA, par formules c'est bien trop lourd.

Fichier joint enregistré sous Excel 2003.

A+

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour Job75, le forum,

Wouahhh !!!

c'est top !!! Merci BCP Job75 !!!

le fichier c'est moi qui l'ai fait.....

j'ai du m'adapter aux différentes problématiques que je rencontrais... du coup ça fait bidouillage....

la macro c'est exactement ce que je voulais.

merci bcp

Rq: je vais la decortiquer pour en comprendre le sens.

Excellente Journée à vous tous

Aurélien

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

bonjour à tous,

Bon finalement j'ai un Pb, je n'arrive pas à comprendre pourquoi... ma maquette est alimentée par des liens... et ça pollue la consolidation...

si Job75 a qq minutes pour jeter un oeil...

je vous joins mon fichier

 

[job75]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour Aurélien,

Désolé pour le retard.

Les cellules contiennent des formules de liaisons, il faut donc copier leurs valeurs.

J'ai juste ajouté 2 lignes :

Sub Consolider()
Dim lig As Long, w As Worksheet, h As Long
Feuil1.Activate 'CodeName de "Consolidation Synthèse"
Application.ScreenUpdating = False 'fige l'écran
Application.DisplayAlerts = False 'évite les invites éventuelles (liaisons)
Rows("5:" & Rows.Count).Delete 'vidage
lig = 5 '1ère ligne à remplir
For Each w In Worksheets
  If w.Name <> ActiveSheet.Name Then
    h = w.Cells(Rows.Count, 2).End(xlUp).Row - 4
    If h > 0 Then
      w.[5:5].Resize(h).Copy Rows(lig) 'pour les formats
      Rows(lig).Resize(h, 13) = w.[5:5].Resize(h, 13).Value 'valeurs colonnes A:M
      lig = lig + h
    End If
  End If
Next
If lig = 5 Then Exit Sub 'si aucun nom
With [5:5].Resize(lig - 5)
  .Sort [B5], Header:=xlNo 'tri sur colonne B
  '---épuration---
  .Columns(1).Replace " ", "", LookAt:=xlWhole
  On Error Resume Next
  .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Fichier (2).

A+

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Super !!! ça fonctionne trop bien !!! j'ai même pu le transposer sur un autre fichier.

par contre je me permets d'abuser un peu... j'ai essayer d'adapter ton code pour récupérer les prénom, nom et site de chaque onglets (prof, admin, etudiant, modele et personnel ss facture) j'ai restreint le code aux premières colonnes, et de récupérer la fonction par le nom de fichier source... mais ça bug...

précisément:

onglet "suivi congés":*

cellule A, B, C AK renseignées à partir des onglets

quand les données viennent de "prof" je souhaiterais obtenir prof dans emploi (colonne D) et ainsi de suite

les colonnes E: AJ sont remplies manuellement

objectif: reprendre l'ensemble des effectifs de chaque onglet pour pouvoir suivre leur congés

tu peux m'aider une fois de plus ???

 

[job75]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonsoir,

Vous revenez sur le fil quand il y a problème.

Car il vous a fallu 5 jours pour accuser réception de mon post #9

le fichier c'est moi qui l'ai fait.....

On avait bien remarqué que vous étiez spécialiste des fichiers alambiqués mais là ça dépasse les bornes.

Regardez les 5 feuilles à traiter : elles sont différentes, elles n'ont pas les mêmes colonnes.

Alors mettez de l'ordre dans tout ça, ensuite on pourra faire une synthèse qui ne soit pas une usine à gaz.

Et sans se prendre la tête.

A+

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour job75, le forum,

Bon, le verdict a le mérite d'être clair et sans appel... je revois ma copie... et ensuite on verra si qqchose est possible facilement...

bonne journée

AurélienP

 

[job75]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Bonjour Aurélien,

Excusez-moi, mais vous m'avez bien énervé avec votre fichier.

Cependant avec VBA on se sort de toutes les situations.

Et ce n'est pas l'usine à gaz :

Sub Consolider()
Dim lig As Long, w As Worksheet, h As Long, col As Byte
Feuil7.Activate 'CodeName de "suivi Congés"
Application.ScreenUpdating = False 'fige l'écran
Application.DisplayAlerts = False 'évite les invites éventuelles (liaisons)
Rows("5:" & Rows.Count).Delete 'vidage
lig = 5 '1ère ligne à remplir
For Each w In Sheets(Array("Prof ", "Modèle", "Admin", "Etudiant", "Personnel ss Facture")) 'espace après Prof...
  h = w.Cells(Rows.Count, "C").End(xlUp).Row - 4 'normalement colonne A mais ne convient pas ici...
  If h > 0 Then
    Cells(lig, "D").Resize(h) = w.Name 'Emploi
    w.[A5].Resize(h, 3).Copy Cells(lig, 1) 'pour les formats
    Cells(lig, 1).Resize(h, 3) = w.[A5].Resize(h, 3).Value 'valeurs colonnes A:C
    col = w.[4:4].Find("=*", LookIn:=xlFormulas).Column
    w.Cells(5, col).Resize(h, 32).Copy Cells(lig, "E") 'colonnes des dates
    col = w.[4:4].Find("commentaires*").Column
    w.Cells(5, col).Resize(h).Copy Cells(lig, "AK") 'pour les formats
    Cells(lig, "AK").Resize(h) = w.Cells(5, col).Resize(h).Value 'valeurs colonne AK
    lig = lig + h
  End If
Next
If lig = 5 Then Exit Sub 'si aucun nom
With [5:5].Resize(lig - 5)
  .Columns(3).AutoFill .Columns(3).Resize(, 2), xlFillFormats 'format colonne D
  .Sort [A5], Header:=xlNo 'tri sur colonne A
  '---épuration---
  .Columns(1).Replace " ", "", LookAt:=xlWhole
  On Error Resume Next
  .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

Il serait bon toutefois de revoir 2 choses :

- le nom de l'onglet Prof qui est suivi d'un espace inutile et source d'erreur

- les textes ("légende") en bas des colonnes A des feuilles Prof et Personnel ss Facture.

Ils obligent à utiliser la colonne C pour déterminer h sur chaque tableau.

Mettez ces textes en colonne B ou C pour qu'on puisse utiliser la colonne A.

Fichier joint.

A+

 

[AurélienP]

Re : consolidation données issues de plusieurs feuilles dans une seule.... (INDIRECT,

Merci Job75,

j'avais repris mon fichier pour le rendre moins "brouillon", ou en tout cas plus homogène...

j'ai fait les modif de "légende" et "d'espace" sur le fichier et dans le code.

par contre petite question technique, comment la macro sait qu'elle va chercher les commentaires (alors que les colonnes varient selon les onglets) ? j'ai trouvé, elle réimporte l'ensemble des colonnes jusqu'au commentaire

merci en tout cas de votre réactivité.

bonne journée.

Aurélien

PS: il faut vraiment que je me remette sérieusement à VBA... mes restes sont sporadiques...