Macro pour copie de données vers une seule feuille

Bonjour,

Dans le fichier en PJ, j'ai adapté une macro trouvée sur le forum afin de copier les données de plusieurs feuilles vers une feuille synthèse:

Sub Importer()
Sheets("Synthese").Range("A2:Z20").ClearContents 'Vide les 20 premières lignes
Derligne = Sheets("Synthese").Range("A65536").End(xlUp).Row + 1 'A quoi ca sert?

   For i = 1 To Worksheets.Count
 Range("A" & Derligne).Value = Sheets(i).Range("L4").Value
 Range("B" & Derligne).Value = Sheets(i).Range("Q10").Value
 Range("C" & Derligne).Value = Sheets(i).Range("D10").Value
 Range("D" & Derligne).Value = Sheets(i).Range("I10").Value
 Range("E" & Derligne).Value = Sheets(i).Range("C8").Value
 Range("F" & Derligne).Value = Sheets(i).Range("N8").Value
 Range("G" & Derligne).Value = Sheets(i).Range("A13").Value
 Range("H" & Derligne).Value = Sheets(i).Range("A45").Value
 Range("I" & Derligne).Value = Sheets(i).Range("N6").Value
 Derligne = Derligne + 1
   Next i
End Sub

Ça fonctionne pas trop mal, mais j'ai quelques questions:

1. Au début de la macro, je vide une plage de cellules (A2:Z20). Comment vider toutes les lignes sauf la première (elle contient le nom des colonnes)?

2. Quand je lance la macro, les valeurs se copient à partir de la 3ème ligne sur la feuille "Synthèse". Ca me va bien finalement, mais pourquoi la ligne est "sautée"?

3. J'ai beau masquer la feuille "Paramètres", elle est quand même traitée par la macro. Comment l'ignorer?

En vous remerciant!!!

Bonjour,

Essaie ainsi :

Sub Importer()
    Dim Cel, Lgn(), i%, n%, wsSynt As Worksheet, ws As Worksheet
    Cel = Split("L4 Q10 D10 I10 C8 N8 A13 A45 N6")
    Set wsSynt = Worksheets("Synthese")
    wsSynt.Range("A1").CurrentRegion.Offset(1).ClearContents
    ReDim Lgn(UBound(Cel)): n = 2
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Synthese", "Paramètres"
            Case Else
                With ws
                    For i = 0 To UBound(Cel)
                        Lgn(i) = .Range(Cel(i))
                    Next i
                End With
                wsSynt.Cells(n, 1).Resize(, UBound(Cel) + 1).Value = Lgn
                n = n + 1
        End Select
    Next ws
End Sub

Cordialement.

Merci pour ton aide.

Je teste!

