image

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 étendues.

Dans cet article, nous allons apprendre comment nous pouvons faire en sorte que tous les tableaux croisés dynamiques d’un classeur modifient automatiquement la source de données. En d’autres 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 du 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 d’un module de base. Cela nous permettra d’utiliser link: / events-in-vba-the-worksheet-events-in-excel-vba [worksheet events].

Si les données source et les tableaux croisés dynamiques se trouvent dans des feuilles différentes, nous écrirons le code VBA pour modifier la source de données du tableau croisé dynamique dans l’objet 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 maintenant à l’explorateur de projet et recherchez la feuille contenant les données source. Double-cliquez dessus.

image

Une nouvelle zone de codage s’ouvrira. 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 sources et actualiser le tableau croisé dynamique ira dans ce bloc de code. Ce code s’exécutera chaque fois que vous passerez de la feuille de données à une autre feuille. Vous pouvez lire sur tous les événements de feuille de calcul link: / events-in-vba-the-worksheet-events-in-excel-vba [here].

image

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, j’ai 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.

Maintenant, j’ai écrit ce code dans la zone de codage de sheet1. J’utilise l’événement Worksheet_Deactivate, afin que ce code s’exécute pour mettre à jour le tableau croisé dynamique chaque fois que nous passons de la feuille de données source.

Private Sub Worksheet_Deactivate()

Dim source_data As Range

'Determining last row and column number

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

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

'Setting the new range

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

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

End Sub

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

Vous pouvez voir l’effet de ce code en gif ci-dessous.

image

Comment ce code modifie-t-il automatiquement les données sources et met-il à jour les tableaux croisés dynamiques? Tout d’abord, 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 s’exécute automatiquement.

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

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

lstcol = Cells (1, Columns.Count) .End (xlToLeft) .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))

Maintenant, nous avons les données source qui sont dynamiques. Nous avons juste besoin de l’utiliser 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 les tableaux croisés dynamiques de chaque feuille. De sorte qu’il ne reste plus de tableau croisé dynamique. Pour cela, nous utilisons des boucles for imbriquées.

Pour chaque ws dans ThisWorkbook.Worksheets

Pour chaque pt dans ws.Tables croisés dynamiques

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Pt suivant

Suivant ws

La première boucle parcourt chaque feuille. La deuxième boucle parcourt chaque tableau croisé dynamique d’une feuille. Les tableaux croisés dynamiques sont affectés à la variable pt. * Nous utilisons la méthode ChangePivotCache de l’objet pt. Nous créons dynamiquement un cache pivot à l’aide de la méthode ThisWorkbook.PivotCaches.Create. Cette méthode prend deux variables SourceType et SourceData. En tant que type de source, nous déclarons xlDatabase et en tant que SourceData, nous transmettons la plage source_data que nous avons calculée précédemment.

Et c’est tout. 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 d’un classeur dans Excel. J’espère avoir été suffisamment explicatif. Si vous avez des questions concernant cet article, faites-le moi savoir dans la section commentaires ci-dessous.

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. Ces quelques lignes peuvent mettre à jour dynamiquement n’importe quel tableau croisé dynamique en modifiant la plage de données source.

link: / custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [Comment actualiser automatiquement les tableaux croisés dynamiques à l’aide de VBA]: Pour actualiser automatiquement vos tableaux croisés dynamiques, vous pouvez utiliser Événements VBA. Utilisez cette simple ligne de code pour mettre à jour automatiquement votre tableau croisé dynamique. Vous pouvez utiliser l’une des 3 méthodes d’actualisation automatique des tableaux croisés dynamiques.

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.link: / formulas-and-functions-introduction-of-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.