VBA pour remplacer un TCD (complexe ??)

Bonjour,

Je travaille dans une société commerciale pour laquelle un suivi des opérations est primordial.

Jusqu'à présent (c'est à dire avant que je rejoigne la société), chaque opération était traitée dans un fichier Excel sur une seule ligne.

Problème : les informations étant nombreuses, cela devient vite illisible.

Avantage : étant donné que toutes les informations sont sur une seule ligne, on pouvait faire des TCD à des fins de reporting (avec des infos limitées toutefois et peu présentables...)

J'ai donc refait un tableau de suivi plus présentable, mais les infos pour une opération sont réparties sur plusieurs lignes. Donc plus de TCD possibles...

J'ai bien tenté des savants mélanges de somme.si et de recherchev, mais en vain.

Je ne suis pas mauvais en Excel "normal" (formules etc) mais je connais très peu la VBA.

Je comprends que la VBA pourrait résoudre mes soucis.

Mon Objectif :

en fonction du tableau ci-joint (Feuille "Suivi" que j'alimente au jour le jour), je souhaiterais pouvoir synthétiser les informations par client et par fournisseur.

Dans la feuille "Reporting" un exemple de ce que je souhaiterais.

L'idée est de faire une marcro dans laquelle je donnerai le nom d'un fournisseur ou d'un Client et que le tableau se produise en automatique.

Est ce envisageable ou impossible meme avec une VBA ?

Merci

Seb

Bonjour,

Je crains que tu ne fasse fausse route.

Ton tableau est sans doute plus joli mais ne correspond pas aux besoins d'une base de données.

Ce qu'il faut pour que ce soit exploitable :

• une ligne de titres
• que des données en-dessous sans ligne vide

Bref, ce que tu avais avant...

eric

Tout d abord merci Éric !

Quelle déception... Moi qui pensait qu avec la VBA tout était possible !!

Dans ce cas, est il possible de faire des filtres "spéciaux" qui isoleraient par exemple 5 lignes en dessous de mon critère de sélection ?

Par exemple, dans ma feuille "suivi", en choisissant "fournisseur 1" le filtre me laisserait également les lignes en dessous jusqu au champ "Mv" ?

Encore merci

Seb

Ce n'est pas impossible, mais plus difficilement gérable.

Il vaut mieux savoir à quoi tu t'exposes.

Si tu débutes, tu vas peut-être avoir du mal à jongler avec les offsets tout de suite.

Et surtout la complexité rend toute évolution périlleuse. C'est un par un qu'il faut repérer tes champs. C'est rébarbatif. Il faut garder en tête que demain tu auras peut-être un paramètre supplémentaire à ajouter, voire même une ligne entière. Il faut anticiper et le prévoir dès la création...

Si tout le monde doit s'arrêter de travailler 1 mois le temps que tu t'en sortes car chaque modif te donne un bug tu risques d'avoir des suées.

Et perso je ne me vois pas m'engager sur un projet de plusieurs semaines.

Tu peux peut-être couper la poire en deux. Garder une base exploitable et te créer un formulaire (ou une feuille) de saisie (plus consultation et modification si besoin).

Ca règle ton problème de lisibilité tout en gardant une gestion facile en ayant une base de donnée 'propre'.

Dans ce cas, est il possible de faire des filtres "spéciaux" qui isoleraient par exemple 5 lignes en dessous de mon critère de sélection ?

Oui, mais déjà tu as 2 fois 'fournisseur 1'...

Prend le temps de la réflexion avant, de laisser mûrir. C'est 80% de la réalisation.

eric

Re Eric,

ok je vois...

mais justement l'idée était de pouvoir filtrer plusieurs fois le même fournisseur (avec lequel plusieurs opérations sont en cours) avec 5 lignes en dessous à chaque fois.

Et je suis d'accord avec toi concernant l'évolution, j'y avais bien pensé. Si je suis absent, ca devient compliqué pour mes collègues.

Bref, la nuit portant conseil : l'inverse de ma première question est il possible ?? J'appelle ça un "back to the future"

A savoir :

1/ je continuerai d'alimenter mes données sur une seule ligne. En même temps c'est le principe de la saisie.... chi*** et inévitable

2/ par contre, mon onglet reporting permettrait une consultation permanente (mise à jour en auto ?) et moins pénible que la lecture d'une seule ligne sans fin. Car c'est aussi ca l'idée principale : être en mesure rapidement et facilement de donner l'exposition et la situation sur un acheteur ou un fournisseur.

Merci

Seb

Bonjour,

As-tu pensé à l'utilisation des tableaux, des TCDs et des segments puisque tu travailles avec Excel 2013 (les segments sont apparus avec Excel 2010)?

Bonjour Jean-Eric,

Absolument pas ! Je ne connais pas cette fonction

Je vais jeter un œil.

Le fonctionnement est-il simple :

• au niveau de la mise en place ?
• de l'évolution ?

Merci !

Seb

Re,

Pour les segments (aperçu des fonctionnalités) :

A te relire si ces informations te paraissent intéressantes.

Bonjour,

1/ je continuerai d'alimenter mes données sur une seule ligne

Pas forcément.

Tu peux garder ton idée de grille de saisie avec un bouton Valider qui t'ajoute une ligne dans ton tableau de données (qu'il faut absolument conserver).