(Désolé pour ma réponse tardive, j'étais en congés)

Fonctionne parfaitement.

Juste une question: possible de le faire dans l'autre sens? C'est à dire partir des données de l'onglet "Synthèse" pour générer des onglets complétés?

Merci!!

Oui, il faut disposer d'un onglet modèle apprêté (mais vide), donc connaître son nom. On supprimera tous les onglets existants, sauf Paramètres et Synthèse. On génèrera les onglets à partir du modèle, pour les remplir avec les infos de Synthèse : il faut savoir comment doivent être nommés les onglets créés.

Cordialement.

Hello,

Dans le fichier en PJ:

  • Feuille "Listing": elle contient les données qui seront intégrées dans les feuilles générées par la macro "Exporter" (le nom des nouvelles feuilles sont les n° de constat,
  • J'ai crée une macro permettant de supprimer les feuilles générées (ça peut servir).

J'ai réussi à obtenir ce que je voulais a priori, mais j'ai besoin d'obtenir une synthèse classant les constats par type (type 1, type2, type 3).

J'ai construit manuellement cette feuille pour donner une idée de ce que je souhaiterais obtenir.

Tu peux me donner un coup de main? En te remerciant.

Bonjour,

Donc la nouvelle question est de passer de Listing à Synthèse nouveau style ?

Je note déjà des trous dans ton Export : tu remplis 4 champs, mais les champs Thème, Description et Type ne sont pas servis, si on les sert, il reste 2 champs non utilisés de Listing : Nature et Préconisation, et 2 champs non remplis : Traitement apporté et Risque.

Y a-t-il correspondance entre ces champs : Risque = Nature ? Traitement = Préconisation ?

Cordialement.

edit : Je ne suis pas très enclin à travailler avec des cellules fusionnées quand cela ne répond à aucune nécessité !

Hello,

Certains champs sont volontairement oubliés dans la feuille "Synthèse".

Il s'agit simplement d'apporter les éléments essentiels sur cette feuille qui servira de page de garde à un document.

J'ai pris cette (mauvaise) habitude de fusionner des cellules pour avoir une mise en page qui corresponde à ce que je souhaite

Voilà ta procédure. Mais je maintiens que, tout en n'étant pas puriste anti-cellules fusionnées, sauf pour les bases de données où cela les rend ingérables), je pense logique qu'elles correspondent au moins à quelque chose que l'on n'aurait pu obtenir autrement, ce qui n'est pas le cas ici.

Cela apporte des complications inutiles... Je le fais, juste pour montrer qu'on peut quasiment toujours faire ! Note que c'est exceptionnel !

NB- J'avais déjà modifié tes procédures existantes avant ta réponse...

Sub Synthèse()
    Dim d As Object, k, itm, aa, tt, eT, SynT(), n&, i&, t&, j%
    Set d = CreateObject("Scripting.Dictionary")
    aa = ActiveSheet.Range("A4").CurrentRegion
    For i = 2 To UBound(aa)
        k = aa(i, 9): itm = d(k) & ";" & i
        d(k) = itm
    Next i
    tt = d.keys
    For i = LBound(tt) To UBound(tt) - 1
        For j = i + 1 To UBound(tt)
            If tt(j) < tt(i) Then
                k = tt(j): tt(j) = tt(i): tt(i) = k
            End If
        Next j
    Next i
    n = UBound(aa) + d.Count * 3 - 2
    k = Array(0, 9, 3, 5, 7)
    eT = Split("N° constat;Local;Zone;Thème;Entreprise", ";")
    With Worksheets("Synthèse")
        .UsedRange.Offset(5).Clear
        .Range("A6").Resize(n, 3).Merge True
        For j = 4 To 10 Step 2
            .Cells(6, j).Resize(n, 2).Merge True
        Next j
        With .Range("A6").Resize(n, 11)
            .HorizontalAlignment = xlCenter
            .Font.Size = 10
        End With
        t = 6
        For j = LBound(tt) To UBound(tt)
            itm = Split(d(tt(j)), ";")
            ReDim SynT(UBound(itm) + 1, 9)
            SynT(0, 0) = tt(j)
            For i = 0 To UBound(k)
                SynT(1, k(i)) = eT(i)
            Next i
            For n = 1 To UBound(itm)
                For i = 0 To UBound(k)
                    SynT(n + 1, k(i)) = aa(CInt(itm(n)), i + 1)
                Next i
            Next n
            With .Range("A" & t).Resize(n + 1, 10)
                .Value = SynT
                .Rows(2).Interior.Color = vbYellow
            End With
            .Range("A" & t).Resize(, 3).BorderAround xlContinuous, xlThin
            .Range("A" & t + 1).Resize(n, 11).Borders.Weight = xlThin
            t = t + n + 2
        Next j
    End With
End Sub

Cordialement.

Merci à toi.

Je vais regarder tout cela tranquillement, l'idée étant d'essayer de comprendre la macro.

Juste pour que je sache et d'éviter de le refaire à l'avenir, en quoi des cellules fusionnées complexifient les choses?

Bonjour,

Quelques explications un peu plus détaillées sur le code :

Sub Synthèse()
    Dim d As Object, k, itm, aa, tt, eT, SynT(), n&, i&, t&, j%
    Set d = CreateObject("Scripting.Dictionary")
    aa = ActiveSheet.Range("A4").CurrentRegion
    For i = 2 To UBound(aa)
        k = aa(i, 9): itm = d(k) & ";" & i
        d(k) = itm
    Next i

Sur les déclarations : d est destinée à accueillir un dico, les variables non typées (type Variant donc) accueilleront à un moment où un autre des tableaux, SynT est elle déclarée en tableau dynamique, et accueillera chaque bloc de résultat (correspondant à un Type) pour l'affecter tour à tour sur la feuille, variables numériques, Long ou Integer, je me suis aligné sur Long pour celles susceptibles de parcourir la base ou une bonne partie dans la mesure où tu as choisi ce type dans tes autres macros...

[NB- En principe le type Integer est valide jusqu'à 32767 (et -32768 dans le sens négatif), cependant les tests que nous avions effectués il y a quelques temps avec eriiic montraient une plus grande rapidité d'exécution avec le type Long, qui devient sensible dès qu'on atteint quelques milliers (tu pourras noter à l'occasion qu'il type systématiquement en Long, j'ai encore quelques scrupules je reste sur integer pour de petits nombres et passe en Long dès qu'on approche de 1000...), et le type Byte se révélait lui particulièrement lent (là je ne l'utilise en principe jamais).]

Dans cette première étape, on affecte le tableau de données complet à une variable (le résultat est un tableau à 2 dimensions de base 1, toujours), il est plus rapide de travailler sur un tableau que sur la feuille. Et on constitue un dictionnaire d'éléments donc la clé est le Type, et l'Item est constitué par la concaténation des numéros de lignes (dans le tableau) des enregistrements du Type, séparés par des points-virgules (qu'on pourra aisément transformer en tableau à partir de ce séparateur pour les utiliser, le fait que la chaîne commence par un ";" fera que le tableau résultant débutera par un élément vide, l'élément 0, et qu'il aura toujours au minimum 2 éléments).

    tt = d.keys
    For i = LBound(tt) To UBound(tt) - 1
        For j = i + 1 To UBound(tt)
            If tt(j) < tt(i) Then
                k = tt(j): tt(j) = tt(i): tt(i) = k
            End If
        Next j
    Next i

2e étape : on affecte les clés de dico à un tableau. Puis on trie ce tableau par ordre (alphabétique) croissant (selon une méthode classique de tri à bulles...) [Dans ton modèle réduit, les types apparaissaient dans le bon ordre mais il peuvent ne pas l'être...]

A la fin de cette étape, le tableau tt contiendra les Types classés dans l'ordre alphabétique et l'on pourra les appeler à partir de ce tableau pour les avoir dans cet ordre sur la feuille Synthèse.

    n = UBound(aa) + d.Count * 3 - 2
    k = Array(0, 9, 3, 5, 7)
    eT = Split("N° constat;Local;Zone;Thème;Entreprise", ";")

Quelques ligne de code intermédiaires pour préparer les étapes suivantes.

Avec n on dimensionne le nombre de lignes de ta synthèse : ce nombre de lignes comprendra autant de lignes que d'enregistrements dans ta base d'une part, et d'autre part ils sont organisés par bloc correspondant à un Type, chaque bloc comportant outre les enregistrements, une ligne vide suivie de la mention du Type suivie d'une ligne d'en-tête du bloc, soit 3 lignes de plus par type. On ajoute donc l'indice maximal du tableau Base (nb de lignes de ta base) et le nb d'éléments dico (nb de Types) multiplié par 3, mais il faut défalquer la première ligne vide précédant le premier Type inscrit dans Synthèse, car on compte à partir de la ligne en dessous (la ligne 6), ainsi que la ligne d'en-tête de la base qui n'est pas un enregistrement. On a ainsi le nombre exact de lignes de la synthèse, qui sera nécessaire pour reconstituer les fusions de cellules !

La 2e ligne de code affecte un tableau de nombre à la variable k, nombres qui correspondent aux indices colonnes des valeurs à affecter au tableau SynT, soit aux numéros de colonnes -1 (le tableau étant de base 0) de tes données dans Synthèse, classées dans l'ordre où ils apparaissent dans la base : les colonnes 1, 10, 4, 6, 8 de la synthèse accueilleront respectivement les colonnes 1, 2, 3, 4, 5 de la Base. Cette gymnastique est évidemment liée comme la précédente aux fusions de cellules.

Enfin, la 3e ligne utilise Split pour former un tableau eT des mentions d'en-tête de chaque bloc Type dans la synthèse.

Pause café avant de poursuivre...

    With Worksheets("Synthèse")
        .UsedRange.Offset(5).Clear
        .Range("A6").Resize(n, 3).Merge True
        For j = 4 To 10 Step 2
            .Cells(6, j).Resize(n, 2).Merge True
        Next j
        With .Range("A6").Resize(n, 11)
            .HorizontalAlignment = xlCenter
            .Font.Size = 10
        End With

Avec cette 3e étape, on commence à s'attaquer à la feuille Synthèse, c'est la phase de nettoyage, on commence par supprimer l'existant à partir de la ligne 6. La méthode efface les données mais détruit aussi toute la mise en forme (taille de police, bordures, alignements, couleurs, et les fusions de cellules). Il va falloir reconstruire cette mise en forme sur le nombre de lignes dont on a besoin.

On fusionne donc d'abord les 3 premières colonnes. On opére ensuite en boucle pour fusionner 4 groupes de 2 colonnes (l'argument de la méthode Merge, True, indique que l'on fusionne séparément chaque ligne de la plage).

NB- Dès lors que l'on a affaire à des fusions, certaines méthodes et propriétés ne réagissent plus de la même façon... Par exemple, si l'affectation de valeur à une plage fusionnée concerne la cellule supérieure gauche de cette plage, la seule de la plage susceptible d'accueillir une valeur, pour l'effacer il faut par contre inclure toutes les cellules de la plage. Ici, on utilise Cells pour cibler les colonnes à fusionner à la suite de la première fusion, et non Offset qui à partir de cette première fusion introduirait un décalage...

On complète les fusions par les éléments de mise en forme commun à toute la plage : alignement centré et taille de police.

        t = 6
        For j = LBound(tt) To UBound(tt)
            itm = Split(d(tt(j)), ";")
            ReDim SynT(UBound(itm) + 1, 9)
            SynT(0, 0) = tt(j)
            For i = 0 To UBound(k)
                SynT(1, k(i)) = eT(i)
            Next i
            For n = 1 To UBound(itm)
                For i = 0 To UBound(k)
                    SynT(n + 1, k(i)) = aa(CInt(itm(n)), i + 1)
                Next i
            Next n

On passe maintenant à la composition de la synthèse. On initialise préalablement la variable t à 6, première ligne de la feuille à servir, on l'incrémentera ensuite lors de la mise en place de chaque bloc Type pour lui donner la ligne de début du bloc suivant, en fonction du nombre de lignes du bloc affecté.

On amorce une boucle j sur le tableau tt, listant les Types, lors de chaque tour de cette boucle on constitue donc un bloc Type qu'on affecte... Dans cette première partie de la boucle on constitue le bloc (tableau SynT) qu'on aura à affecter dans la seconde partie.

tt(j) nous fournit le Type que l'on traite. Ce Type est aussi la clé de l'élément dico qui contient les lignes de la base concernées par ce Type.

On commence par extraire sous forme de tableau (avec Split) ces numéros de lignes dans la variable itm. On peut ainsi dimensionner le bloc Type qu'on traite : autant de lignes que de numéros de lignes de la base à prendre en compte +2 (mention du Type et en-tête de bloc), 10 colonnes (car les cellules fusionnées non accessibles n'en continuent pas moins d'exister). Le dimensionnement de SynT, en retrait de 1, tient compte de l'élément 0, en ligne et en colonne.

On affecte d'abord au tableau la mention du Type : là c'est simple, une seule valeur en début de ligne, c'est l'élément (0, 0) et la valeur correspond à tt(j) (mention du Type).

On affecte ensuite notre 2e ligne (ligne 1 du tableau SynT), soit les mentions d'en-tête, et on ne sert que les colonnes utiles, listées dans la variable k. On affecte donc au moyen d'une boucle i sur les valeurs de k, les éléments (1, k(i)) du tableau SynT. La valeur de chaque élément est prélevée dans eT(i), tableau des en-têtes de bloc Type que nous avons préalablement constitué. Il n'y a qu'une ligne à servir et les indices restent alignés, pas trop compliqué encore...

Pour le reste du bloc, on a à opérer une double boucle sur les lignes et les colonnes : boucle n sur les lignes consignées dans le tableau itm (qu'on parcourt de 1 à l'indice max de ce tableau, son élément 0 étant vide), n va donc aller de 1 au nombre de lignes à servir, mais pour le tableau SynT on affecte à partir de la ligne qui suit l'en-tête, soit la ligne d'indice 2, soit n+1. Pour la boucle colonne i imbriquée, on affecte comme précédemment les colonnes k(i), donc chaque affectation va concerner l'élément (n+1, k(i)) du tableau SynT. Cependant, si les colonnes du tableau SynT sont numérotées à partir de 0, celles de la bas où l'on ira chercher les valeurs le sont à partir de 1. Donc la valeur affectée à chaque élément de SynT sera : aa(CInt(itm(n)), i+1), itm(n) étant le numéro de ligne de la base (issu de la valeur de l'élément dico splitté, de type String, d'où sa conversion en nombre). Là tu peux voir que sans les fusions, les boucles auraient tout de même été nettement plus simples !

Ayant constitué le bloc Type dans le tableau SynT, il ne reste plus qu'à l'affecter à la feuille :

            With .Range("A" & t).Resize(n + 1, 10)
                .Value = SynT
                .Rows(2).Interior.Color = vbYellow
            End With
            .Range("A" & t).Resize(, 3).BorderAround xlContinuous, xlThin
            .Range("A" & t + 1).Resize(n, 11).Borders.Weight = xlThin
            t = t + n + 2
        Next j
    End With

On dimensionne la plage cible de la feuille, à partir de la cellule en A, ligne t. Au sortir de la boucle n de constitution du tableau SynT, n aura été incrémentée, et aura donc une valeur correspondant au nombre de lignes d'enregistrements du bloc +1, le dimensionnement du bloc correspondant à ce nombre de lignes +2, on peut dimensionner la plage cible en lignes à n+1, et en colonnes à 10 (nombre fixe).

On affecte donc SynT. Ça, opération simple ! Et on en profite pour colorer la ligne 2 du bloc en jaune (ligne d'en-tête).

Il reste à achever la mise en forme : d'abord border la mention du type en première ligne, sur la première cellule, mais il faut dimensionner cette cellules aux 3 colonnes fusionnées pour que la bordure les englobe. Ensuite on borde le reste, à partir de la ligne en dessous, en dimensionnant sur le nombre de ligne du bloc -1, donc n, et en colonnes sur 11 et non plus 10, car la colonne J est fusionnée avec K, à inclure pour les bordures...

Enfin, on incrémente t pour cibler la première ligne du bloc suivant, en ajoutant n+2, de façon à laisser une ligne vide entre deux blocs.

La procédure s'achèvera quand tous les blocs auront été inscrits.

Cordialement...

J'ai regardé de près le code, et en exécutant la macro pas à pas, j'y vois plus clair, mais pas suffisamment pour l'adapter parfaitement à mon besoin:

  • Dans la fiche synthèse, j'ai l'ordre suivant (N° constat - Zone - Thème - Entreprise - Local). Quel paramètre dans la macro dois-je changer pour les afficher dans l'ordre que je souhaite?
  • Aussi, comment faire pour ajouter un item, comme par exemple "Préconisation"?

En te remerciant.

Il faut prendre le déroulement pas à pas et modifier les éléments correspondants.

Si tu veux placer les éléments dans un ordre différents, il faut modifier leur ordre dans les tableaux qui le définissent, il convient que tu indiques quel ordre tu voudrais pour que je puisse t'illustrer les lignes à modifier.

De même si tu veux ajouter un élément (ou en enlever), cela se jouera sur les mêmes lignes...

Pas de gros bouleversement mais il ne faut pas se tromper. Tant qu'on ne touche pas à la structure (organisée à partir du Type), les modifications resteront mineures et relativement faciles une fois repérés leurs emplacements.

Cordialement.

Rechercher des sujets similaires à "macro copie donnees seule feuille"