🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Pivot Table dans Excel — créer, rafraîchir, et pourquoi il affiche de vieux chiffres

|

VBA Pivot Table dans Excel — créer, rafraîchir, et pourquoi il affiche de vieux chiffres

TL;DR — Un PivotTable est bâti sur un PivotCache, une copie figée des données source. Construisez-le en deux temps : PivotCaches.Create(xlDatabase, source) puis .CreatePivotTable(destination). Disposez le rapport en réglant l'.Orientation de chaque champ (xlRowField, xlColumnField, xlPageField) et ajoutez les nombres avec AddDataField. Le piège qui attrape tout le monde : vous modifiez la source et le pivot continue d'afficher les anciens chiffres jusqu'à ce que vous appeliez pt.RefreshTable. Pointez le cache sur une Table et le pivot grandit avec vos données au lieu de se figer sur une plage fixe.

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:="tblSales")     ' un nom de Table -> grandit tout seul
Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales")

pt.PivotFields("Region").Orientation = xlRowField
pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum

pt.RefreshTable                                          ' relit la source apres chaque changement

Tous ceux qui automatisent un reporting finissent par écrire une macro qui construit un PivotTable, puis se heurtent au même mur : le pivot est juste le jour où vous le construisez, et faux tous les jours suivants. Vous ajoutez cent lignes de ventes, vous lancez le rapport, et les totaux n'ont pas bougé. Aucune erreur. La cause est la seule idée à retenir sur les pivots : un PivotTable ne synthétise pas vos cellules — il synthétise un PivotCache, une copie des données faite au moment de la construction. Chaque bizarrerie ci-dessous en découle : pourquoi vous rafraîchissez, pourquoi la plage source compte, pourquoi deux pivots peuvent partager un même cache. Tenez « il lit un instantané, pas la feuille » et le pivot cesse de vous surprendre.

Ce que vous allez apprendre

  • Le modèle mental — un pivot lit un PivotCache, pas vos cellules vivantes
  • La construction moderne en deux temps : PivotCaches.Create puis CreatePivotTable
  • Disposer les champs avec les quatre valeurs d'Orientation, et ajouter des champs de données
  • Le piège du rafraîchissement — pourquoi votre pivot affiche de vieux chiffres et comment le corriger
  • Le piège de la plage source — pointer le cache sur une Table pour que le pivot grandisse
  • Retrouver, repointer et effacer les pivots qu'un classeur a accumulés

Le modèle mental : un pivot lit un cache, pas vos cellules

Quand vous construisez un PivotTable, Excel ne le câble pas à votre feuille. Il prend les données source, les copie dans une structure cachée en mémoire appelée PivotCache, et le pivot visible n'est qu'une vue sur ce cache. Le cache est une photographie prise à l'instant de la construction. Modifiez la source ensuite et la photographie ne change pas — c'est exactement pourquoi le pivot continue d'afficher de vieux chiffres.

Debug.Print pt.PivotCache.SourceData     ' ce a partir de quoi le cache a ete bati
Debug.Print pt.PivotCache.RecordCount    ' combien de lignes contient l'INSTANTANE - pas la feuille

Deux conséquences utiles en découlent directement. D'abord, plusieurs pivots peuvent partager un même cache (construisez le second à partir de pt.PivotCache plutôt que d'un nouveau PivotCaches.Create), ce qui garde le fichier léger et les rafraîchit ensemble. Ensuite, rafraîchir n'est pas une corvée d'entretien optionnelle — c'est l'étape qui reprend la photographie. Dès que vous voyez le cache comme un objet distinct doté de sa propre copie des données, le reste de l'API cesse d'être un fourre-tout de méthodes et devient une seule histoire : construire le cache, façonner la vue, reprendre l'instantané.

Construire un PivotTable : le cache d'abord, puis la table

Le schéma moderne fiable, c'est deux étapes explicites. Créez le cache à partir de la source, puis créez la table à partir du cache :

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sales!A1:D1000")

Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), _
    TableName:="pt_Sales")

SourceData accepte un nom de Table ("tblSales"), un nom défini, ou une adresse qualifiée par feuille sous forme de chaîne — et c'est la forme adresse qui abrite le piège de la plage source, sur lequel nous revenons plus bas. TableDestination doit être une vraie cellule sur une feuille, et le pivot a besoin de place pour grandir : pointez-le donc sur le coin supérieur gauche d'une zone par ailleurs vide. Nommer le pivot (TableName:="pt_Sales") compte : c'est ainsi que vous le retrouvez avec Worksheets("Report").PivotTables("pt_Sales") lors d'une exécution ultérieure, au lieu de deviner avec PivotTables(1).

