Microsoft 365 TCD

[Cath32]

Bonjour à tous,
J'ai un gros TCD avec mes ventes par clients et par agent.
J'ai affiché une page par agent (via les pages de rapport). J'ai besoin de pouvoir faire un classeur par agent, sans qu'ils aient accès au reste des données, sachant que idéalement j'aimerais pouvoir afficher les chiffres
- par client
- par produit
et que j'aimerais ne pas passer une journée entière à faire tout cela... (et c'est mal barré pour le moment )
Merci par avance à tous !!
Catherine

 

[Zon]

Bonjour Cath32 et bienvenue sur XLD,

Aurais tu un fichier exemple ? ton niveau en VBA ??

A+++

 

[Cath32]

voici ! le but étant de pouvoir sortir un TCD par agent, que je leur envoie sans que les autres lignes, clients & agents n'apparaissent...
Niveau VBA, je suis pas trop mal je pense, pas facile de te dire. j'apprends à testant...
Merci !

 

[Zon]

Re,

Afin que je comprenne bien ce que tu aimerais.

Pour la création des fichiers :
Pour l'agent 1
Tu veux juste les résultats du TCD de l'agent 1(Résultats par produit suivant année) avec la feuille Base uniquement avec les infos de l'agent 1. Ton TCD donne aussi par code postal , pourquoi pas tout regrouper ?

Et/ou tu veux :dans le fichier créé tu veux le TCD Actif , avec la base adéquate.=> ça j'aime pas trop créer des TCD à la volée en VBA, c'est pas toujours très stable.

Autres questions:
Dans ton vrai fichier, combien d'Agents ? Combien de produits ? de lignes pour Base ? Quelle version d'XL ? PC ou Mac ??

As tu pensé à mettre ton tableau dans la feuille Base en tableau structuré ??

A+++

 

[Cath32]

alors...
agent 1 : résultat par client, et produit PUIS par produit avec les clients en 2nd filtre. le code postal sert ou pas selon les filtres, çà me permet de voir des tendances perf de produits
l'idéal bien sûr est d'avoir un TCD actif... ce que je fais si j'extraie au coup par coup par agent, mais oui, l'idéal serait d'avoir un TCD complet, de filtrer par agent et de pouvoir envoyer cela sans que les autres données soient visibles et accessibles...
Je suis sur Xcel 365, PC, 20 produits, 30 agents, ma base complète fait 16 col (mais je ne me sers pas de tout) et... 23 000 lignes.
Mon fichier est en tableau structuré, oui
merci !

 

[chris]

Bonjour

Cela fait plus de 20 ans qu'on ne fait pas de TCD sur des colonnes entières mais, comme suggéré par zon, sur des tableaux structurés.

Ce que j'ai fait : mise sous forme de tableau et modification de la source du TCD , pour prendre ce tableau.
Placement du champ agent en zone de filtre du TCD.
(on pourrait eventuellement le déplacer dans le code puis le replacer en fin de code...)

Chemin à adapter et contrôle des éventuels problèmes de nommage des classeurs

Sub Ventiler()
  
    Chemin = "T:\TEMP\"
    Application.ScreenUpdating = False
  
    ActiveSheet.PivotTables("Tableau croisé dynamique48").ShowPages PageField:="agent"
      
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> "base" And Ws.Name <> "Feuil2" Then
            Ws.Move
            Set WK2 = ActiveWorkbook
            Set WS2 = ActiveSheet
            With WS2.PivotTables(1)
                WS2.Range(Mid(WS2.PivotTables(1).DataBodyRange.Address, InStrRev(WS2.PivotTables(1).DataBodyRange.Address, ":") + 1, 8)).ShowDetail = True
                   .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Tableau1")
            End With
            WK2.SaveAs Filename:=Chemin & WS2.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            WK2.Close
        End If
    Next Ws
End Sub

 

[Zon]

Salut ,

Chris , je commençais à préparer un truc sans toucher au TCD à partir de Base en tableau structuré.

Chris, j'ai adapté ton code au ficher de Cath, en pensant rajouter une boucle sur les agents pour créer n fichiers agents.
Mais :
je n'arrive à sortir de cette erreur 1004 pour showpages => Une idée j'ai essayé du WIth , du Set ..
si je boucle sur les pivots items pour savoir si il sont visibles cela fonctionne ...

donc je l'ai enlevé. du coup j'ai un TCD vide.