Pour la consultation tu peux enrichir ton TCD avec les segments indiqués par jean-éric, ce qui te suffira peut-être. C'est vrai que c'est un confort supplémentaire intéressant.

Ou garder ton idée de feuille Reporting. Seulement en ayant toujours comme source ta BdD.

D'ailleurs on ne connait toujours pas les champs de ta BdD ni quels sont tes besoins au niveau du reporting.

2 blocs de données de cumul par fournisseurs en fonction du Pymt Terms ? En limitant à des périodes ? Autre chose ?

Pour l'instant il n'y a que toi qui connait les données sources et les besoins, difficile de te donner d'autres idées ou conseils.

eric

Pas forcément.

Tu peux garder ton idée de grille de saisie avec un bouton Valider qui t'ajoute une ligne dans ton tableau de données (qu'il faut absolument conserver).

Eric,

si je comprends bien, ce qui serait envisageable de faire est :

1/ la saisie dans mon "magnifique" tableau

2/ une macro qui mettra les infos en ligne pour permettre la création d'un TCD (au moyen d'un bouton)

3/ faire mon TCD à des fins de reporting avec les éléments dont j'ai besoin

Pour répondre a ta question sur les besoins, je vais me limiter d'abord au principal dans un premier temps : quelles sont mes échéances de paiements a venir sur mes fournisseurs (Champs : Supplier, Date, banque et "to be paid" qui correspond au montant)

Et la même chose à l'inverse, à savoir les échéances de fonds a recevoir sur mes clients (meme champs donc)

Je pense que par la suite, je pourrais ajuster le TCD au fur et a mesure des demandes de mes responsables, qui je l'avoue, ne sont pas encore bien précises

Seb

Merci Jean-Eric pour les liens et l'info sur les segments (très utile !)

Seb

si je comprends bien, ce qui serait envisageable de faire est :

1/ la saisie dans mon "magnifique" tableau

Exactement, dans la mesure où ton magnifique tableau représente tous les champs que tu dois ajouter.

Une petite macro de validation t'ajoutera une ligne dans ta BdD.

je vais me limiter d'abord au principal dans un premier temps : quelles sont mes échéances de paiements a venir sur mes fournisseurs (Champs : Supplier, Date, banque et "to be paid" qui correspond au montant)

Et la même chose à l'inverse, à savoir les échéances de fonds a recevoir sur mes clients (meme champs donc)

Les segments devraient faire ton bonheur.

En fait ce sont des filtres beaucoup plus accessibles que les listes déroulantes des TCD. Tu les as toujours sous les yeux et tu vois la sélection en cours.

eric

Merci Eric !

Tous ces échanges précieux pour me permettre de reformuler ma toute première question !!! et cela en fonction des mes nouveaux besoins :

quels code VBA seront nécessaires pour mettre les données de mon (toujours) magnifique tableau en ligne ? Ce qui me permettra par la suite de faire mon TCD et d'y inclure des segments.

Tks !!

Seb

Bonsoir,

Peux-tu joindre un exemple de ton fichier original et le's) type(s) de synthèse(s) que tu souhaites?

A te relire

Bonsoir Jean-Eric,

Ci-joint un extrait de ma position.

J'ai environ 200 transactions à suivre, soit 1'000 lignes (onglet "suivi"), et les fournisseurs et clients reviennent plusieurs fois sur une même période dans différentes transactions.

L'objectif du TCD serait donc d'avoir une vision consolidée sur un acheteur / fournisseur.

Dans l'onglet "Tableau pour TCD", il s'agit du tableau qui serait constitué au moyen de la VBA.

L'objectif de ce tableau serait de n'avoir qu'une ligne par transaction afin de pouvoir faire mon TCD.

Dans l'onglet TCD, un exemple du reporting que j'aimerai obtenir dans un premier temps. Je pourrais ajuster par la suite

Est ce ok comme ça ?

Merci encore de votre aide !

Seb

Une proposition de code pour enregistrer tes saisies.

J'ai fait en sorte que tu puisses construire et modifier ta feuille sans à avoir à modifier le code.

Explications sur la feuille, ça te fait une base de départ.

