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 avec ce tableau croisé dynamique est qu'il ne sera pas automatiquement actualisé s'il y a un changement dans les 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.

Vous pouvez télécharger ce modèle Excel VBA Refresh Pivot Table ici - VBA Refresh Pivot Table Excel Template

# 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 à l'aide de 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 comme «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 de modification de feuille de calcul 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 si un changement se produit 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».