Comment Auto Refresh tableaux croisés dynamiques à l’aide VBA Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: custom-functions-in-vba

Comme nous le savons tous, chaque fois que nous apportons des modifications aux données source d’un tableau croisé dynamique, cela ne se reflète pas immédiatement dans le tableau croisé dynamique. Nous devons actualiser les tableaux croisés dynamiques pour voir les changements. Et si vous envoyez un fichier mis à jour sans actualiser les tableaux croisés dynamiques, vous pouvez vous sentir embarrassé. Dans cet article, nous allons donc apprendre à actualiser automatiquement un tableau croisé dynamique à l’aide de VBA. Cette façon est plus facile que vous ne l’imaginiez.

Il s’agit de la syntaxe simple pour actualiser automatiquement les tableaux croisés dynamiques dans le classeur.

'Code in Source Data Sheet Object

Private Sub Worksheet_Deactivate()

sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh

End Sub

Que sont les caches de pivot? Chaque tableau croisé dynamique stocke les données dans le cache pivot. C’est pourquoi pivot peut afficher les données précédentes. Lorsque nous actualisons les tableaux croisés dynamiques, il met à jour le cache avec de nouvelles données source pour refléter les modifications sur le tableau croisé dynamique.

Nous avons donc juste besoin d’une macro pour actualiser le cache des tableaux croisés dynamiques. Nous allons le faire en utilisant un lien: / events-in-vba-the-worksheet-events-in-excel-vba [workheet event] afin que nous n’ayons pas à exécuter la macro manuellement. === Où coder pour actualiser automatiquement les tableaux croisés dynamiques? Si vos données source et vos tableaux croisés dynamiques se trouvent dans des feuilles différentes, le code VBA doit figurer dans la feuille de données source.

Ici, nous utiliserons l’événement Worksheet_SelectionChange. Cela rendra le code exécuté chaque fois que nous passerons de la feuille de données source à une autre feuille.

J’expliquerai plus tard pourquoi j’ai utilisé cet événement.

Ici, j’ai des données source dans sheet2 et des tableaux croisés dynamiques dans sheet1.

Ouvrez VBE à l’aide de la touche CTRL + F11. Dans l’explorateur de projet, vous pouvez voir trois objets, Sheet1, Sheet2 et le classeur.

Puisque Sheet2 contient les données source, double-cliquez sur l’objet Sheet2.

Vous pouvez maintenant voir deux menus déroulants en haut de la zone de code. Dans le premier menu déroulant, sélectionnez la feuille de calcul. Et dans la deuxième liste déroulante, sélectionnez Désactiver. Cela insérera un sous-nom vide Worksheet_Deactivate. Notre code sera écrit dans ce sous. Toutes les lignes écrites dans ce sous, get sont exécutées dès que l’utilisateur passe de cette feuille à une autre feuille.

Sur la feuille 1, j’ai deux tableaux croisés dynamiques. Je souhaite actualiser un seul tableau croisé dynamique. Pour cela, j’ai besoin de connaître le nom du tableau croisé dynamique. Pour connaître le nom d’un tableau croisé dynamique, sélectionnez une cellule de ce tableau croisé dynamique et accédez à l’onglet d’analyse du tableau croisé dynamique. Sur le côté gauche, vous verrez le nom du tableau croisé dynamique. Vous pouvez également modifier le nom du tableau croisé dynamique ici.

Maintenant que nous connaissons le nom du tableau croisé dynamique, nous pouvons écrire une ligne simple pour actualiser le tableau croisé dynamique.

Private Sub Worksheet_Deactivate()

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Et c’est fait.

Désormais, chaque fois que vous basculerez à partir des données source, ce code vba s’exécutera pour actualiser le tableau croisé dynamique1. Comme vous pouvez le voir dans le gif ci-dessous.

Comment actualiser tous les tableaux croisés dynamiques dans le classeur? Dans l’exemple ci-dessus, nous voulions uniquement actualiser un tableau croisé dynamique spécifique. Mais si vous souhaitez actualiser tous les tableaux croisés dynamiques d’un classeur, il vous suffit d’apporter de légères modifications à votre code.

Private Sub Worksheet_Deactivate()

'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

For Each pc In ThisWorkbook.PivotCaches

pc.Refresh