=> ça me crée bien un TCD et un tableau issu du TCD même si je perds Base ou feuil2 sur le classeur d'origine, ça j'ai plus qu'à l'adapter


Merci pour ton retour.

A+++

 

[chris]

RE
J'ai testé sur le fichier posté au #3 et cela fonctionne parfaitement.
Le code s'adapte à toute forme de TCD et à n'importe quel nombre d'agents...

C'est simple : je n'ai touché à rien d'autre que ce que j'ai énoncé et notamment déplacé le champ Agent dans la zone de filtre du TCD, ce qui permet le showpages réservé à ces champs... (c'est une fonctionnalité que l'on utilise manuellement souvent...)

Il faut utiliser les automatismes d'Excel plus rapides que n'importe quel code VBA

 

[Zon]

Re,

En effet cela fonctionne, super le coup du move, j'avais oublié qu'un TCD


On attend le retour de Cath32.


Mais pour moi , j’ai 2 questions à te poser :

La méthode ChangePivotCache rend autonome le fichier Agent1 grace à tableau1 certes, mais nous n’avons plus que 2023 (la dernière année en fait).


Si je veux toutes les années dans Tableau1 ??

Est ce que tu aurais plus simple que ceci :

Bon en bouclant sur la derniere ligne TCD avec la méthode showdetail , je vais créer les feuilles avec les années manquantes. Puis ajouter chaque plage de données de ces feuilles sur Tableau1.



Question subsidiaire

Comment récupérer les lignes filtrées d’un tableau structuré ?

J’ai essayé tout un tas de truc :

'     Const PlBase$ = "TableauBase" '
    
 '   T = Range(PlBase).ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Value 'me donne que les 4 ou 5 premieres lignes
 '  With Sheets("Base").ListObjects(PlBase)
  '    T = .ListColumns(4).DataBodyRange.SpecialCells(xlCellTypeVisible).Value
  ' End With
    
  ' T = Range(PlBase).ListObject.Range.SpecialCells(xlCellTypeVisible).Value

En te remerciant pour ton retour.
A+++

 

[Zon]

Il faut utiliser les automatismes d'Excel plus rapides que n'importe quel code VBA

Si on peut oui , mais je me souviens d'un grand débat jadis sur le tri des données par excel : en tableau VBA c'était plus rapide que la méthode sort...
Y a 20 ans les pc n'étaient pas aussi rapide , le TCD sont gourmands en ressources.. On faisait aussi bien avec des faux TCD..

 

[chris]

RE

Un correctif qui déplace et replace le champ agent et qui affiche / n'affiche plus le total des années afin de bien exporter toutes les données et non une seule année (je n'avais pas vu qu'il était absent)

On peut éventuellement masquer le total dans les TCD individuels aussi...

Sub Ventiler()
    
    Chemin = "T:\TEMP\"
    Application.ScreenUpdating = False
    
    With Worksheets("Feuil2").PivotTables(1)
        .RowGrand = True
        .PivotFields("agent").Orientation = xlPageField
        .PivotFields("agent").Position = 1
        .ShowPages PageField:="agent"
    End With
    
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> "base" And Ws.Name <> "Feuil2" Then
            Ws.Move
            Set WK2 = ActiveWorkbook
            Set WS2 = ActiveSheet
            With WS2.PivotTables(1)
                WS2.Range(Mid(WS2.PivotTables(1).DataBodyRange.Address, InStrRev(WS2.PivotTables(1).DataBodyRange.Address, ":") + 1, 8)).ShowDetail = True
                   .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Tableau1")
            End With
            WK2.SaveAs Filename:=Chemin & WS2.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            WK2.Close
        End If
    Next Ws
    
    With Worksheets("Feuil2").PivotTables(1)
        .RowGrand = False
        .PivotFields("agent").Orientation = xlRowField
        .PivotFields("agent").Position = 2
    End With
End Sub

 

[Zon]

Re,

Magnifique Chris... tu as répondu à ma 1ere question ...


y a juste la gestion d'erreur du saveas à gérer. éventuellement.

Et pour la 2ème.....

 

[Dranreb]

Si on peut oui , mais je me souviens d'un grand débat jadis sur le tri des données par excel : en tableau VBA c'était plus rapide que la méthode sort...
Y a 20 ans les pc n'étaient pas aussi rapide , le TCD sont gourmands en ressources.. On faisait aussi bien avec des faux TCD..

Même aujourd'hui, je n'utilise jamais ni TCD ni PQ, tant ma fonction Gigogne classe et regroupe rapidement des données en collections imbriquées.