Vous verrez peut-être encore de vieilles macros utiliser Worksheets("Report").PivotTableWizard ... en un seul appel. Ça marche, mais ça cache le cache, ne vous en donne aucune poignée propre, et c'est exactement le code que crache l'enregistreur de macros. Préférez la forme en deux temps pour que le cache — l'objet qui contient réellement vos données — soit quelque chose que vous pouvez nommer et rafraîchir.

Disposer les champs : les quatre orientations

Un PivotTable a quatre zones, et chaque champ atterrit dans l'une d'elles via son .Orientation. Les champs de ligne et de colonne forment la grille ; le champ de page est le filtre du rapport ; les champs de données sont les nombres synthétisés :

With pt
    .PivotFields("Region").Orientation = xlRowField
    .PivotFields("Month").Orientation = xlColumnField
    .PivotFields("Category").Orientation = xlPageField     ' le filtre du rapport
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With

Deux choses mordent ici. D'abord, un nom de champ doit correspondre exactement à l'en-tête source — PivotFields("Ammount") déclenche une erreur d'exécution, pas une colonne vide. Ensuite, plus sournois : la synthèse par défaut d'un champ de données est Somme uniquement quand toute la colonne est numérique. Glissez une seule valeur texte ou une seule cellule vide dans une colonne de montants et Excel bascule silencieusement le défaut sur Nombre (Count), et votre rapport affiche un décompte là où vous attendiez une somme. Voilà pourquoi ajouter les champs de données avec un AddDataField ... , xlSum explicite vaut mieux que les déposer en espérant — vous déclarez la fonction au lieu d'hériter d'une supposition. Utilisez xlAverage, xlCount, xlMax et les autres de la même façon.

Le piège du rafraîchissement : pourquoi votre pivot affiche de vieux chiffres

C'est le bug de pivot numéro un, et ce n'est pas du tout un bug — c'est le cache qui fait son travail. Vous modifiez la source, le cache tient toujours l'ancienne photographie, et le pivot l'affiche fidèlement :

' Vous avez ajoute 200 lignes a la source... le pivot ne l'a pas remarque.
pt.RefreshTable            ' relit le cache de CE pivot depuis sa source

pt.PivotCache.Refresh      ' rafraichit le cache -> met a jour chaque pivot qui le partage
ThisWorkbook.RefreshAll    ' rafraichit tous les pivots, requetes et liens du fichier

RefreshTable reprend l'instantané pour un seul pivot. Si plusieurs pivots partagent un cache, PivotCache.Refresh les met tous à jour d'un coup. La règle pratique : toute macro qui modifie les données doit rafraîchir les pivots qui les lisent, et tout pivot sur lequel vous comptez sans surveillance doit se rafraîchir à l'ouverture du classeur. Un pivot construit une fois et jamais rafraîchi est une capture d'écran, pas un rapport — il continuera d'afficher le jour de sa naissance. Si vous avez déjà envoyé par mail un tableau de bord « en direct » qui s'est révélé vieux d'une semaine, c'est pour ça.

Le piège de la plage source : pointez le cache sur une Table

Le second échec silencieux, c'est la plage source. Construisez le cache à partir d'une adresse fixe et les lignes du mois prochain tombent en dehors — même un rafraîchissement ne les ramènera pas, car elles ne sont pas dans la plage que le cache a reçu l'ordre de lire :

' FRAGILE - les nouvelles lignes sous la ligne 1000 sont invisibles a jamais
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "Sales!A1:D1000")

' ROBUSTE - une Table s'auto-etend, donc le cache voit toujours chaque ligne
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")

Pointer le cache sur une Table (un ListObject) est le correctif qui rend tout l'aval auto-entretenu : la Table grandit à mesure que vous ajoutez des lignes, le cache lit toute la Table, et un simple RefreshTable récupère les nouvelles données sans le moindre calcul de plage. Une plage nommée dynamique fait le même travail si vous ne pouvez pas utiliser de Table. Pour repointer un pivot existant sur une meilleure source sans le reconstruire, donnez-lui un nouveau cache avec ChangePivotCache. Voir VBA Table pour transformer une plage en Table et VBA Named Range pour l'alternative par nom dynamique.

