Comment mettre à jour dynamiquement toutes les sources de données des tableaux croisés dynamiques dans Excel

Dans un article précédent, nous avons appris comment vous pouvez modifier et mettre à jour dynamiquement des tableaux croisés dynamiques individuels avec des sources de données réduites ou en expansion.

Dans cet article, nous allons Découvrez comment nous pouvons faire en sorte que tous les tableaux croisés dynamiques dun classeur modifient automatiquement la source de données. En dautres termes, au lieu de changer un tableau croisé dynamique à la fois, nous essaierons de changer la source de données de tous les tableaux croisés dynamiques du classeur pour inclure dynamiquement de nouvelles lignes et colonnes ajoutées aux tables source et refléter instantanément le changement dans les tableaux croisés dynamiques.

Ecrire le code dans la feuille de données source

Puisque nous voulons que cela soit complètement automatique, nous utiliserons des modules de feuille pour écrire du code au lieu dun module de base. Cela nous permettra dutiliser des événements de feuille de calcul.

Si les données source et les tableaux croisés dynamiques sont dans des feuilles différentes, nous écrirons le code VBA pour changer la source de données du tableau croisé dynamique dans lobjet de feuille qui contient les données source ( pas qui contient des tableaux croisés dynamiques).

Appuyez sur CTRL + F11 pour ouvrir léditeur VB. Accédez à présent à lexplorateur de projet et recherchez la feuille contenant les données source. Double-cliquez dessus.

Une nouvelle zone de codage va souvrir. Vous ne verrez peut-être aucun changement, mais vous avez maintenant accès aux événements de la feuille de calcul.

Cliquez sur le menu déroulant de gauche et sélectionnez la feuille de calcul. Dans le menu déroulant de gauche, sélectionnez désactiver. Vous verrez un sous-vide vide écrit sur le nom de la zone de code worksheet_deativate. Notre code pour modifier dynamiquement les données source et actualiser le tableau croisé dynamique ira dans ce bloc de code. Ce code sexécutera chaque fois que vous passerez de la feuille de données à une autre feuille. Vous pouvez consulter tous les événements de la feuille de calcul ici.

Nous sommes maintenant prêts à implémenter le code.

Code source pour mettre à jour dynamiquement tous les tableaux croisés dynamiques dans le classeur avec une nouvelle plage

Pour expliquer comment cela fonctionne, jai un classeur. Ce classeur contient trois feuilles. Sheet1 contient les données source qui peuvent changer. Sheet2 et Sheet3 contiennent des tableaux croisés dynamiques qui dépendent des données source de sheet2.

Jai maintenant écrit ce code dans la zone de codage de sheet1. Jutilise lévénement Worksheet_Deactivate, afin que ce code sexécute pour mettre à jour le tableau croisé dynamique chaque fois que nous passons de la feuille de données source.

Si vous avez un classeur similaire, vous pouvez directement copier ces données.Jai expliqué que ce code fonctionne ci-dessous afin que vous puissiez le modifier selon vos besoins.

Vous pouvez voir leffet de ce code en gif ci-dessous.

Comment ce code change-t-il automatiquement données source et mettre à jour les tableaux croisés dynamiques?

Tout dabord, nous avons utilisé un événement worksheet_deactivate. Cet événement se déclenche uniquement lorsque la feuille contenant le code est commutée ou désactivée. Voici donc comment le code sexécute automatiquement.

Pour obtenir dynamiquement la table entière sous forme de plage de données, nous déterminons la dernière ligne et la dernière colonne.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Column

En utilisant ces deux nombres, nous définissons la source_data. Nous sommes convaincus que la plage de données source commencera toujours à partir de A1. Vous pouvez définir votre propre référence de cellule de début.

Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Nous avons maintenant les données source qui sont dynamique. Nous devons juste lutiliser dans le tableau croisé dynamique.

Comme nous ne savons pas combien de tableaux croisés dynamiques un classeur contiendra à la fois, nous allons parcourir chaque feuille et chaque tableau croisé dynamique de chaque feuille. quil ne reste plus de tableau croisé dynamique. Pour cela, nous utilisons des boucles for imbriquées.

Pour chaque ws dans ce classeur.Feuilles de travail

Pour chaque pt dans ws.Tables croisés dynamiques

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Pt suivant

ws suivant

La première boucle parcourt chaque feuille. La deuxième boucle parcourt chaque tableau croisé dynamique dune feuille.

Les tableaux croisés dynamiques sont attribués à la variable pt. Nous utilisons la méthode ChangePivotCache de lobjet pt. Nous créons dynamiquement un cache pivot à laide de ThisWorkbook.PivotCaches.Create

Méthode. Cette méthode prend deux variables SourceType et SourceData. Comme type de source, nous déclarons xlDatabase et comme SourceData, nous transmettons la plage source_data que nous avons calculée précédemment.

Et que je sasseoir. Nous avons automatisé nos tableaux croisés dynamiques. Cela mettra automatiquement à jour tous les tableaux croisés dynamiques du classeur.

Alors oui les gars, voici comment vous pouvez modifier dynamiquement les plages de sources de données de tous les tableaux croisés dynamiques dans un classeur dans Excel. Jespère avoir été suffisamment explicatif. Si vous avez des questions concernant cet article, faites-le moi savoir dans la section commentaires ci-dessous.

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 nimporte quel tableau croisé dynamique en modifiant la plage de données source.

Comment actualiser automatiquement les tableaux croisés dynamiques à laide de VBA: Pour actualiser automatiquement vos tableaux croisés dynamiques, vous pouvez utiliser les événements VBA. Utilisez cette simple ligne de code pour mettre à jour automatiquement votre tableau croisé dynamique. Vous pouvez utiliser lune des 3 méthodes dactualisation automatique des tableaux croisés dynamiques.

Exécutez une macro si un changement est effectué sur la feuille dans la plage spécifiée: dans vos pratiques VBA, vous auriez besoin dexécuter des macros lorsquune certaine plage ou les changements de cellule. Dans ce cas, pour exécuter des macros lorsquune modification est apportée à une plage cible, nous utilisons lévénement de modification.

Exécuter une macro lorsquune modification est effectuée 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.

Le 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.

Les événements de la feuille de calcul dans Excel VBA | Lévénement de feuille de calcul est vraiment utile lorsque vous souhaitez que vos macros sexécutent lorsquun événement spécifié se produit sur la feuille.

Articles populaires:

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. La fonction RECHERCHEV dans Excel | Cest lune des fonctions les plus utilisées et les plus populaires dExcel qui est utilisée pour rechercher la valeur de différentes plages et feuilles.

COUNTIF dans Excel 2016 | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous navez pas besoin de filtrer vos données pour compter une valeur spécifique. La fonction Countif est essentielle pour préparer votre tableau de bord.

Comment utiliser la fonction SUMIF dans Excel | Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs à des conditions spécifiques.