TCD a partir d'une liste deroulante

Bonjour,

Est il possible de faire un TCD, à partir d'une liste déroulante?

Pour être plus précis, la liste déroulante comprend le nom de mes feuilles.

Chaque feuilles à le même nombre de colonnes, mais un nombre de lignes différent.

Merci d'avance pour vos réponses.

Tbo

Bonsoir,

Regarde le fichier joint.

Le TCD est basé sur une liste dynamique (regarde les noms définis dans l'onglet "tcd", en colonnes G et H)

J'ai rajouté une macro, afin de mettre à jour le TCD dès que tu modifies ta sélection dans la cellule A1 de l'onglet "tcd"

Bonne soirée

Bonsoir,

En complément de la réponse de cousinhub, il vaut mieux modifier la source du TCD plutôt que de créer un TCD à chaque fois.

Pour modifier la source d'un TCD en VBA :

WorkSheets(TCD").PivotTables(1).PivotCache.SourceData = "MaFeuilleSource!MaPlage"

Ou MaPlage est représentée de style de référence L1C1, par exemple : L1C1:L1000C3

Merci messieurs.

Je vais dans un premier temps essayer de reproduire la méthode de Cousinhub (base 1 OK base 2 OK Base...?? )

jetait a la base partis sur une méthode qui ressemble a celle de Benead, avec un tcd, qui en fonction d'une variable correspondant au nom de la feuille met a jours la plage de donnée entre A1 et CK derlign.

Mais je bloc... donc si j'arrive a reproduire la méthode de Cousinhub je prend! ^^

En tout cas merci de vos réponses.

Bonjour,

J'ai donc défini 2 noms reprenant les noms des onglets.

base1, qui correspond à la première colonne de l'onglet base1

idem pour base2

la source de données pour le TCD est la zone dynamique "base"

Celle-ci est calculée en fonction de la valeur de la cellule A1 de l'onglet "tcd"

Dans la formule :

=DECALER(INDIRECT(tcd!$A$1);;;NBVAL(INDIRECT(tcd!$A$1));2)

J'utilise la fonction Decaler, dont la syntaxe est :

DECALER(réf;lignes;colonnes;hauteur;largeur)

La référence est donc la zone de la cellule A1. Comme on ne peut pas définir directement une zone par une cellule, il faut utiliser la fonction INDIRECT afin qu'Excel comprenne qu'on veut la zone, et non la valeur de cette cellule. regarde l'aide à propos de cette fonction.

Et la hauteur de la base est déterminée par la fonction NBVAL, toujours avec le même principe (INDIRECT)

La largeur, par le dernier argument (ici 2 dans mon exemple, car mes tableaux ne comportent que 2 colonnes)

Bon courage, et bon dimanche

Je te remercie pour les explication.

J'ai fini par arrivé à reproduire ton exemple.

Je suis entrain d'essayer de l'adapter à mon cas...

Je bloc actuellement sur le "nommage" (je pense pas que cela soit très français) des plage que je souhaite.

En clair pour chaque feuilles je définis la plage comme étant : "A1:CH" & Derlig et je lui donne le nom de la feuille (certaines feuilles ayant des espaces dans leurs noms).

De plus, dans l'exemple, je dois mettre une macro dans la feuille "tdc", or cette feuille (dans mon cas) est crée à l'issue d'une autre macro.

Est-il possible, d'écrire grâce à une macro, une macro dans une feuille? (pour que la feuille du "tdc" refresh à chaque modifications de la liste déroulante).

pour l'instant j'en suis la :

Sub Macro1()

Dim Derlig As Long

Dim sh As Worksheet

Derlig = Cells(Rows.Count, "A").End(xlUp).Row

For Each sh In Sheets

If sh.Name <> "Base" And sh.Name <> "Sommaire" Then

Set maplage = Range("A1:CH" & Derlig)

maplage.Select

With Selection

Names.Add Name:=sh.Name, RefersTo:="='" & ActiveSheet.Name & "'!" _

& Selection.Address

End With

End If

Next sh

End Sub

mon souci pour le moment est que la macro ne change pas de feuille et reste sur la première.

Re-,

Tout d'abord, si tu as des espaces dans les noms de tes onglets, ça ne peut pas fonctionner, les noms "définis" ne pouvant être composés d'espaces...

A ce que je comprends, tu détermines tes noms définis par VBA?

Dans ce cas, il faudrait agir différemment...

Mais comme je ne connais pas la structure de ton fichier, c'est beaucoup moins facile...

Si tu veux que je continue à t'aider, le mieux serait que tu mettes un fichier exemple, exempt de toutes données confidentielles...

Ensuite, nul besoin d'écrire un autre code via ton code de création d'onglet, il suffit de mettre dans le ThisWorkbook un truc dans le genre :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "tcd" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Address = "$A$1" Then ThisWorkbook.RefreshAll
End If
End Sub

Bon courage

Re-,

Un exemple dans le fichier joint (V2)

Les codes :

Dans le ThisWorkbook :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "tcd" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Address = "$A$1" And Target <> "" Then Modif_Base (Target.Value)
End If
End Sub

et dans un module :

Sub Modif_Base(Ws As String)
Dim Plg As Range
Set Plg = Sheets(Ws).Range("A1:B" & Sheets(Ws).Cells(Rows.Count, "A").End(xlUp).Row)
ActiveSheet.PivotTables("Tableau croisé dynamique2").ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Plg, Version:=xlPivotTableVersion14)
End Sub

Le fichier :

Ci joint l'exemple de ce à quoi je souhaiterais aboutir...

merci pour ton aide toujours aussi précieuse...

Re-,

Dans le fichier joint, j'ai modifié la validation de données de la cellule A1 de l'onglet "Sommaire".

Les noms des onglets sont triés par ordre alphabétique..

Les codes sont dans le "ThisWorkbook", dans le code de l'onglet "Sommaire" et dans le Module1

PS, j'ai supprimé les liens hypertextes des onglets, mais tu sais comment les remettre....Oups...

Bon courage

Merci

je suis entrain d'essayer de l'adapter a ma macro mais pour le moment ça coince un peu...

mais je tiens a chercher...

Bonsoir Tbo et Cousinhub,

Sans vouloir remettre en question votre travail, généralement l'intérêt principal des formules est de se passer de macro, mais dans la mesure ou le VBA devient indispensable, alors il me semble plus judicieux de tout construire en VBA, et je vois que vous avez fait le bon choix. Juste une chose concernant les BD et notamment la façon de récupérer la dernière ligne, personnellement je ne suis pas un fanatique de End(xlUp) qui a le gros inconvénient d'être sensible aux filtres et lignes masquées, si les BD sont correctement gérées, j'opterai pour UsedRange.rows.count et même mieux si toutes les bases de données sont construites de la même manière, alors je prendrais directement la plage active dans sa totalité :

Set Plg = Sheets(Ws).Range("A1:J" & Sheets(Ws).Cells(Rows.Count, "A").End(xlUp).Row)

Remplacé par

Set Plg = Sheets(Ws).UsedRange

On peut filtrer toutes les feuilles, la plage active prendra toujours toutes les lignes et colonnes.

L'autre point concerne exclusivement tbo, je m'interroge sur la conception même de ton classeur : pourquoi faire compliqué quand on peut faire simple, et je ne crois pas que cousinhub me contredira : pourquoi te compliques-tu la vie à créer autant de bases de données identiques pour y mettre "la même chose", sous entendu les mêmes types d'informations ? L'essence même d'un base de données est de pouvoir prendre en compte les différentes typologie de données, il suffit de paramétrer la BD pour gérer ces différences.

Je m'explique : tu as construis dans l'exemple une vingtaine de feuilles représentant des bases de données ou chaque colonne indique le même type de contenu d'une feuille à l'autre, il est donc largement préférable de construire une seule BD (donc une seule feuille) dans laquelle tu ajoutes une ou plusieurs colonnes représentant une catégorie de produit, sous catégorie de produit, typologie. Par exemple une catégorie Informatique avec une sous catégorie pour "Ordinateur", "Disque dur", "Ecran"... Une catégorie Hifi Vidéo avec une sous catégorie "Télévision", "Chaine HIFI", "Home Cinéma"... Dan mon exemple, il faut donc ajouter une colonne pour la catégorie et une colonne pour la sous catégorie.

Il te reste donc une seule base de données que tu peux filtrer comme tu le souhaites, par exemple un filtre sur "Ordinateur" et tu retrouveras à l'identique le contenu de ta feuille ordinateur... Le premier intérêt est que tu n'as besoin que d'un TCD qui s'appuie exclusivement sur une BD (exit les formules et/ou le code VBA), le second intérêt est que tu pourras faire des synthèses avec toutes les catégories sur le même TCD, par exemple tu pourras voir le montant total de remboursement catégorie par catégorie avec le total général... Ce qu'il est impossible de faire avec le fonctionnement actuel.

Bonsoir,

Personnellement, je n'utilise pas UsedRange, car cette fonction prend en compte les cellules vides qui auraient été formatées...(anciennement remplies, puis effacées...)

Ces cellules ne sont pas comptabilisées par la méthode .End(xlup).....mais en cas de filtre, effectivement, c'est pas bon non plus....

UsedRange représente la plage utilisée

Maintenant, on pourrait utiliser CurrentRegion, qui représente la zone en cours, et qui ne tient pas compte d'un filtre éventuel...

Bref, vaste débat...

Bonne nuit

Moi j'utilise quasiment exclusivement cette propriété et elle ne me fait jamais défaut, la seule contrainte que je connaisse, depuis des années que je l'utilise, c'est que la plage doit commencer en A1, si ce n'est pas le cas le nb de lignes et/ou de colonnes ne correspondent pas au nb de lignes ou colonnes réel, par exemple si la plage utilisée commence en A10 et comporte 10 lignes, UsedRange.Rows.count indiquera 10 alors que la dernière ligne sera la ligne 19, il faut donc de la rigueur dans la feuille en commençant toujours en A1 ou en mettant au moins du texte dans cette première cellule. Pour moi, UsedRange n'est que du bonheur...

Bonjour,

Je vais donc donner un peu plus d’informations, pour répondre à tes interrogations Benead.

J’ai besoin de ventiler ma base par « problèmes » car il se trouve que dans certains cas, il m'est nécessaire de recalculer les remboursements. Dans ce cas, il m'est plus simple d’avoir une feuille par problèmes pour y ajouter des colonnes et ainsi grâce a une formule (prenant en compte plusieurs nouvelles colonnes déjà en place-ayants des données différentes en fonction des "problemes"-) recalculer les remboursements.

Je te rejoins dans l’idée qu’une base de données permet d’avoir une vision de la globalité du dossier et grâce à un filtre rapide, on peut voir la base par « problèmes ». Mais comme tu l'as déjà compris, cela devient plus complexe en cas de recalcule de certaines valeurs.

Pour ce qui est du tableau croisé dynamique cela permet de faire ressortir les "problèmes" par année et par individu (permettant ainsi une analyse plus rapide et plus précise).

Je n’arrive toujours pas à adapter le code mis dans la feuille « sommaire », puisque une fois à la suite de ma macro cela me donne bien une liste déroulante mais avec seulement une seule itération ou tous les noms de feuille sont concatènerais.

De plus je ne vois pas comment je vais pouvoir mettre la macro écrit dans « thisworkbook » puisque je ne peux écrire de macro via ma macro.

Dans ce cas je pense qu’il faut que je modifie la vision de mon projet.

Je m’explique, jusqu'à présent, j’ouvrais ma base et j’appliquais une macro qui était dans mon « Personal.XLSB ».

Je pense que je vais plutôt devoir partir sur un fichier excel.xlsm avec le TCD déjà en place (ainsi que la macro dans le « thisworkbook », et le module), dans lequel je vais devoir faire une input box pour aller chercher mon fichier base (format excel97) et un bouton pour y appliquer ma macro principale.

Le TCD n’aura alors plus qu'à prendre ses valeurs dans les onglets.

De plus,Cela aura l'avantage de laisser mon fichier base d’origine intact.

Il n’y aura plus qu’a faire un enregistrement de la copie modifiée au même endroit (avec un nom du style « mêmenom.ventilée »).

Il faut respecter un ordre du coup, importation puis ventilation puis création de la liste et là tout devrait tourner….

Cela me semble plus simple… suis -je dans l’erreur ?

Bonsoir tbo,

Je pense que c'est parce que tu n'a pas suffisamment d'expérience ou de recul dans la gestion des fichiers et des bases de données que cela te semble plus facile de faire une cinquantaine de feuilles avec un calcul de remboursement différent par feuille (ou par groupe de feuilles).

En tout cas, tu arrives inexorablement à un dilemme : quelle est la meilleure solution ? 50 bases de données avec une problématique de reporting d'une part, et de vision macro d'autre part, ou bien 1 seule base de données avec x calculs de remboursements différents en fonction des catégories ? Actuellement ton problème actuel est d'avoir 50 BD avec 50 TCD potentiels que tu veux regrouper en un seul. Et peut-être de futurs problèmes de navigation dans le classeur pour rentrer les données...

Tu es probablement le mieux placé pour savoir quelle est la meilleure solution... Encore que. Pose sur papier les avantages et inconvénients de chacune des deux méthodes de la conception de l'application, en passant par la mise à jour des données jusqu'au niveau de finesse de reporting que tu souhaites. Cela te permettra de prendre un peu de recul par rapport à la solution qui te semblait induscutable.

Personnellement, avec les infos qui sont en ma possession, je pense que, à part la phase de conception (et encore), tu as tout à gagner à ne faire qu'une seule base, quitte à passer un peu plus de temps dans la phase de construction de celle-ci.

Mais tu peux aussi très bien terminer ton application et la remettre en cause plus tard, avec l'expérience tu pourras y intégrer de nouvelles idées ou autres besoins.

De toute façon, si tu as encore besoin d'aide fais-nous signe.