Lister les valeurs de 2 colonnes dans 2 feuilles sur une seule colonne

Le 18/12/2017 à 23:35

Bonjour à tous

Un problème me tient tête depuis ce matin sur Excel, j'ai donc décidé de m'inscrire sur ce forum pour tacher d'y trouver une réponse et ne pas complètement perdre ma journée.

J'ai plusieurs fichiers Excel qui présentent des données organisées de la même manière et je souhaite récupérer automatiquement toutes ces données les unes à la suite des autres sur un seul et même fichier qui présente lui aussi la même organisation des données avec les mêmes colonnes.

Les deux "sous-fichiers" s'appellent Ventes_FR.xslx et Ventes_EN.xslx et le fichier "parent" s'appelle "Ventes_totales".

Dans la 1ère colonne que je souhaite remplir, je déclare que si la cellule A2 du fichier "Ventes_FR" n'est pas vide, alors on écrit cette valeur dans la cellule A2 de "Ventes_totales" et ainsi de suite. Si la cellule est vide, cela veut dire que nous sommes arrivé à la fin de la colonne A du fichier "Ventes_FR" et je souhaite donc commencer à lister toutes les cellules de la colonne A du fichier "Ventes_EN" avec la même logique, ce qui donne le code suivant :

 =SI([Ventes_FR.xlsx]Feuille1!A2<>"";[Ventes_FR.xlsx]Feuille1!A2;SI([Ventes_EN.xlsx]Feuille1!A2<>
[Ventes_EN.xlsx]Feuille1!A2;FIN))

En faisant un drag and drop de A2 jusqu'à A100 (admettons) sur le fichier "Ventes_totales", la cellule A2 va s'incrémenter pour devenir A3, puis A4 et ainsi de suite.

Actuellement, le code fonctionne jusqu'à la fin du premier fichier "Ventes_FR". Le problème étant qu'il va amorcer le fichier "Ventes_EN" avec la même valeur de la colonne A que dans le fichier "Ventes_FR". Si dans "Ventes_FR" il y avait 42 valeurs, la lecture des valeurs de "Ventes_EN" va commencer à partir de A42.

Je recherche donc une manière de faire commencer Ventes_EN dès le début, à la cellule A2. J'ai tenté beaucoup de choses mais sans succès et maintenant je suis un peu tout embrouillé ... J'espère que j'ai tout de même réussi à me faire comprendre !

Merci d'avance et bonne soirée.

Le 19/12/2017 à 00:06

Bonsoir,

je pense qu'il te faudra passer par une macro.

une proposition

Sub aargh()
    rep = "d:\downloads\2017\"    'répertoire dans lequel se trouvent les fichiers ventes
    wbl = Split("ventes_fr.xlsx,ventes_en.xlsx", ",") 'nom des fichiers ventes
    Set wst = ThisWorkbook.Sheets("total") 'nom de feuille de total_vente
    dlt = wst.Cells(Rows.Count, 1).End(xlUp).Row 'nombre de lignes présentes sur wst
    For i = LBound(wbl) To UBound(wbl) 'on prend chaque fichier ventes
        Set wb = Workbooks.Open(rep & wbl(i)) 'on l'ouvre
        Set ws = wb.Sheets("feuille1") 'nom de feuille dans ventes 'ws = feuille de ventes contenant les données qui nous intéressent
        dl = ws.Cells(Rows.Count, 1).End(xlUp).Row 'dl N° dernière ligne à copier
        ws.Range("A2:A" & dl).Copy wst.Cells(dlt, 1) ' on copie la plage de ventes vers total_vente
        dlt = dlt + dl - 1 'on ajuste le nombre de lignes présentes sur wst
        wb.Close 'on ferme le fichier
    Next 'on passe au fichier ventes suivant
End Sub
Le 19/12/2017 à 00:16