Retrouver, repointer et effacer les pivots

Les pivots s'accumulent, et une exécution ultérieure doit retrouver celui qu'elle a créé plutôt que d'empiler une deuxième copie par-dessus. Parcourez les collections pour les localiser ou les nettoyer :

Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        Debug.Print ws.Name & " ! " & pt.Name & " -> " & pt.PivotCache.SourceData
    Next pt
Next ws

Avant de reconstruire, vérifiez si votre pivot nommé existe déjà et rafraîchissez-le au lieu d'ajouter un doublon ; si vous voulez réellement un pivot neuf, supprimez l'ancien avec pt.TableRange2.Clear (toute la zone du pivot) pour qu'une nouvelle construction dispose d'un espace propre. Traitez le pivot comme un objet durable que vous retrouvez par son nom — la même discipline qui empêche le code VBA Worksheet d'écrire sur le mauvais onglet empêche votre macro de reporting d'engendrer des pivots à chaque exécution.

Comment ExcelMaster aide

Les erreurs de pivot qui coûtent vraiment du temps sont discrètes : le rapport qui ne s'est jamais rafraîchi, la plage source qui s'est arrêtée à la ligne 1000 en mars, le total devenu un décompte parce qu'une cellule contenait du texte. Chacune s'exécute sans erreur et tend à quelqu'un le mauvais chiffre.

ExcelMaster construit les pivots comme le ferait un analyste soigneux. Demandez-lui de « synthétiser les ventes par région et par mois », et il pointe le cache sur une Table pour que la source grandisse d'elle-même, règle explicitement la fonction de synthèse de chaque champ de données au lieu d'hériter de Nombre, rafraîchit après avoir écrit, et retrouve le pivot par son nom pour qu'une nouvelle exécution mette à jour le rapport au lieu de le dupliquer. Vous décrivez la synthèse voulue ; il câble le cache, les champs et le rafraîchissement pour que le chiffre soit encore juste le mois prochain.

Questions fréquentes

Comment créer un tableau croisé dynamique en VBA ?

Construisez-le en deux temps. Créez d'abord un cache à partir de la source : Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tblSales"). Créez ensuite la table à partir du cache : Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales"). Pointez SourceData sur un nom de Table pour que le pivot grandisse avec vos données, et donnez un nom au pivot pour pouvoir le retrouver.

Pourquoi mon tableau croisé dynamique VBA ne se met-il pas à jour quand les données changent ?

Parce qu'un PivotTable lit un PivotCache — une copie figée de la source faite au moment de la construction — pas vos cellules vivantes. Modifier la source ne touche pas le cache, donc le pivot continue d'afficher les anciens chiffres. Appelez pt.RefreshTable pour relire un seul pivot, pt.PivotCache.Refresh pour chaque pivot qui partage le cache, ou ThisWorkbook.RefreshAll pour tout le fichier après un changement.

Comment ajouter des champs à un tableau croisé dynamique en VBA ?

Réglez l'Orientation de chaque champ : pt.PivotFields("Region").Orientation = xlRowField, puis xlColumnField et xlPageField pour les zones colonne et filtre. Ajoutez les nombres avec pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum afin de contrôler la fonction de synthèse — sinon Excel devine, et bascule sur Nombre si la colonne contient la moindre cellule texte ou vide.

Comment empêcher la plage source du pivot de rater de nouvelles lignes ?

Ne pointez pas le cache sur une adresse fixe comme "Sales!A1:D1000", car les lignes ajoutées en dessous ne sont jamais vues. Convertissez la source en Table et passez le nom de la Table comme SourceData (PivotCaches.Create(xlDatabase, "tblSales")) ; la Table s'auto-étend, donc un RefreshTable normal récupère chaque nouvelle ligne. Une plage nommée dynamique fonctionne aussi si une Table n'est pas envisageable.

Comment parcourir tous les tableaux croisés dynamiques d'un classeur ?

Imbriquez deux boucles : For Each ws In ThisWorkbook.Worksheets puis For Each pt In ws.PivotTables. À l'intérieur, pt.Name et pt.PivotCache.SourceData vous disent quel pivot lit quoi. Utilisez la même boucle pour rafraîchir chaque pivot, ou pour vérifier si votre pivot nommé existe déjà avant d'en construire un doublon.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1 — vérifié le 02/09/2026.

Guides connexes : VBA Table · VBA Chart · VBA Range · VBA Named Range · VBA Worksheet