Créer une macro qui permet de copier/coller des colonnes succéssivement dans une boucle

Excellerateur · Juillet 28, 2021, 2:27

Salut l’équipe!

Ceci est ma première contribution.

En effet je cherche à créer une macro VBA qui fera une boucle qui va dans chaque cycle faire un tri (du plus grand au plus petit) sur les colonnes « C », « D », « E », « F », « G », et « H ».

Puis en 2nde étape je veux que le code copie la liste des 4 premières cellules de la colonne « A » donc les range(« A3:A6 ») et la colle sur une autre feuille (feuille A, range((« A3:A6 »), puis sur la feuille Calculs ratios vient copier la liste triée en colonne « C » et la colle à la suite de la copie précédente (sur feuille A, range(« B3:B6 »)).

Puis par la suite sur la feuille Calculs ratios, après avoir fait le tri (du plus grand au plus petit) sur la colonne « D » on copie encore les range(« A3:A6 ») que l’on colle sur la feuille A en range((« D3: D6 »), ensuite on revient sur la feuille Calculs ratios, on copie la liste triée en colonne « D » et la colle à la suite de la copie précédente (sur feuille A, range(« E3:E6 »)).

Et ainsi de suite jusqu’à remplir les 6 tableaux en feuille A. Sur le modèle réel chaque tableau à créer est sur un autre onglet et il y a bien plus de 6 tableaux à créer d’où tout l’intérêt d’un code.

Le fait de trier du plus grand au plus petit à chaque fois change l’ordre des intitulés en colonne « A » donc j’ai réellement besoin de créer une boucle pour cela.

Vous pouvez vous servir du fichier pour le comprendre.

Merci d’avance de votre attention.

Excellemment votre.

Fichier joint.

Copie de pays-et-ratios-v11.xlsx (16,5 Ko)

FFO · Juillet 28, 2021, 3:46

Bonjour

Ci-joint ma proposition

Onglet « Calculs ratios » cliques sur le bouton « Recopie » en cellule V4

Va constater le résultat onglet « Feuille A »

Attention aucune donnée après celles à traiter en colonne A onglet « Calculs ratios » j’ai déplacé celles qui étaient présentes en colonne B et C

Fichier de type « .xlsm » pour le besoin des traitements en VBA à maintenir impérativement

Testes et dis moi

Copie de pays-et-ratios-v11.xlsm (29,4 Ko)

Excellerateur · Juillet 28, 2021, 4:13

Bonjour @FFO!

Déjà merci infiniment pour ton temps et l’apport précieux que tu apportes à mon sujet.
Je vais être honnête, je suis encore un « DEBUTANT » sur vba et je ne comprends pas tout à fait tout ce que tu as écris dans ton code qui ne semble pour tant pas si compliqué que ça…
Je n’ai par exemple pas compris toutes les variables (ou fonctions ) de cette partie du code.

Range("A3:" & Cells(derligne, i).Address).Sort key1:=Range(Cells(3, i).Address), order1:=xlDescending, Header:=xlYes

Puis pour le reste j’essaye de comprendre progressivement, mais je crois bien que la fonction cells a un truc à jouer dans ce jeu.

Excellemment votre

FFO · Juillet 28, 2021, 5:10

Cette ligne sert pour le tri qui évolue dans la boucle au niveau de la colonne
C’est pour cela que j’utilise l’instruction Cells qui me permet d’utiliser la variable i évoluant à chaque tour de boucle (le tri change de colonne à chaque fois)
Puis de l’inclure dans la partie d’instruction qui nécessite de cibler 2 cellules Donc d’utiliser l’instruction Range qui ne fonctionne qu’avec la lettre de la colonne et son numéro de ligne d’où pour l’obtenir avec l’instruction Cells d’associer l’instruction Address
Je peux ainsi cibler les 2 cellules pour le tri (première et dernière cellule de la plage à trier) et la faire évoluer en fonction de la colonne concernée avec l’instruction Cells

Voilà pour les explications

Excellerateur · Juillet 29, 2021, 8:12

OK je regarde ça et je te dis si je m’en sors.

Merci pour ton temps et ta très généreuse contribution.

Excellerateur · Juillet 29, 2021, 3:53

Je t’avoue que j’essaye d’intégrer ton code dans le miens mais ça ne marche pas.
Je ne sais pas où je commets l’erreur.
Je sais entre autre que moi je fais un copier/coller plutôt que d’une correspondance de cellules, mais je n’y arrive toujours pas.

FFO · Juillet 29, 2021, 4:16

Peux tu me transmettre ton fichier pour que je regarde
Avec un minimum de données non confidentielles

Excellerateur · Juillet 30, 2021, 10:17

Merci beaucoup pour toute ton aide sur mon problème.

En effet il n’y a pas moyen de modifier le fichier car il a trop d’informations et du coup est assez lourd en soit, avec plusieurs fichiers imbriqués entre eux.
Je ne sais pas si je peux te montrer le code correspondant qui a quelques commentaires.

Sub maj_plus_gros_porteurs ()

'Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim repertoire As String
Dim passif As String
Dim fso As Object
Dim wb1 As Workbook
Dim wb4 As Workbook
Dim Nom_onglet As String
Dim Nom_fonds As String
Dim Nb_fonds As Integer

Sheets(« plus gros porteurs »).Delete
Sheets(« total redemption »).Delete
Sheets(« Reporting Liquidité »).Delete

Set wb1 = Workbooks(« SETUP Liquidite 2020 V2 »)
Set fso = CreateObject(« Scripting.FileSystemObject »)
repertoire = « X:\Cont-Out\Controle »
passif = wb1.Sheets(« SETUP »).Range(« M18 »).Value
Workbooks.Open Filename:=repertoire & passif

Set wb4 = ActiveWorkbook
’ Mainenant on colle les trois onglets (« plus gros porteur », « redemption » et « Liquidité »)
'on les colle respectivement après l’onglet « Fixed Income » de la feuille « SETUP Liquidite 2020 »
wb4.Sheets(« plus gros porteurs »).Copy After:=wb1.Sheets(« Fixed Income »)
wb4.Sheets(« redemption »).Copy After:=wb1.Sheets(« Fixed Income »)
wb4.Sheets(« Liquidité »).Copy After:=wb1.Sheets(« Fixed Income »)
wb4.Close savechanges:=False

Nb_fonds = wb1.Sheets(Setup).Range(« A7 »).Value

For i = 1 To Nb_fonds

Nom_onglet = Sheets(« SETUP »).Range(« C » & i + 7).Value
Nom_fonds = Sheets(« SETUP »).Range(« D » & i + 7).Value

'on efface d’abord l’ancienne liste de top 5 client sur chaque onglet de fonds
Sheets(Nom_onglet).Range(« Y14 », « Z18 »).ClearContents

'on selectionne le fonds correspondant en faisant un filtre
Sheets(« plus gros porteurs »).Range(« B4 »).AutoFilter Criteria1:=Array(Nom_fonds)

'on fait la boucle qui va selectionner chaque fonds puis copier/coller la liste des 5 plus gros porteurs avec le montant correspondant à chaque fois

If Sheets(« plus gros porteurs »).Range(« B4 »).Value = Nom_fonds Then

Sheets(« plus gros porteurs »).PivotTables(« Tableau croisé dynamique15 »).PivotFields(« Nom du Compte Titre »).AutoSort xlDescending, « Somme de Encours Fin de Periode », Sheets(« plus gros porteurs »).PivotTables(« Tableau croisé dynamique15 »).PivotColumnAxis.PivotLines(1), 1
Sheets(« plus gros porteurs »).Range(« A5 », « A9 »).Copy
Sheets(Nom_onglet).Range(« Y14 »).PasteSpecial xlPasteValues

'ici il est question de mettre une fonction qui va permettre de décaler la cellule à copier vers la droite car étant donné que c’est un TCD, la copie de cellule en dur pourrait poser problème à l’avenir.
Sheets(« plus gros porteurs »).Range(« B5 », « B9 »).Copy

Sheets(Nom_onglet).Range(« Z14 »).PasteSpecial xlPasteValues

Sheets(Nom_onglet).Range(« Z1 ») = Application.WorksheetFunction.SumIf(Sheets(« Inventaire »).Columns(« E »), _
Sheets(Nom_onglet).Range(« Y2 »), Sheets(« Inventaire »).Columns(« AG »))
End If

Next i

End Sub

Dans l’image j’ai surligné la partie dans laquelle je crois qu’on devrait ajouter la fonction pour aller selectionner la cellule de droite comme demandé dans mon message précédant.

J’espère que mon illustration n’est pas trop compliquée à comprendre.

Merci encore.

FFO · Juillet 30, 2021, 10:38

Cette fonction à rajouter concerne ma procédure ?

Excellerateur · Juillet 30, 2021, 11:45

Oui elle concerne ta procédure

Excellerateur · Juillet 30, 2021, 1:26

Pour encore illustrer ma requête, la voici en image.
Là c’est l’onglet sur lequel il y a les tables afiltrer. (Comme je l’ai dit, il s’agit d’un TCD).

Moi je voudrais en fait être capable de faire les tris (du plus grand au plus petit) dans chaque colonne (entre les colonnes B et F dans l’image, mais bcp plus dans le fichier) puis, une fois le tri fait, je veux copier la plage"A5:A9" puis maintenant en fonction de ce que le tri va donner comme nouveau classement je copie à chaque fois la plage « B5:B9 ».
Et tout ceci sans avoir de cellule en dur dans mon code.

FFO · Juillet 30, 2021, 2:41

Ma procédure n’est pas une fonction mais une macro

Je te propose dans le fichier joint un nouveau traitement qui s’inspire de ta présentation (colonne A les titres à recopier et à partir de la colonne B les valeurs à trier nombre de colonnes non limité)

Ce traitement à partir de la colonne B trie chaque colonne de la valeur la plus grande à la valeur la plus petite puis reporte dans l’onglet « Feuille A » à partir de la colonne A toutes les 2 colonnes les 4 premiers enregistrements première colonne et les 4 valeurs correspondantes colonne de tri onglet source

Reporte les lignes de code de la macro « Recopie2 » dans un module dans ton fichier adaptes la première ligne en fonction du nom de l’onglet cible à remplir :

cible = « Feuille A »

Cet onglet cible sera complété à l’identique de l’onglet « Feuille A » de ce fichier à partir de la colonne A

Onglets source actif à l’écran exécute cette macro toute seule et regarde le résultat onglet cible
Sa structure doit être identique à celle de l’onglet « Feuil1 » (entête des colonnes en ligne 2 données à trier et libellé à recopier colonne A en dessous)

Fais moi un retour

Le nouveau fichier avec ce traitement ci-joint

Tu peux en préambule faire un essai onglet « Feuil1 » bouton « Recopie » résultat « Feuille A » en mettant autant de colonne à trier que tu veux
Attention ces colonnes doivent toutes avoir une entête en ligne 2
Va constater le résultat onglet « Feuille A »

Commence par ces essais puis l’assemblage avec tes traitements sera fait dans un 2° temps

Copie de pays-et-ratios-v12.xlsm (35,0 Ko)

Excellerateur · Juillet 30, 2021, 2:51

Ok je regarde ça toute de suite et je te fais un retour.

Honnêtement tu es hyper impressionnant. Tu as tellement bien cerné la problématique.
Il n’y a rien à dire t’es un génie. En plus tu expliques encore mieux le sujet que moi même qui l’ai proposé.

Mais merci déjà pour ton soutien

FFO · Juillet 30, 2021, 4:23

Trop de compliments mais ravi de t’aider
en début de code mets ces lignes :

feuillesource = « Feuil1 »
cible = « Feuille A »
Sheets(feuillesource).Activate

et ainsi à son exécution de cibler la bonne source et cible et de rendre actif à l’écran celui source

Bien adapter ces variables en fonction des noms de ton fichier

Fais moi un retour et si OK on fera le nécessaire pour intégrer l’ensemble

Excellerateur · Juillet 30, 2021, 5:04

En effet j’ai testé ta macro qui marche très bien.

J’ai aussi testé avec la macro « Recopie » sur l’onglet « Feuil1 » (je me suis amusé à ajouter le nombre de colonne comme tu l’as dit et ça a marché également).

Depuis je traine à intégrer ton code dans mon module,j’ai un petit bug (bien avant la partie modifiée) qui me bloque un peu.

J’ai tout de même un peu modifié la structure de ton code qui est « on ne peut plus édifiant ».
J’explique:
ici dans l’exemple j’ai voulu faire apparaitre les valeurs copiées de l’onglet source (onglet « Calculs ratios ») sur un seul onglet cible (onglet feuille A). Pourtant dans mon fichier d’origine il y a autant d’onglet cible qu’il n’y a de colonne à trier dans l’onglet source; et à chaque fois les données recopiées sont collées dans le même emplacement dans les onglets cibles (à chaque fois dans les range(« Y14:Y18 ») et les Range(« Z14:Z18 »). (Oui en fait je cherche les 5 plus gros au lieu des 4 comme dans mon exemple).

D’où l’écriture suivante:

Sheets(Nom_onglet).Range("Y14").Value = Range(Cells(5, 1).Address, Cells(9, 1).Address).Value
Sheets(Nom_onglet).Range("Z14").Value = Range(Cells(5, i).Address, Cells(9, i).Address).Value

à la place de cette partie de ton code:

Sheets(cible).Range(Cells(3, colonne).Address, Cells(6, colonne).Address).Value = Range(Cells(3, 1).Address, Cells(6, 1).Address).Value
Sheets(cible).Range(Cells(3, colonne + 1).Address, Cells(6, colonne + 1).Address).Value = Range(Cells(3, i).Address, Cells(6, i).Address).Value

Bien entendu moi j’ai Nom_onglet à la place de cible car j’ai bien défini en amont à quoi cela renvoie:

Nom_onglet = Sheets("SETUP").Range("C" & i + 7).Value

comme ça, je ne me pose pas la question de l’onglet cible est réglée.

Je ne sais pas si tu vois…

FFO · Juillet 30, 2021, 5:23

As tu intégré dans ton fichier ma macro dans un module toute seule puis adapter les première lignes de codes en fonction des noms d’onglet correspondant :

feuillesource = « Feuil1 »
cible = « Feuille A »

A supposer que la source soit « Calculs ratios » la cible « feuille A » mettre :

feuillesource = « Calculs ratios »
cible = « Feuille A »
ce qui donne ce code :

Sub Recopie2()
feuillesource = « Calculs ratios »
cible = « Feuille A »
Sheets(feuillesource).Activate
Sheets(cible).Range(« A3 », « Q » & Sheets(cible).Range(« A » & Rows.Count).End(xlUp).Row + 1).ClearContents
colonne = 1
derligne = Range(« A » & Rows.Count).End(xlUp).Row
dercolonne = Cells(2, Columns.Count).End(xlToLeft).Column
For i = 2 To dercolonne
ActiveSheet.Sort.SortFields.Clear
Range(« A3: » & Cells(derligne, i).Address).Sort key1:=Range(Cells(3, i).Address), order1:=xlDescending, Header:=xlYes
Sheets(cible).Range(Cells(3, colonne).Address, Cells(6, colonne).Address).Value = Range(Cells(3, 1).Address, Cells(6, 1).Address).Value
Sheets(cible).Range(Cells(3, colonne + 1).Address, Cells(6, colonne + 1).Address).Value = Range(Cells(3, i).Address, Cells(6, i).Address).Value
colonne = colonne + 3
Next

End Sub

A mettre par exemple à la suite de ton traitement comme ceci :

Puis de l’exécuter ?

FFO · Juillet 30, 2021, 6:17

"sont collées dans le même emplacement dans les onglets cibles (à chaque fois dans les range(« Y14:Y18 ») et les Range(« Z14:Z18 »)

Il faudrait peut être au lieu d écrire comme ceci :

Sheets(Nom_onglet).Range(« Y14 »).Value = Range(Cells(5, 1).Address, Cells(9, 1).Address).Value

Mettre comme ceci

Sheets(Nom_onglet).Range(« Y14 »,« Y18 »).Value = Range(Cells(5, 1).Address, Cells(9, 1).Address).Value

Idem pour l autre ligne

Mettre comme ceci :

Sheets(Nom_onglet).Range(« Z14 »,« Z18 »).Value = Range(Cells(5, 1).Address, Cells(9, 1).Address).Value

Essaies et dis moi

Excellerateur · Août 2, 2021, 8:51

Désolé pour le retard de réaction, le week-end je n’avais pas accès à l’ordinateur.

En effet, je ne sais pas si c’est plutôt moi qui m’y prends mal dans l’incrémentation de ton code dans un module de mon fichier.

j’ai bien redéfini (fin je crois) les nouvelles feuilles à remplacer comme tu me l’as dit.
La seule différence est que moi j’ai plusieurs cibles (les feuilles cibles varient en fonction de la colonne qui est sélectionnée dans la « feuille source »). ce qui correspond au « Nom_onglet » que j’ai défini dans mon code comme tu peux le voir plus haut.
En fait j’ai mis les noms des onglets dans un tableau sur le sheets(setup).

La procédure que tu me proposes me semble tout à fait correcte et faisable même avec mon niveau il faudrait juste que je prête attention pour voir ce que j’ai omis.

Je suis à bout avec ce code

FFO · Août 2, 2021, 11:37

Boujour

As tu essayé de mettre mon traitement comme je te l’ai proposé dans une macro à part non intégrée dans la tienne et de la faire fonctionner ?

Excellerateur · Août 3, 2021, 3:23

Oui j’ai bien mis ton traitement dans une une macro séparée et elle marche.
Je suis en effet en train de débloqué la mienne grâce aux pistes que tu m’as proposé. Normalement ça devrait le faire ce soir car je suis sur un sujet à livrer puis je me mets dessus.