Waw ! Je me débrouille en C/C++ donc je comprends où tu veux en venir avec cette macro mais je n'ai jamais fais de VBA de ma vie. Je pense mettre du temps avant de savoir exploiter ce code (même si in fine ça me serait vraiment profitable ...)

N'y a t'il cependant pas d'autres moyens avec de simples fonctions Excel ?

Merci beaucoup

Le 19/12/2017 à 10:45

bonjour,

N'y a t'il cependant pas d'autres moyens avec de simples fonctions Excel ?

Peut-être un autre membre du forum aura-t-il une idée ?

Le 23/12/2017 à 17:53

Bonjour ! J'ai vraiment besoin de ce code donc j'ai décidé de regarder d'un peu plus près le code VBA que vous m'avez fournit

Il y a cependant des choses que je ne comprends toujours pas et le code ne fonctionne pas chez moi. Je compte donc sur vous pour m'aiguiller

J'ai l'impression de comprendre l'essentiel, même si je serais aujourd'hui incapable de le retaper moi-même. J'ai juste du mal à comprendre ce qui se passe du côté de la déclaration de la variable wbl plus d'autres choses que j'ai mis en commentaires après les vôtres, ligne par ligne.

Sub mise_en_commun()
    rep = "C:\Users\DEVICE\Desktop\Tests"    'répertoire dans lequel se trouvent les fichiers ventes 'Ok
    wbl = Split("Ventes_FR.xlsx,ventes_EN.xlsx", ",") 'nom des fichiers ventes 'Selon ce que j'ai trouvé sur le net, la fonction Split permet de diviser une chaine en plusieurs sous-chaine. Je ne comprends donc pas le résultat qu'elle est sensé produire dans la variable wbl 
    Set wst = ThisWorkbook.Sheets("Nov") 'nom de feuille de total_vente 'wst est déclaré en tant qu'objet si j'ai bien compris et copie ainsi les valeur de la feuille "Nov" dans le classeur où est lancé la macro  
    dlt = wst.Cells(Rows.Count, 1).End(xlUp).Row 'nombre de lignes présentes sur wst ' dlt prend comme valeur le nombre de ligne du début jusqu'à la fin de la feuille "Nov"
    For i = LBound(wbl) To UBound(wbl) 'on prend chaque fichier ventes 'Ok, admettons ^^ 
        Set wb = Workbooks.Open(rep & wbl(i)) 'on l'ouvre 'On ouvre le classeur étant dans le répertoire précisé plus tôt avec les différents fichiers de ventes compris dans la variable wbl ... Je ne comprends juste pas le "i", qui doit s'incrémenter de 1 à chaque boucle j'imagine.
        Set ws = wb.Sheets("Nov") 'nom de feuille dans ventes 'ws = feuille de ventes contenant les données qui nous intéressent
        dl = ws.Cells(Rows.Count, 1).End(xlUp).Row 'dl N° dernière ligne à copier 'dl prend la valeur du nombre de lignes pleines 
        ws.Range("A2:A" & dl).Copy wst.Cells(dlt, 1) ' on copie la plage de ventes vers total_vente 'Ok 
        dlt = dlt + dl - 1 'on ajuste le nombre de lignes présentes sur wst 'Ok 
        wb.Close 'on ferme le fichier ' Ok 
    Next 'on passe au fichier ventes suivant 'Ok
End Sub

Je vous remercie d'avance pour votre aide.

Le 24/12/2017 à 11:31

bonjour

Sub mise_en_commun()
    rep = "C:\Users\DEVICE\Desktop\Tests"    'répertoire dans lequel se trouvent les fichiers ventes 'Ok
    wbl = Split("Ventes_FR.xlsx,ventes_EN.xlsx", ",") 'nom des fichiers ventes 'Selon ce que j'ai trouvé sur le net, la fonction Split permet de diviser une chaine en plusieurs sous-chaine. Je ne comprends donc pas le résultat qu'elle est sensé produire dans la variable wbl