Alt+F11 pour aller dans VBE.

N'hésite pas à te servir de F1 sur les fonctions, propriétés et méthodes pour découvrir VBA.

eric

Bonjour,

Ci-joint création TCD en VBA mais avec 2 lignes, c'est un peu limite pour la mise en forme.

A te relire si tu es intéressé.

Cdlt.

Option Explicit
Option Private Module
Public Sub Creer_TCD()
Dim WSS As Worksheet, _
    WSD As Worksheet, _
    PRange As Range, _
    PTCache As PivotCache, _
    PT As PivotTable

    Application.ScreenUpdating = False

    Set WSS = Worksheets("Tableau pour TCD")
    Set WSD = Worksheets("TCD")

    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT

    Set PRange = WSS.Range("A2").CurrentRegion
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, 1), TableName:="TCD1")

    PT.AddFields RowFields:=Array("Fournisseur", "BANK")

    With PT.PivotFields("Fournisseur")
        .LayoutSubtotalLocation = xlAtTop
        .LayoutForm = xlOutline
        .LayoutCompactRow = True
    End With

    With PT.PivotFields("BANK")
        .LayoutSubtotalLocation = xlAtTop
        .LayoutForm = xlOutline
        .LayoutCompactRow = True
    End With

    With PT.PivotFields("to be paid")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0"
        .Name = "Sum to be paid"
    End With

    With PT.PivotFields("DATE")
        .Orientation = xlDataField
        .Function = xlMax
        .Position = 2
        .NumberFormat = "dd/mm/yyyy"
        .Name = "max Date"
    End With

    With PT.DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With

    PT.TableStyle2 = "PivotStyleLight16"

    With PT
        .ColumnGrand = False
        .RowGrand = False
    End With

    PT.PivotFields("Fournisseur").Subtotals(1) = True
    PT.PivotFields("Fournisseur").Subtotals(1) = False

    WSD.Activate
    Range("A2").Select

    Set WSS = Nothing: Set WSD = Nothing: Set PRange = Nothing
    Set PTCache = Nothing: Set PT = Nothing

End Sub

Merci messieurs pour ces propositions !

Je regarde tout ça, essaye de comprendre les codes pour pouvoir me débrouiller tout seul et vous reviens si j ai des doutes

Sebastien

Bonjour Eric,

Bon, évidemment je suis perdu...

Je suis quand même arrivé à changer les champs des plages pour intégrer d'autres données.

Par contre, j'ai bien essayé de comprendre les codes mais pas facile !

Par exemple, le code Dim de la 1ère ligne : je comprends que tu donnes ici une "valeur/fonction" aux feuilles. Mais quel est l'objectif ?

Autre problème, j'ai essayé de rajouter des transactions dans la feuille saisie mais lorsque le bouton "valider" est actionné seul la première transaction est reprise dans la feuille BdD.

Je comprends donc que le tableau de la feuille saisie est un "tremplin" pour remplir les données de la feuille "BdD".

Merci,

Séb

Bonjour,

Bon, que je me rappelle ce que j'avais fait. Ca date un peu...

Dans un 1er temps tu n'as pas besoin d'aller dans le code.

Essaie déjà le fonctionnement prévu pour comprendre et voir si ça te va. Quand le principe te sera acquis, il sera temps de te pencher sur le code et de le faire évoluer si besoin.

Je vais essayer d'expliquer différemment.

Par exemple tu as un nouveau champ "Email vendeur" qui vient d'apparaitre, et que tu veux obligatoire à la saisie.

Dans 'Saisie' tu décides de mettre sa saisie en G10. Juste au-dessus, en G9 tu dois mettre son nom : Email vendeur.

Tu fais tes encadrements, tes couleurs etc.

Tu dois ajouter également son nom en ligne de titre dans BdB. Par exemple tu insères une colonne B et tu saisis Email vendeur en B1.

Maintenant tu dois juste ajouter sa référence dans le ruban 'Formules / Gestionnaire de noms' :

• dans 'ChampSaisie', Fait référence à : tu ajoutes à la fin ;$G$10 (ou tu vas cliquer sur la cellule), tu valides avec le V à gauche.
• Idem dans 'ChampsObligatoire' comme c'est un champ que tu as décidé saisie obligatoire
• et si tu veux qu'il soit vidé après la validation tu l'ajoutes également à 'Champs RAZ'.

Dans le classeur exemple je n'ai mis que quelques champs dans les noms. A toi d'ajouter tous ceux qui t'intéressent, de supprimer ceux qui sont en double et de faire ta mise en page.

le code Dim de la 1ère ligne :

C'est la déclaration et le typage des variables. Pas obligatoire mais fortement recommandé.

eric