Next pc

End Sub

Dans ce code, nous utilisons un lien: / vba-for-loops-with-7-examples [For loop] pour parcourir chaque cache de pivot dans le classeur. L’objet ThisWorkbook contient tous les caches de pivot. Pour y accéder, nous utilisons ThisWorkbook.PivotCaches.

Pourquoi utiliser l’événement Worksheet_Deactivate?

Si vous souhaitez actualiser le tableau croisé dynamique dès qu’une modification est apportée aux données source, vous devez utiliser l’événement Worksheet_Change. Mais je ne le recommande pas. Votre classeur exécutera le code chaque fois que vous apporterez des modifications à la feuille. Vous devrez peut-être effectuer des centaines de modifications avant de vouloir voir le résultat. Mais Excel rafraîchira le tableau croisé dynamique à chaque changement. Cela entraînera une perte de temps et de ressources de traitement. Donc, si vous avez des tableaux croisés dynamiques et des données dans différentes feuilles, il est préférable d’utiliser l’événement de désactivation de feuille de travail. Il vous permet de terminer votre travail. Une fois que vous passez aux feuilles de tableau croisé dynamique pour voir les modifications, il modifie les modifications.

Si vous avez des tableaux croisés dynamiques et des données source sur la même feuille et que vous voulez que les tableaux croisés dynamiques s’actualisent automatiquement, vous pouvez utiliser `link: / tips-using-worksheet-change-event-to-run-macro-when- any-change-is-made [Worksheet_Change Event] `.

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Comment actualiser tout dans les classeurs lorsqu’une modification est apportée aux données source? Si vous souhaitez actualiser tout sur un classeur (graphiques, tableaux croisés dynamiques, formules, etc.), vous pouvez utiliser la commande ThisWorkbook.RefreshAll.

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

Veuillez noter que ce code ne modifie pas la source de données. Donc, si vous ajoutez des données sous les données source, ce code n’inclura pas automatiquement ces données. Vous pouvez utiliser link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] pour stocker les données source. Si vous ne souhaitez pas utiliser de tables, nous pouvons également utiliser VBA pour inclure de nouvelles données. Nous l’apprendrons dans le prochain tutoriel.

Alors oui mec, voici comment vous pouvez actualiser automatiquement les tableaux croisés dynamiques dans Excel. J’espère avoir été suffisamment explicatif et cet article vous a bien servi. Si vous avez des questions sur ce sujet, vous pouvez me les poser dans la section commentaires ci-dessous.

Articles liés:

Comment mettre à jour dynamiquement la plage de sources de données de tableau croisé dynamique dans Excel: Pour modifier dynamiquement la plage de données source des tableaux croisés dynamiques, nous utilisons des caches croisés dynamiques. Ces quelques lignes peuvent mettre à jour dynamiquement n’importe quel tableau croisé dynamique en modifiant la plage de données source. Dans VBA, utilisez des objets de tableaux croisés dynamiques comme indiqué ci-dessous …

link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Exécuter une macro si un changement est effectué sur la feuille dans la plage spécifiée]: * Dans vos pratiques VBA, vous obtenir la nécessité d’exécuter des macros lorsqu’une certaine plage ou cellule change. Dans ce cas, pour exécuter des macros lorsqu’une modification est apportée à une plage cible, nous utilisons l’événement change.

lien: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Exécuter une macro quand une modification est faite sur la feuille] | Donc, pour exécuter votre macro chaque fois que la feuille est mise à jour, nous utilisons les événements de feuille de travail de VBA.

lien: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [code VBA le plus simple pour mettre en évidence la ligne et la colonne actuelles en utilisant] | Utilisez ce petit extrait de code VBA pour mettre en évidence la ligne et la colonne actuelles de la feuille.

lien: / events-in-vba-the-worksheet-events-in-excel-vba [The Worksheet Events in Excel VBA] | Les événements de feuille de calcul sont vraiment utiles lorsque vous souhaitez que vos macros s’exécutent lorsqu’un événement spécifié se produit sur la feuille.

Articles populaires:

lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.

lien: / formules-et-fonctions-introduction-de-vlookup-function [La fonction RECHERCHEV dans Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter une valeur spécifique.

La fonction Countif est indispensable pour préparer votre tableau de bord.

lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.