Conclusion: découvrez comment utiliser une simple macro pour actualiser automatiquement les tableaux croisés dynamiques chaque fois que des modifications sont apportées aux données source. Je partage également une solution non macro pour mettre à jour les tableaux croisés dynamiques lorsque le fichier est ouvert. Inclut un didacticiel vidéo et le téléchargement de fichiers Excel.
Niveau de compétence: intermédiaire
Télécharger le fichier Excel
Si vous apprenez mieux en le faisant vous-même, vous pouvez télécharger le fichier que j’utilise dans la vidéo pour suivre. Voici le fichier Excel qui contient le code VBA.
Actualiser le tableau croisé dynamique automatiquement.xlsm (41,5 Ko)
Mettre à jour automatiquement les tableaux croisés dynamiques
Vos tableaux croisés dynamiques peuvent-ils être mis à jour immédiatement et automatiquement lorsque leurs données sources changent?
Absolument. Cela nécessite l’utilisation d’une macro vraiment simple que je vais vous montrer comment créer ci-dessous.
Si vous n’êtes pas trop familier avec les macros et VBA, je suggère de consulter ma série de vidéos gratuites en 3 parties sur la mise en route des macros & VBA.
De plus, si vous êtes nouveau dans les tableaux croisés dynamiques, J’ai une série pour vous expliquer ce qu’ils sont et comment les utiliser. Regardez la première vidéo de cette série sur les tableaux croisés dynamiques & Tableaux de bord
Pour mettre à jour automatiquement nos tableaux croisés dynamiques, nous allons écrire une macro avec une instruction simple. Cette instruction dit essentiellement: lorsque j’apporte une modification à ma feuille de calcul, actualisez tous les tableaux croisés dynamiques et les connexions de données. Voici les étapes pour créer la macro.
Ouvrez Visual Basic Editor.
Vous pouvez le faire en cliquant sur le bouton Visual Basic de l’onglet Développeur du ruban.
Le raccourci clavier pour ouvrir l’éditeur Visual Basic est Alt + F11.
Si vous ne voyez pas l’onglet Développeur, vous pouvez le rendre visible en utilisant les instructions ici. Vous ne devez le faire qu’une seule fois, puis l’onglet Développeur sera toujours visible chaque fois que vous ouvrirez Excel à l’avenir.
Ouvrez le module Feuille qui contient vos données source.
Dans la fenêtre Explorateur de projets de l’éditeur Visual Basic, recherchez le classeur que vous souhaitez modifier. Sous ce classeur sont répertoriées les feuilles dans le classeur. Sélectionnez la feuille contenant les données source. Ensuite, double-cliquez dessus.
Si vous ne voyez pas la fenêtre Explorateur de projet, vous pouvez l’activer à partir de la vue menu (raccourci clavier: Ctrl + R).
Ajouter un nouvel événement pour les changements de feuille de calcul.
Double-cliquer sur la feuille ouvre le module de code pour cet objet. Dans le module de code, nous voulons créer une macro d’événement. Pour ce faire, choisissez Feuille de travail dans la liste déroulante Objet sur la gauche.
Cela ajoutera un événement Worksheet_SelectionChange à le module, dont nous ne voulons pas réellement, nous allons donc le supprimer dans un instant. Avant de le faire, allons dans le menu déroulant Procédure sur la droite et choisissez Modifier.
Cela ajoute un nouveau événement en haut appelé Worksheet_Change. Nous allons maintenant mettre en évidence et supprimer le code inutile en dessous.
La macro d’événement Worksheet_Change s’exécutera à chaque fois qu’une modification est apportée aux cellules de cette feuille de calcul. Nous pouvons ajouter du code VBA à l’événement Worksheet_Change pour effectuer des actions lorsque l’utilisateur modifie des cellules.
Remarque: l’événement SelectionChange qui est ajouté par défaut s’exécutera à chaque fois que l’utilisateur sélectionne une cellule dans la feuille. Puisque nous voulons que le code s’exécute uniquement lorsque l’utilisateur modifie / change les cellules, nous utilisons l’événement Change. Consultez mon article sur les modules de code VBA & Comment exécuter des macros en fonction des événements utilisateur pour en savoir plus sur les modules de feuille et les événements.
Ajoutez le code VBA à actualiser tous les tableaux croisés dynamiques.
Ensuite, juste en dessous de la ligne Worksheet_Change, saisissez cette instruction:
ThisWorkbook.RefreshAll
La méthode RefreshAll actualisera tous les tableaux croisés dynamiques, requêtes et connexions de données dans le classeur. Cette action est la même que si vous cliquez manuellement sur le bouton Actualiser dans l’onglet Données.
Ajouter cette ligne de code à l’événement Worksheet_Change actualisera le classeur chaque fois qu’une modification est apportée à la feuille de calcul indiquant que le code est dans.
Tableau croisé dynamique & Données source sur la même feuille
Aleksandrs a posé une excellente question sur les commentaires de la vidéo YouTube. Si votre tableau croisé dynamique et vos données sources sont sur la même feuille, vous devrez ajouter du code pour désactiver les événements.
L’actualisation place l’événement dans une boucle récursive et peut finir par planter Excel. Voici le code pour éviter cela.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Vérification pour vous assurer que votre macro est en cours d’exécution
Une façon de vérifier si la macro fonctionne est de le tester. Apportez une modification aux données source et voyez si elles se reflètent dans votre tableau croisé dynamique.Si votre modification n’est pas facile à repérer parce que vous avez trop de données, ou pour une autre raison, il existe un autre moyen de voir si votre macro se déclenche.
Dans l’éditeur VB, vous pouvez cliquer sur le bouton colonne grise juste à gauche de votre macro Worksheet_Change. Cela fera apparaître un cercle rouge. Il met également en évidence cette ligne de code en rouge.
C’est ce qu’on appelle un arrêt ou un point d’arrêt.
Le raccourci clavier pour activer / désactiver un point d’arrêt est: F9
Désormais, chaque fois qu’une action se produit et déclenche la macro, Excel passe à l’éditeur VB et met la macro en pause afin que vous puissiez vérifier le code. Dans notre cas, cette action correspond à toute modification apportée à la feuille de calcul.
Vous pouvez ensuite appuyer sur F8 pour parcourir chaque ligne, ou appuyez sur F5 pour courir jusqu’à la fin (ou au point d’arrêt suivant).
Si vous apportez une modification à la feuille de calcul et qu’Excel ne vous entraîne pas dans l’éditeur VB, vous savez qu’il y a un problème avec la macro ne fonctionne pas. Si tel est le cas, il est probable que vous n’ayez pas enregistré le fichier en tant que classeur prenant en charge les macros (.xlsm) et / ou les macros activées. Vous devrez peut-être enregistrer & fermer le fichier, puis le rouvrir et activer les macros.
Pour supprimer le point d’arrêt que vous avez placé sur la macro, cliquez simplement sur le cercle rouge pour le faire disparaître (raccourci clavier: F9).
Le raccourci clavier pour effacer tous les points d’arrêt est: Ctrl + Maj + F9
Actualisation des tableaux croisés dynamiques sans Macro
Un inconvénient à utiliser cette macro pour actualiser vos tableaux croisés dynamiques est que tout historique d’annulation est perdu à chaque exécution de la macro. En d’autres termes, lorsque vous cliquez sur le bouton Annuler (ou appuyez sur Ctrl + Z), Excel ne se souvient pas de la dernière chose que vous avez faite et ne peut donc pas l’annuler. Par conséquent, rien ne se passera et votre dernière modification ne sera pas annulée.
Il existe une alternative qui vous permet de conserver votre historique d’annulation. Cependant, cette alternative actualise uniquement votre tableau croisé dynamique lorsque le classeur est ouvert, pas à chaque fois qu’une modification est apportée. Voici comment vous pouvez utiliser cette option.
À partir de n’importe quelle cellule de votre tableau croisé dynamique:
- Accédez à l’onglet Analyser dans le ruban.
- Cliquez sur le bouton Options.
- Accédez à l’onglet Données dans la nouvelle fenêtre qui s’ouvre.
- Cochez la case « Actualiser les données lors de l’ouverture du fichier ».
Après avoir cliqué sur OK, vous pouvez recevoir le message d’avertissement suivant si plusieurs tableaux croisés dynamiques ont été créés à partir de la même plage de données source. Cliquez simplement sur OK pour le parcourir.
Encore une fois, juste à titre de comparaison, si vous utilisez cette option, vous conservez l’historique d’annulation, mais cela actualise uniquement le tableau croisé dynamique lorsque le classeur est fermé et rouvert. Si vous utilisez l’option macro, vous perdez l’historique d’annulation, mais le tableau croisé dynamique est automatiquement mis à jour tes chaque fois qu’une modification est apportée au classeur.
Variations pour actualiser les tableaux croisés dynamiques
La macro que nous avons examinée actualisera non seulement vos tableaux croisés dynamiques, mais actualisera également toutes les requêtes. . Si vous souhaitez actualiser uniquement les tableaux croisés dynamiques, vous pouvez remplacer la commande « ThisWorkbook.RefreshAll » par ce code à la place:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Chaque tableau croisé dynamique est connecté à un cache pivot sous-jacent, qui est connecté aux données source. Le code parcourt tous les caches pivot du classeur et actualise chacun d’entre eux. Les tableaux croisés dynamiques de la même plage source peuvent partager des caches croisés dynamiques. Cette méthode est donc plus rapide que de parcourir tous les tableaux croisés dynamiques .
De même, disons que vous ne voulez actualiser qu’un seul tableau croisé dynamique. Dans ce cas, vous pouvez échanger le code « ThisWorkbook.RefreshAll » avec le code ci-dessous.
Et enfin, si vous utilisez Power Query et souhaitez désactiver l’actualisation en arrière-plan afin que les requêtes soient actualisées AVANT les tableaux croisés dynamiques, j’ai écrit un article pour expliquer comment faire cela en désactivant l’actualisation en arrière-plan sur les requêtes.
Utilisez le Désactiver l’événement à la place
Une autre option consiste à utiliser l’événement Worksheet_Deactivate au lieu de Worksheet_Change. L’événement Worksheet_Deactivate sera exécuté chaque fois que l’utilisateur quitte la feuille et sélectionne une feuille différente. Cela permet à l’utilisateur d’apporter toutes les modifications à les données source, puis le tableau croisé dynamique sera automatiquement actualisé lorsqu’ils accèdent à une autre feuille, y compris les feuilles qui contiennent le tableau croisé dynamique.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Ce code sera toujours placé dans le module de feuille qui contient les données source. C’est une bonne option si vos tableaux croisés dynamiques ou vos connexions de données prennent un quelques secondes ou plus pour mettre à jour, et vous ne voulez pas attendre chaque fois qu’une modification est apportée aux données source.
La seule fois où vous ne voudrez peut-être pas l’utiliser, c’est si votre tableau croisé dynamique et votre source les données sont sur la même feuille.Ce sera généralement un cas rare, et je ne le recommande généralement pas.
Merci à la suggestion de Ted sur celui-ci.
Gagnez du temps & Embarras
J’espère que cet article vous fera gagner du temps et facilitera la tâche des utilisateurs de vos fichiers. Il peut également aider à éviter la gêne lorsque vous oubliez d’actualiser les tableaux croisés dynamiques avant d’envoyer des rapports. Croyez moi, j’ai commis cette erreur plus de fois que je ne voudrais l’admettre… 🙂