wbl est un tableau qui contient le nom des fichiers. j'aurais pu écrire wbl=array("Ventes_FR.xlsx","ventes_EN.xlsx")

    Set wst = ThisWorkbook.Sheets("Nov") 'nom de feuille de total_vente 'wst est déclaré en tant qu'objet si j'ai bien compris et copie ainsi les valeur de la feuille "Nov" dans le classeur où est lancé la macro

wst me permettra d'éviter de retaper ThisWorkbook.Sheets("Nov") lorsque je voudrai faire référence à cette feuille

    dlt = wst.Cells(Rows.Count, 1).End(xlUp).Row 'nombre de lignes présentes sur wst ' dlt prend comme valeur le nombre de ligne du début jusqu'à la fin de la feuille "Nov"

dlt prend le numéro de la dernière cellule non vide de la colonne 1

    For i = LBound(wbl) To UBound(wbl) 'on prend chaque fichier ventes 'Ok, admettons ^^

lbound(wbl) indice du premier élément du tableau wbl

ubound(wbl) indice du dernier élément du tableau wbl

        Set wb = Workbooks.Open(rep & wbl(i)) 'on l'ouvre 'On ouvre le classeur étant dans le répertoire précisé plus tôt avec les différents fichiers de ventes compris dans la variable wbl ... Je ne comprends juste pas le "i", qui doit s'incrémenter de 1 à chaque boucle j'imagine.

oui, ton imagination ne te fait pas défaut

        Set ws = wb.Sheets("Nov") 'nom de feuille dans ventes 'ws = feuille de ventes contenant les données qui nous intéressent
        dl = ws.Cells(Rows.Count, 1).End(xlUp).Row 'dl N° dernière ligne à copier 'dl prend la valeur du nombre de lignes pleines

dl = numero de ligne de la dernière cellule non vide (colonne 1) de ws

   ws.Range("A2:A" & dl).Copy wst.Cells(dlt, 1) ' on copie la plage de ventes vers total_vente 'Ok 
        dlt = dlt + dl - 1 'on ajuste le nombre de lignes présentes sur wst 'Ok 
        wb.Close 'on ferme le fichier ' Ok 
    Next 'on passe au fichier ventes suivant 'Ok
End Sub
Le 26/12/2017 à 15:04

Super ! Ca fonctionne ! Je tente maintenant d'enrichir le code existant en le rendant plus adaptable. En ce sens, j'essaye de faire en sorte que le tableau de la variable wbl ne contienne pas seulement le nom des fichiers .xlsx que l'on renseigne, mais bien tous les fichiers présents dans le dossier. Après quelques recherches, cela donnerait le résultat suivant :

Sub mise_en_commun()

    rep = "C:\Users\DEVICE\Desktop\Tests\"    'répertoire dans lequel se trouvent les fichiers ventes

    Dim mois As String, dossier As String
    Application.ScreenUpdating = False

    mois = ActiveWorkbook.ActiveSheet.Name 'La variable mois prends la valeur du nom de la feuille excel
    dossier = rep & "Ventes_par_pays\"
    wbl = Array(dossier & "*.xlsx*") 'Créé un tableau avec les différents noms des tableaux présents dans le dossier "Ventes_par_pays"

    Set wst = ThisWorkbook.Sheets(mois) 'variable comprenant la feuille dans laquelle nous voulons voir apparaître le résultat

    dlt = wst.Cells(Rows.Count, 1).End(xlUp).Row 'nombre de lignes présentes sur wst 

    For i = LBound(wbl) To UBound(wbl) 'on prend chaque fichier ventes du plus petit indice jusqu'au plus grand indice

        Set wb = Workbooks.Open(rep & wbl(i)) 'on l'ouvre 
        Set ws = wb.Sheets("Nov") 'nom de feuille dans ventes 
        dl = ws.Cells(Rows.Count, 1).End(xlUp).Row 'dl N° dernière ligne à copier

        ws.Range("A2:A" & dl).Copy wst.Cells(dlt, 1) ' on copie la plage de ventes vers total_vente
        dlt = dlt + dl - 1 'on ajuste le nombre de lignes présentes sur wst

        wb.Close 'on ferme le fichier

    Next  'on passe au fichier ventes suivant

    Application.ScreenUpdating = True

