Copier des données d'une feuille à d'autres selon conditions
Bonjour,
Je suis nul en VBA et avec les formules je n'arrive pas à faire ce que je veux. j'arrive bien à copier les données avec une formule "SI" mais je me retouve avec des lignes vides dans les feuilles
J'ai un classeur avec plusieurs feuilles (journal, banque, livret, badnet et caisse.
Dans les 5 feuilles j'ai 9 colonnes, dans la feuiile journal la colonne A contient une liste déroulante contenant du texte (CA, LI, BA, ES).
Je voudrais que la macro recherche le texte de la feuille journal en colonne A et ensuite copie les données des 9 colonnes dans les 4 autres feuilles mais sans laisser de lignes vides et en répartissant. Si le texte est CA=Banque, LI=Livret, BA=Badnet, ES=Caisse.
Ensuite comme en colonne 3 des 5 feuilles je rentre des dates et je voudrai que la macro fasse le tri par date.
J'espère que je me suis exprimé clairement.
Merci de votre aide.
Philippe
Bonjour Galfy
Je suis nul en VBA et avec les formules je n'arrive pas à faire ce que je veux
Vous savez qu'il existe des formations
Sinon tout simplement, vous pouvez utiliser les filtres
Dans journal, vous sélectionnez A2 puis dans le menu "Données" -> Filtrer (entonnoir)
La vous filtrer chaque type de pièce, "BA" pour commencer
Vous décochez "(Sélectionner tout)" et vous cochez BA
Vous sélectionnez les cellules B3:I10 et CTRL+C (= copier)
Vous allez dans la feuille "Badnet", sélectionnez la première cellule vide dans A et CTRL+V (= coller)
Simple non
A+
Bonjour
Une solution sans VBA : on ventile via PowerQuery
Quand la source évolue : Données, Actualiser Tout
Bonjour,
Cela je sais le faire mais il y a plusieurs inconvénients car je remplis le tableau au fur et à mesure. Du coup je risque me retrouver avec des doublons et lorsque l'on tri, certaines lignes ne le sont pas car elles restent à leur place.
De plus cela fait faire plein de manipulation car il y a 4 feuilles.
Cordialement
Pour 78chris
Bonjour,
Je ne connais pas le principe de power query donc je ne comprends pas tout. Il reste le problème que dans la feuille 1 vous avez déplacez des données qui servait pour une autre feuille et je ne peux pas trier par date.
Cordialement
Bonjour
Une solution avec vba sans tri pour le moment, avec une feuille Tables qui récapitule tes listes.
Pour ajouter une ligne au journal, inserérer une ligne en ligne 6 et remplir ( date récente en haut?)
A regarder si, ok m'indiquer clef de tri
Cordialement
*FINDRH
RE
Les requêtes sont automatiquement triées par date et numéro de pièce à chaque actualisation...
Je ne connais pas le principe de power query donc je ne comprends pas tout. Il reste le problème que dans la feuille 1 vous avez déplacez des données qui servait pour une autre feuille et je ne peux pas trier par date
Dans l'exemple les données déplacées n'étaient pas utilisées (j'ai pris soin de vérifier ! Il y a pas mal d'éléments en erreur dans ce classeur exemple donc difficile d'avoir une vision claire)
PowerQuery existant depuis plus de13 ans et les tableaux structurés depuis plus de 20 ans, il me semble qu'il serait temps de ne plus travailler comme il y a 20 ans...
D'autant que tu précises
Je suis nul en VBA
A noter que les formules en colonne J du tableau TbJour auxquelles je n'ai pas touché, deviennent fausses dès qu'on ajoute une ligne.
Dans ma version il faudrait également corriger la formule afin d'avoir la même formule sur toute la colonne (obligatoire pour le bon fonctionnement des tableaux structurés) :
=SOMME(J2;[@CREDIT];-[@DEBIT])@FINDRH : tu as utilisé des tableaux structurés mais codé comme s'il n'existaient pas, avec plein de select partout, dommage...
Bonjour
La mise en forme "tableau", même incomplète, facilite ce type de traitement
Pour le journal, les formules sont faussées si on insere une ligne en haut du tableau, si on rajoute la ligne en dernier ou insère n'importe ou la formule est bonne.
C'est aussi pour cela que le solde d'entrée et les autres totaux des colonnes sont extérieurs au tableau
Je ne comprends pas pas le problème des select, cela ralentit le traitement, peut être, mais les étapes de la macro sont mieux documentées... surtout lorsque l'on jongle avec les onglets
Ci joint la version avec deux ajouts et un tri date décroissant
Cordialement
FINDRH
RE
Pour le journal, les formules sont faussées si on insère une ligne en haut du tableau, si on rajoute la ligne en dernier ou insère n'importe ou la formule est bonne.
Non cela fout le bazar sur plusieurs lignes
La mise en forme "tableau", même incomplète, facilite ce type de traitement
Je ne comprends pas pas le problème des select, cela ralentit le traitement, peut être, mais les étapes de la macro sont mieux documentées... surtout lorsque l'on jongle avec les onglets
Il y a les bonnes pratiques et celles-ci sont à respecter tant pour les select que pour les tableaux structurés : il doivent être évoqués indépendamment des noms de feuilles, des adresses Excel...
Bonjour à tous,
Merci pour vos réponses, je regarde demain car je suis dans le train puis avion pour réunion.
Merci dans tous les cas.
Philippe
Bonjour Chris
Dans mon dernier envoi l'insertion d'une ligne en milieu de journal comme l'ajout en bas de tableau ne perturbe pas les formules de calcul et le solde colle, quelle que soit le sens de tri (Date ascending ou descending).
Le seul vrai tableau est journal et j'en utilise les fonctionnalités.
Les autres onglets sont des extraits "morts" avec une formule unique pour le calcul du solde...
Je ne suis pas un programmeur professionnel , désolé ..., tous les chemins mènent à Rome....
Cordialement
FINDRH
RE
Dans mon dernier envoi l'insertion d'une ligne en milieu de journal comme l'ajout en bas de tableau ne perturbe pas les formules de calcul et le solde colle, quelle que soit le sens de tri (Date ascending ou descending).
Déjà à l'ouverture la formule des 2 dernières lignes ne sont pas cohérentes
Si on ajoute une ligne, celle de la ligne qui terminait le tableau change et devient fausse
L'utilisation de formules de ce type nécessite des précautions dans les tableaux structurés. C'est un problème connu.
Il faut utiliser soit DECALER soit à la rigueur
=$J$2+SOMME($I$6:[@CREDIT])-SOMME($H$6:[@DEBIT])pour éviter les erreurs.
Je ne suis pas un programmeur professionnel , désolé ..., tous les chemins mènent à Rome....
Peu le sont ici : il n'est pas nécessaire d'être pro pour apprendre à coder correctement.
Ce qui me gêne est de fournir à autrui pour un usage professionnel, donc à risque, un code ne respectant pas les règles.
Il vaut mieux se faire la main sur des demandes concernant des trucs persos sans trop d'incidences : foot, lotos, gestion de biblio ou disco...
Regarde les liens que je t'ai donnés : ces didacticiels permettent de mieux comprendre et s'améliorer...
Merci pour ton retour.
On apprend de ses erreurs et des échanges du forum, ta formule fonctionne parfaitement
Rassure toi je n'ai aucune prétention de développeur des produits même si Excel m'accompagne depuis 1987 et que je découvre toujours des outils et fonctions qui m'auraient bien arrangé dans ma vie professionnelle d'alors.... j'en apprends encore toujours et les recherches de solutions entretiennent mes vieux neurones !
Le codage parfait c'est le nirvana des développeurs, la méthode du béotien qui dépanne permet à un autre béotien de comprendre les instructions et leur enchainement...... si on anoutit au bon résultat !!
encore merci pour tes conseils
Bien cordialement
FINDRH
Bonsoir à tous
Si l'on prend en compte les colonnes jusqu'à la colonne "Credit", seules les feuilles "Journal", "Compte bancaire" et "Livret" comportent 9 colonnes, les feuilles "Badnet" et "Caisse" n'en contiennent que 8.
Pour me faciliter la tâche, j'ai donc harmonisé tout ça en rajoutant la colonne "Pièce n°" dans ces 2 feuilles.
J'ai considéré que les feuilles "Badnet", "Caisse", "Livret" et "Compte bancaire" sont initialement créées et n'ai pas traité le calcul de la colonne "Solde"
Option Explicit
Sub ventile()
Dim a, i As Long, lastRow As Long, lastRow1 As Long, dico As Object
Application.ScreenUpdating = False
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets("Journal")
.AutoFilterMode = False
lastRow = .Range("a" & Rows.Count).End(xlUp).Row
a = .Range("A2:I" & lastRow)
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 1)) Then
dico(a(i, 1)) = Empty
With .Range("A2:I" & lastRow)
.AutoFilter 1, a(i, 1)
Select Case a(i, 1)
Case "BA": a(i, 1) = "Badnet"
Case "CA": a(i, 1) = "Compte bancaire"
Case "LI": a(i, 1) = "Livret"
Case "ES": a(i, 1) = "Caisse"
End Select
With Sheets(a(i, 1))
lastRow1 = .Range("d" & Rows.Count).End(xlUp).Row
If lastRow1 > 2 Then
With .Range("A2:I" & lastRow1)
.Offset(1).ClearContents
End With
End If
End With
.Offset(1).Copy Sheets(a(i, 1)).Cells(3, 1)
.AutoFilter
End With
End If
Next
End With
Set dico = Nothing
Application.ScreenUpdating = True
End SubPour le tri, c'est plus simple de le faire en amont dans la feuille "Journal", non
En fait, j'ai traduit en VBA ce que JExceL2fr décrit manuellement.
klin89
Bonjour Klein89,
Alors au premier abord cela fonctionne trés bien en faisant le tri avant mais si on ne commet pas d'erreur.
Par contre j'ai un petit souci. Si je m'appercois d'une erreur aprés ventilation, par exemple j'ai mis BA et en fait c'était CA. Si je re ventile les nouvelles lignes sont bien en CA mais celles en BA n'ont pas étaient retirées. Par contre si on crée une nouvelle ligne avec BA la copie se fait et cela les supprime. Il faudrait que les lignes en CA, BA, LI et ES soit soit supprimées systématiquement avant ventilation, sans toucher au solde.
Merci beaucoup à tous pour votre travail.
Philippe
Bonjour à tous
Dire que depuis Mardi à 15:44 tu as une solution parfaitement opérationnelle qui ne conserve pas d'anciennes données au mauvais endroit mais tu l'as dédaignée arguant que tu ne connais pas PowerQuery (mais pas plus VBA semble-t-il), comme tu as également dédaigné ma réponse de Mardi à 19:28
J’éviterai donc soigneusement tes posts à l'avenir... la politesse et la curiosité étant pour moi des qualités importantes.
Bonne continuation
Bonjour 78Chris,
Alors non je n'ai pas dédaigné ta réponse et je t'ai répondu, mais j'aime comprendre ce qu'il se passe et avec ta technique je n'ai pas de visibilité. La version de Klein89 a été tout de suite efficace et je comprends a peu prés la macro, c'est pour cela que j'ai jeté mon dévolu sur celle-la.
Mais je n'ai pas abandonné la tienne, je fais des tests de modification de date, de sommes et d'annulation afin d'etre sur que cela fonctionne.
Cordialement
Re galfy,
Le code réajusté en tenant compte des formules dans la colonne "solde" mais bon, à ce rythme on peut vite monter une usine à gaz
Option Explicit
Sub ventile()
Dim a, e, i As Long, feuille, lastRow As Long, lastRow1 As Long, dico As Object
Application.ScreenUpdating = False
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
feuille = Array("Compte bancaire", "Livret", "Badnet", "Caisse")
For Each e In feuille
With Sheets(e)
lastRow1 = .Range("J" & .Rows.Count).End(xlUp).Row
If lastRow1 > 2 Then
With .Range("A2:J" & lastRow1)
.Offset(1).Resize(.Rows.Count - 1).ClearContents
End With
End If
End With
Next
With Sheets("Journal")
.AutoFilterMode = False
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
a = .Range("A2:I" & lastRow)
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 1)) Then
dico(a(i, 1)) = Empty
With .Range("A2:I" & lastRow)
.AutoFilter 1, a(i, 1)
Select Case a(i, 1)
Case "BA": a(i, 1) = "Badnet"
Case "CA": a(i, 1) = "Compte bancaire"
Case "LI": a(i, 1) = "Livret"
Case "ES": a(i, 1) = "Caisse"
End Select
.Offset(1).Copy Sheets(a(i, 1)).[a3]
With Sheets(a(i, 1))
lastRow1 = .Range("D" & Rows.Count).End(xlUp).Row
With .Range("A2:J" & lastRow1)
.Sort key1:=.Cells(3), order1:=xlAscending, Header:=xlYes
.Rows(2).Cells(.Rows(2).Cells.Count).Formula = "=i3-h3"
If .Rows.Count > 2 Then
.Offset(2).Resize(.Rows.Count - 2).Columns("j").Formula = "=j3+i4-h4"
End If
End With
End With
.AutoFilter
End With
End If
Next
End With
Set dico = Nothing
Application.ScreenUpdating = True
End Subklin89
Bonjour,
Pour Klin89,
Toujours le mêm souçi, une fois la ventilation faite, les lignes ne sont pas effacées dans les 4 onglets si je fais une modification.
Cordialement
Re galfy,
Relancez la macro une fois la modification faite dans la feuille "Journal" et vos données seront mises à jour dans les autres feuilles.
Klin89