Comment faire pour mise à jour automatique de tableau croisé dynamique utilisant VBA dans Microsoft Excel
Dans cet article, nous allons apprendre comment mettre à jour automatiquement le tableau croisé dynamique à l’aide de VBA dans Microsoft Excel 2010.
Pourquoi avons-nous besoin de mettre à jour le tableau croisé dynamique?
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.
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 fera exécuter le code 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 ne voulions actualiser qu’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 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 calcul. 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.
J’espère que cet article sur la mise à jour automatique du tableau croisé dynamique à l’aide de VBA dans Microsoft Excel est explicatif. Trouvez plus d’articles sur le calcul des valeurs et des formules Excel associées ici. Si vous avez aimé nos blogs, partagez-les avec vos amis sur Facebook. Et vous pouvez également nous suivre sur Twitter et Facebook. Nous serions ravis de vous entendre, faites-nous savoir comment nous pouvons améliorer, compléter ou innover notre travail et le rendre meilleur pour vous. Écrivez-nous à [email protected].
Articles liés:
link: / excel-pivot-tables-how-to-dynamically-update-pivot-table-data-source-range-in-excel [Comment mettre à jour dynamiquement la plage de source de données de tableau croisé dynamique dans Excel]: pour modifier dynamiquement le plage de données source des tableaux croisés dynamiques, nous utilisons des caches pivot. 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 obtiendrez 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.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Run Macro When any Change is Made On Sheet]: Donc pour exécuter votre macro chaque fois que la feuille mises à jour, nous utilisons les événements de feuille de travail de VBA.
link: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [Simplest VBA Code to Highlight Current Row and Column Using]: Utilisez ce petit extrait de code VBA pour mettre en évidence la ligne et la colonne actuelles de la feuille.
link: / events-in-vba-the-worksheet-events-in-excel-vba [Les événements de feuille de travail dans Excel VBA]: L’événement de feuille de calcul est vraiment utile lorsque vous voulez que vos macros s’exécutent lorsqu’un événement spécifié se produit sur la feuille.
Articles populaires:
link: / tips-if-condition-in-excel [Comment utiliser la fonction IF dans Excel]: L’instruction IF dans Excel vérifie la condition et renvoie une valeur spécifique si la condition est TRUE ou renvoie une autre valeur spécifique si FALSE .
link: / formulas-and-functions-introduction-of-vlookup-function [Comment utiliser 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 dans différentes plages et feuilles.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.
link: / tips-countif-in-microsoft-excel [Comment utiliser la fonction COUNTIF dans Excel]: Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.