VBA: Détails des Tableaux croisés dynamiques dans le Classeur

Vous travaillez souvent avec les 'Tableaux croisés dynamiques' (TCD) en Excel et vous voulez vous simplifier la vie? C'est bien sûr possible! Avec le code VBA qui suit, vous pourrez obtenir les détails de vos tables pivot en un clic. Plus besoin de chercher chaque information manuellement. Et si, en plus, vous utilisez plusieurs tableaux dans un seul Classeur, vous êtes tombés sur la bonne page…

Utiliser VBA pour gérer les Tableaux croisés dynamiques

Même si les Tableaux croisés dynamiques peuvent paraître comme une fonctionnalité "à part" dans Excel, il est tout de même possible d'utiliser VBA pour les gérer: on peut les créer, effacer, arranger, changer les sources de données etc. Cela reste un peu complexe au niveau de la syntaxe du code VBA mais cela en vaut certainement la peine de s'y intéresser de plus près.

Remarque pratique: pour vous faciliter la vie, n'hésitez pas à utiliser l'enregistreur des macros! Lancez-le, modifiez votre TCD comme vous le voulez et ensuite étudiez/analysez/modifiez le code VBA enregistré. Cela vous fera gagner beaucoup de temps.

Le code VBA suivant vous permettra de générer une liste des tableaux croisés dynamiques ("pivot tables" en anglais) présents dans le Classeur (Workbook) avec les informations utiles sur ces tableaux (cela fonctionne même si le Classeur ne contient qu'un seul tableau croisé dynamique):

le nom du Tableau croisé dynamique
la feuille sur laquelle se trouve le tableau croisé dynamique
la plage (Range) sur laquelle le tableau se trouve – très utile si vous voulez travailler avec les résultats de la table pivot (copier les données, formatage,…)
la plage (Range) utilisée comme source des données
le nom de l'utilisateur qui a rafraîchi le tableau la dernière fois
la date et l'heure du dernier rafraîchissement

Cette procédure crée une nouvelle Feuille (Sheet) dans le Classeur où elle se trouve avec les informations cités plus haut.

Et voici le résultat et pratique:

VBA: lister les Tableaux croisés dynamiques dans un Classeur Excel

Procédure VBA pour obtenir les détails des Tableaux croisés dynamiques

Et voici donc le code de la procédure en question. Il vous suffit de l'utiliser tel quel (copier/coller dans un Module de votre Projet).

Sub ListeTableauxCroisesDynamiques()
'par Excel-Malin.com ( https://excel-malin.com )

'déclaration des variables
Dim tcd As PivotTable
Dim CompteurFeuilles As Long
Dim CompteurLignes As Integer
Dim objNewSheet As Worksheet

Application.ScreenUpdating = False

'ajout d'une nouvelle feuille
Set objNewSheet = Worksheets.Add
objNewSheet.Activate

CompteurFeuilles = 2
CompteurLignes = 2

'prépare les titres
Range("A1").Value = "Nom du tableau"
Range("B1").Value = "Source des données"
Range("C1").Value = "Feuille"
Range("D1").Value = "Plage du tableau"
Range("E1").Value = "Rafraîchi par"
Range("F1").Value = "Rafraîchi le"

'extraît l'information sur les Tableau croisés dynamiques
Do While CompteurFeuilles <= Worksheets.Count
    Sheets(CompteurFeuilles).Select
    For Each tcd In ActiveSheet.PivotTables
         objNewSheet.Cells(CompteurLignes, 1).Value = tcd.Name
         objNewSheet.Cells(CompteurLignes, 2).Value = tcd.SourceData
         objNewSheet.Cells(CompteurLignes, 3).Value = ActiveSheet.Name
         objNewSheet.Cells(CompteurLignes, 4).Value = tcd.TableRange1.Address
         objNewSheet.Cells(CompteurLignes, 5).Value = tcd.RefreshName
         objNewSheet.Cells(CompteurLignes, 6).Value = tcd.RefreshDate
        
         CompteurLignes = CompteurLignes + 1
    Next
    CompteurFeuilles = CompteurFeuilles + 1
Loop

objNewSheet.Activate

Columns("A:F").EntireColumn.AutoFit 'adapte la largeur des colonnes
Application.ScreenUpdating = True
End Sub

Sub ListeTableauxCroisesDynamiques()

'par Excel-Malin.com ( https://excel-malin.com )

'déclaration des variables

Dim tcd As PivotTable

Dim CompteurFeuilles As Long

Dim CompteurLignes As Integer

Dim objNewSheet As Worksheet

Application.ScreenUpdating = False

'ajout d'une nouvelle feuille

Set objNewSheet = Worksheets.Add

objNewSheet.Activate

CompteurFeuilles = 2

CompteurLignes = 2

'prépare les titres

Range("A1").Value = "Nom du tableau"

Range("B1").Value = "Source des données"

Range("C1").Value = "Feuille"

Range("D1").Value = "Plage du tableau"

Range("E1").Value = "Rafraîchi par"

Range("F1").Value = "Rafraîchi le"

'extraît l'information sur les Tableau croisés dynamiques

Do While CompteurFeuilles <= Worksheets.Count

Sheets(CompteurFeuilles).Select

For Each tcd In ActiveSheet.PivotTables

objNewSheet.Cells(CompteurLignes, 1).Value = tcd.Name

objNewSheet.Cells(CompteurLignes, 2).Value = tcd.SourceData

objNewSheet.Cells(CompteurLignes, 3).Value = ActiveSheet.Name

objNewSheet.Cells(CompteurLignes, 4).Value = tcd.TableRange1.Address

objNewSheet.Cells(CompteurLignes, 5).Value = tcd.RefreshName

objNewSheet.Cells(CompteurLignes, 6).Value = tcd.RefreshDate

CompteurLignes = CompteurLignes + 1

CompteurFeuilles = CompteurFeuilles + 1

Loop

objNewSheet.Activate

Columns("A:F").EntireColumn.AutoFit 'adapte la largeur des colonnes

Application.ScreenUpdating = True

End Sub

Il ne sera pas difficile de transformer cette procédure en une fonction qui aurait pour argument un Classeur – ainsi, vous pourriez facilement lister les détails des TCD dans n'importe quel Classeur sans devoir y ajouter du code…

Pour aller plus loin en VBA…

Et voici quelques liens qui pourraient vous être utiles dans votre utilisation de Excel, de VBA et des tableaux croisés dynamiques…

Une fonction VBA bien utile si vous travaillez avec les tableaux croisés dynamiques: Nettoyer la “cache” des tables pivot
Un peu plus sur le principe de tableau croisé dynamique [en anglais]
Liste de toutes les fonctions Excel (plus de 480 fonctions!)
Liste de toutes les fonctions VBA
Des outils Excel à télécharger gratuitement – développés en VBA – pour vous inspirer…
Comment utiliser RECHERCHEV en VBA
Calcul de la SOMME en VBA

Laissez un commentaire Annuler la réponse

3 commentaires sur “VBA: Détails des Tableaux croisés dynamiques dans le Classeur”

3 commentaires

Stéphane
08/06/2016, 16:14

Merci! C'est exactement ce que je cherchais

Répondre
- JML
  27/02/2017, 12:06
  
  Merci. C'est très pratique
  Dans mon cas j'ai du modifier
  Sheets(CompteurFeuilles).Select
  en
  Sheets(CompteurFeuilles).Activate.
  
  Répondre
salim gherbi
06/01/2021, 14:25

Merci, c'est très utile

Répondre