End Sub

Mais une "erreur d'execution 1004" apparait. Je me doute que c'est la ligne "wbl = Array(dossier & "*.xlsx*")" qui bloque mais je ne sais pas comment résoudre le problème.

Le 26/12/2017 à 15:56

bonjour,

proposition de correction (je n'ai pas pu tester), ne fonctionne pas sur mac

Sub mise_en_commun()

    rep = "C:\Users\DEVICE\Desktop\Tests\"    'répertoire dans lequel se trouvent les fichiers ventes

    Dim mois As String, dossier As String
    Application.ScreenUpdating = False

    mois = ActiveWorkbook.ActiveSheet.Name    'La variable mois prends la valeur du nom de la feuille excel
    dossier = rep & "Ventes_par_pays\"

    Set wst = ThisWorkbook.Sheets(mois)    'variable comprenant la feuille dans laquelle nous voulons voir apparaître le résultat

    dlt = wst.Cells(Rows.Count, 1).End(xlUp).Row    'nombre de lignes présentes sur wst
    nf = Dir(dossier & "*.xlsx*")    'on met dans nf le premier fichier qui correspond au filtre
    While nf <> ""    'tant qu'il y a un fichier
        Set wb = Workbooks.Open(dossier & nf)    'on l'ouvre
        Set ws = wb.Sheets("Nov")    'nom de feuille dans ventes, il faudra sans doute corriger ceci
        dl = ws.Cells(Rows.Count, 1).End(xlUp).Row    'dl N° dernière ligne à copier

        ws.Range("A2:A" & dl).Copy wst.Cells(dlt, 1)    ' on copie la plage de ventes vers total_vente
        dlt = dlt + dl - 1    'on ajuste le nombre de lignes présentes sur wst

        wb.Close    'on ferme le fichier
        nf = Dir()   'on passe au fichier ventes suivant
    Wend

    Application.ScreenUpdating = True

End Sub
Le 02/01/2018 à 16:59

Bonjour H2so4 !

De l'eau a coulé sous les ponts et les fêtes n'ont pas favorisé le processus ... Mais j'ai réussi à intégrer le code et à y implémenter bien d'autres fonctionnalités ! Je n'aurais jamais pensé faire cela sous VBA un jour ! Je voulais donc commencer par vous remercier.

Je suis arrivé à un stade de débogage et tout commence à fonctionner du tonnerre sauf une petite erreur qui va finir par me rendre fou ...

Vous trouverez en pièce-jointe un fichier xlsx. on ne peut plus simple : Une liste de date au format jour/mois/année où je ne souhaite garder que le jour. En le faisant manuellement et en enregistrant la macro à côté, il suffit d'utiliser la conversion de données et de supprimer ce qui se trouve après 2 caractères ou bien après un "/".

Pourtant lorsque j'implémente le code dans mon programme complet, je me retrouve avec des inepties du genre "42324" UNIQUEMENT sur certaines valeurs et pas toutes. C'est à n'y rien comprendre, j'ai surmonté beaucoup de difficultés pour en arriver jusque là mais là c'est juste incompréhensible.

Je vous remercie d'avance pour vos lumières.

10envoi-quest.xlsx (9.60 Ko)
Le 02/01/2018 à 18:05

bonsoir,

pour avoir le jour correspondant à une date

d=day(cells(1,1))
Le 03/01/2018 à 00:20

Cette fois-ci ça y est, mon problème est complètement résolu ... Merci pour tout.

Rechercher des sujets similaires à "lister valeurs colonnes feuilles seule colonne"