Le problème

J'aimerai que mon tableau croisé dynamique se mette à jour automatiquement et simultanément ! Comment puis-je faire ?

La solution

Tout le monde connait les tableaux croisés dynamiques ? Si oui, on continue ! Pour ceux qui ne connaissent pas, je vous invite à lire cette première astuce qui est une brève introduction à la construction de ces tableaux très utiles.

Imaginons notre tableau de données suivant :

Et nous voulons arriver à :

Si nous voulons créer notre tableau, nous allons passer par les différentes étapes suivantes :

Nous allons arriver ici :

Ensuite, il faut cliquer sur suivant, ça normalement, c’est facile et logique… . Après, on sélectionne le tableau de données :

On clique sur suivant et enfin, le plus facile, on choisit où sera situé le tableau croisé dynamique.

Il est plus facile de cliquer sur disposition pour choisir la place des champs dans le tableau plutôt que de les choisir une fois revenu dans Excel. Exemple, je clique sur disposition avant de cliquer sur terminer et j’obtiens :

Une fois ici, je fais glisser les champs dans mon tableau et je valide.

Et voilà, notre tableau est construit :

Bon, là, le souci, comme tous tableaux croisés dynamiques, il faut cliquer sur le point d’exclamation de la barre d’outils pour le mettre à jour :

De plus, si on ajoute des données, elles ne seront pas prises en compte, vous êtes d’accord ? si si, je vous assure, ajoutez des données à la ligne 18 et vous verrez que cette ligne ne sera pas prise en compte.

J’en vois certains déjà venir et dire :
- t’as qu’à juste sélectionner tes lignes entières comme ça elles seront prises en compte tes données, banane !!!
Remarque pertinente à laquelle je répondrais
- « Oui effectivement mais les données ne se mettent pas à jour, malgré tout, automatiquement ! »
- « Alors, tu nous expliques comment tu fais pour mettre à jour automatiquement tes tableaux croisés sans qu’on est à cliquer sur le point d’exclamation ?? »
- « Houlà petit newbie, calme toi, je vais tout t’expliquer. »

Pour cela, nous allons tout d’abord créer une zone nommée.

Une zone nommée, Kesako ?

Une zone nommée, sur Excel, est une cellule ou une plage de cellule que l’on a nommée expressément par un intitulé clair. Exemple, je peux sélectionner mon tableau de données et le nommer « DONNEES ».
Comme ceci :

Ou comme cela :

Dans les 2 cas, vous obtiendrez ceci :

En effet, le nom « DONNEES » fait bien référence à la table B2 :D17.

Le nom ne doit pas comporter d’espace. Si vous souhaitez mettre quelque chose du genre : « Tableau Données », Excel n’en voudra pas. Je vous conseille donc de mettre par exemple « Tableau_Donnees » ou « TableauDonnees ». De même, évitez les accents !

Là, encore une fois, vous allez me dire : « Ok, c’est bien, mais ma zone est figée encore une fois… »
Vous avez raison, nous ne sommes qu’à la moitié de l’astuce. Pour que cette zone soit dynamique, il faut mettre une formule à la place de mettre =Feuil1!$B$2 :$B$17

Notre formule, la voici :

Nous allons l’associer à un

qui compte le nombre de ligne non vides. A partir de là, j’ai tout dit.

Nous allons construire cette fonction ensemble :
On commence par choisir la cellule référence :La première cellule du tableau, ici B2.

Bien mettre les $, sinon, la formule risque de bouger et ça ne va pas fonctionner.

Ensuite, on peut choisir d’utiliser les arguments lignes et colonnes, mais moi, je préfère hauteur et largeur. Ma formule devient donc :

La hauteur, c’est ce qui nous intéresse. C’est le plus important puisque notre tableau aura des lignes supplémentaires. C’est ici que la formule NBVAL() entre en jeu :

Par cette formule, je compte le nombre de cellules non vides comprises entre B2 et B65000. Cette formule va me renvoyer un nombre. Dans notre exemple, le nombre renvoyé est 16. Cela signifie que nous décalons la hauteur du tableau de 16 lignes à partir de B2. Enfin, la largeur du tableau qui ne bouge pas, donc pas la peine de mettre un nbval(). A la place, on va mettre le nombre de colonnes présentes dans notre tableau, ici : 3.

Et voilà, votre zone est devenue dynamique.
Vous devriez obtenir ceci normalement:

Revenons maintenant à notre TDC. A la place de mettre :

nous allons mettre le nom de la zone nommée créée précédemment :

Ensuite, si le tableau croisé dynamique se trouve dans la même page, entrez ce code

Ici, le tableau croisé dynamique s’appelle « Tableau croisé dynamique3 ». Pour connaitre le nom de votre TDC, placez-vous dessus, cliquez droit puis allez dans option. Là, vous pouvez visualiser le nom et différentes options de votre tableau.

Si le TDC est dans une feuille différente, alors entrez ce code dans la feuille où est situé le TDC

C’est le même code hormis la toute première ligne.
Fermez le VBA et ensuite testez ! Voilà, votre Tableau croisé dynamique est vraiment dynamique puisqu’il se met à jour simultanément.

- C’est bien beau, mais je le mets où ce code ?
Ah j’oubliais de vous préciser : Il ne faut pas coller ce code dans le classeur mais dans le VBA. Pour cela :

Vous arrivez ici :

J’ai coupé un peu pour que ça rentre dans le site !
Etudions un peu ce nouvel environnement :

Dans le cadre rouge, vous avez le classeur sur lequel je travaille actuellement. Il se nomme "Classeur 1" et est composé de 3 feuilles nommées expressément feuil1, feuil2, feuil3 et également d’un module appelé ThisWorkbook. Pour ne pas vous embrouiller, je vais aller droit au but (pas comme Marseille…. ) Double-cliquez sur la feuille où est située votre TDC puis collez l’un des 2 codes ci-dessus selon la situation.
Fermez VBA.
Testez si ce n’est déjà fait !