Tableau croisé dynamique Excel VBA Refresh
Lorsque nous insérons un tableau croisé dynamique dans la feuille, une fois que les données changent, les données du tableau croisé dynamique ne changent pas elles-mêmes, nous devons le faire manuellement, mais dans VBA, il existe une instruction pour actualiser le tableau croisé dynamique qui est expression.refreshtable , en utilisant cela, nous pouvons actualiser le tableau croisé dynamique en faisant référence à la feuille de calcul le constituant ou nous pouvons nous référer à l'ensemble des tableaux croisés dynamiques dans les feuilles de calcul et les actualiser tous en même temps.
Le tableau croisé dynamique est essentiel pour analyser l'énorme quantité de données. Cela facilite l'analyse, la synthèse et l'interprétation des données utiles. Cependant, l'un des problèmes de ce tableau croisé dynamique est qu'il ne sera pas automatiquement actualisé en cas de modification des données source. L'utilisateur doit actualiser le tableau croisé dynamique en accédant au tableau croisé dynamique particulier à chaque fois qu'il y a un changement. Mais dites adieu au processus manuel car nous avons ici la méthode pour actualiser le tableau croisé dynamique dès que vous apportez un changement dans le tableau croisé dynamique.
Comment actualiser automatiquement le code VBA des données de tableau croisé dynamique?
Le seul moment où le tableau croisé dynamique doit être mis à jour est chaque fois qu'il y a un changement dans les données source du tableau croisé dynamique auquel nous faisons référence.
Par exemple, regardez les données ci-dessous et le tableau croisé dynamique.
Maintenant, je vais changer les nombres dans les données sources, c'est-à-dire de A1 à B17.
Dans la cellule B9, je dois changer la valeur de 499 à 1499, soit une augmentation de 1000 dans les données, mais si vous regardez le pivot, le résultat est toujours 4295 au lieu de 5295. Je dois actualiser manuellement mon tableau croisé dynamique pour mettre à jour le tableau croisé dynamique.
Pour résoudre ce problème, nous devons écrire un code de macro Excel simple pour actualiser le tableau croisé dynamique chaque fois qu'il y a un changement dans les données source.
# 1 - Macro simple pour actualiser tous les tableaux
Étape 1: modifier l'événement de la feuille de données
Nous devons déclencher l'événement de modification de la fiche technique. Dans l'éditeur Visual Basic, double-cliquez sur la fiche technique.
Une fois que vous double-cliquez sur la feuille, sélectionnez "Feuille de travail" et sélectionnez l'événement comme "Modifier".
Vous verrez une sous-procédure automatique ouverte en tant que Worksheet_Change (ByVal Target As Range)
Étape 2: utiliser l'objet de feuille de calcul
Reportez-vous à la fiche technique en utilisant l'objet Worksheets.
Étape 3: Référez-vous au tableau croisé dynamique par nom
Faites référence au nom du tableau croisé dynamique par le nom du tableau croisé dynamique.
Étape 4: Utilisez la méthode Refresh Table
Sélectionnez la méthode en tant que "Refresh Table".
À présent, ce code actualisera le tableau croisé dynamique «PivotTable1» chaque fois qu'il y aura un changement dans la feuille de données source. Vous pouvez utiliser le code ci-dessous. Il vous suffit de changer le nom du tableau croisé dynamique.
Code:
Private Sub Worksheet_Change (ByVal Target As Range) Worksheet ("Data Sheet"). Tableaux croisés dynamiques ("PivotTable1"). RefreshTable End Sub
# 2 - Actualiser tous les tableaux croisés dynamiques de la même feuille de calcul
Si vous avez plusieurs tableaux croisés dynamiques dans la même feuille de calcul, vous pouvez actualiser tous les tableaux croisés dynamiques en un seul clic. Utilisez le code ci-dessous pour actualiser tous les tableaux croisés dynamiques de la feuille.
Code:
Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Sélectionnez With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable. RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub
Vous devez modifier le nom de la feuille de calcul et les noms du tableau croisé dynamique selon les détails de votre feuille de calcul.
# 3 - Actualiser toutes les tables du classeur
Il est hautement improbable que nous ayons tous les tableaux croisés dynamiques sur la même feuille de calcul. Habituellement, pour chaque rapport, nous essayons d'ajouter des tableaux croisés dynamiques séparés dans des feuilles séparées. Dans ces cas, nous ne pouvons pas continuer à écrire le code de chaque tableau croisé dynamique à actualiser.
Donc, ce que nous pouvons faire, c'est avec un seul code utilisant des boucles, nous pouvons parcourir tous les tableaux croisés dynamiques du classeur et les actualiser d'un simple clic sur le bouton.
Le code ci-dessous parcourra chaque tableau croisé dynamique et les actualisera.
Code 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT comme tableau croisé dynamique pour chaque PT dans ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub
Code 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC As PivotCache For Each PC In ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Les deux codes feront le rafraîchissement des tableaux croisés dynamiques.
Si vous souhaitez que le tableau croisé dynamique soit actualisé dès qu'il y a une modification dans la feuille de données de la feuille pivotante, vous devez copier et coller les codes ci-dessus dans l'événement Worksheet Change dans ce classeur.
# 4 - Évitez le temps de chargement en utilisant l'événement de désactivation de feuille de calcul
Lorsque nous utilisons l'événement «Changement de feuille de travail», il continue de s'actualiser même s'il n'y a pas de changement dans la source de données, mais en cas de changement dans la feuille de calcul.
Même si vous entrez un seul point dans la feuille de calcul, il essaie d'actualiser le tableau croisé dynamique. Pour éviter cela, nous pouvons utiliser la méthode «Worksheet Deactivate» au lieu de la méthode «Worksheet Change».
Désactivez les mises à jour des événements sur le tableau croisé dynamique lors du passage d'une feuille à une autre.
