Comment créer un tableau croisé dynamique avec VBA ?

Avant de vous remettre ce guide et que vous commenciez à utiliser VBA pour créer un tableau croisé dynamique , permettez-moi de vous avouer quelque chose.

J’ai appris à utiliser VBA il y a à peine SIX ans. Et la première fois que j’ai écrit un code de macro pour créer un tableau croisé dynamique, ce fut un échec.

Depuis lors, j’ai appris plus de mon mauvais codage que des codes qui fonctionnent réellement.

Aujourd’hui, je vais vous montrer un moyen simple d’automatiser vos tableaux croisés dynamiques à l’aide d’un code macro.

Normalement, lorsque vous insérez un tableau croisé dynamique dans une feuille de calcul, cela se produit par un processus simple, mais tout ce processus est si rapide que vous ne remarquez jamais ce qui s’est passé.

Dans VBA, tout ce processus est le même, il s’exécute simplement à l’aide d’un code. Dans ce guide, je vais vous montrer chaque étape et vous expliquer comment écrire un code pour cela.

Regardez simplement l’exemple ci-dessous, où vous pouvez exécuter ce code de macro avec un bouton, et il renvoie un nouveau tableau croisé dynamique dans une nouvelle feuille de calcul en un éclair.

Sans plus tarder, commençons à écrire notre code de macro pour créer un tableau croisé dynamique.

Les 8 étapes simples pour écrire un code de macro dans VBA pour créer un tableau croisé dynamique dans Excel

Pour votre commodité, j’ai divisé l’ensemble du processus en 8 étapes simples. Après avoir suivi ces étapes, vous pourrez automatiser tous vos tableaux croisés dynamiques.

Assurez-vous de télécharger ce fichier à partir d’ici pour suivre.

1. Déclarer des variables

La première étape consiste à déclarer les variables que nous devons utiliser dans notre code pour définir différentes choses.

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

Dans le code ci-dessus, nous avons déclaré :

1. PSheet : pour créer une feuille pour un nouveau tableau croisé dynamique.
2. DSheet : à utiliser comme fiche technique.
3. PChache : à utiliser comme nom pour le cache du tableau croisé dynamique.
4. PTable : À utiliser comme nom pour notre tableau croisé dynamique.
5. PRange : pour définir la plage de données source.
6. LastRow et LastCol : pour obtenir la dernière ligne et colonne de notre plage de données.

2. Insérer une nouvelle feuille de calcul

Avant de créer un tableau croisé dynamique, Excel insère une feuille vierge, puis y crée un nouveau tableau croisé dynamique.

Et, le code ci-dessous fera la même chose pour vous.

Il insérera une nouvelle feuille de calcul avec le nom « Tableau croisé dynamique » avant la feuille de calcul active et s’il existe déjà une feuille de calcul portant le même nom, il la supprimera d’abord.

Après avoir inséré une nouvelle feuille de calcul, ce code définira la valeur de la variable PSheet sur la feuille de calcul du tableau croisé dynamique et DSheet sur la feuille de calcul des données source.

'Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")

Assurez-vous de remplacer le nom des feuilles de calcul dans le code par les noms que vous avez dans vos données.

3. Définir la plage de données

Maintenant, la prochaine étape consiste à définir la plage de données à partir de la feuille de calcul source. Ici, vous devez prendre soin d’une chose, vous ne pouvez pas spécifier une plage source fixe.

Vous avez besoin d’un code capable d’identifier l’intégralité des données de la feuille source. Et ci-dessous le code :

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Ce code commencera à partir de la première cellule du tableau de données et sélectionnera jusqu’à la dernière ligne, puis jusqu’à la dernière colonne.

Et enfin, définissez cette plage sélectionnée comme source. La meilleure partie est que vous n’avez pas besoin de changer la source de données à chaque fois lors de la création du tableau croisé dynamique.

4. Créez un cache de pivot

Dans Excel 2000 et supérieur, avant de créer un tableau croisé dynamique, vous devez créer un cache de pivot pour définir la source de données.

Normalement, lorsque vous créez un tableau croisé dynamique, Excel crée automatiquement un cache de pivot sans vous le demander, mais lorsque vous devez utiliser VBA, vous devez écrire un code pour cela.

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

Ce code fonctionne de deux manières, premièrement, définir un cache croisé dynamique à l’aide d’une source de données, et deuxièmement, définir l’adresse de cellule dans la feuille de calcul nouvellement insérée pour insérer le tableau croisé dynamique.

Vous pouvez modifier la position du tableau croisé dynamique en modifiant ce code.

5. Insérer un tableau croisé dynamique vierge

Après le cache croisé dynamique, l’étape suivante consiste à insérer un tableau croisé dynamique vide. Rappelez-vous simplement que lorsque vous créez un tableau croisé dynamique, vous obtenez toujours un pivot vide en premier, puis vous définissez toutes les valeurs, colonnes et lignes.

Ce code fera la même chose :

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Ce code crée un tableau croisé dynamique vide et le nomme « SalesPivotTable ». Vous pouvez modifier ce nom à partir du code lui-même.

6. Insérer des champs de ligne et de colonne

Après avoir créé un tableau croisé dynamique vide, la prochaine étape consiste à insérer des champs de ligne et de colonne, comme vous le faites normalement.

Pour chaque champ de ligne et de colonne, vous devez écrire un code. Ici, nous voulons ajouter des années et des mois dans le champ de ligne et des zones dans le champ de colonne.

Voici le code :

'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
.Orientation = xlRowField
.Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
.Orientation = xlColumnField
.Position = 1
End With

Dans ce code, vous avez mentionné l’année et le mois comme deux champs. Maintenant, si vous regardez le code, vous constaterez qu’un numéro de position s’y trouve également. Ce numéro de position définit la séquence des champs.

Chaque fois que vous devez ajouter plusieurs champs (Ligne ou Colonne), spécifiez leur position. Et vous pouvez modifier les champs en modifiant leur nom à partir du code.

7. Insérer un champ de données

L’essentiel est de définir le champ de valeur dans votre tableau croisé dynamique.

Le code de définition des valeurs diffère de la définition des lignes et des colonnes car nous devons définir ici le formatage des nombres, des positions et des fonctions.

'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With

Vous pouvez ajouter le montant comme champ de valeur avec le code ci-dessus. Et ce code formatera les valeurs sous forme de nombre avec un séparateur (,).

Nous utilisons xlsum pour additionner les valeurs, mais vous pouvez également utiliser xlcount et d’autres fonctions.

8. Formater le tableau croisé dynamique

En fin de compte, vous devez utiliser un code pour formater votre tableau croisé dynamique. Il existe généralement une mise en forme par défaut dans un tableau croisé dynamique, mais vous pouvez modifier cette mise en forme.

Avec VBA, vous pouvez définir le style de formatage dans le code.

Le code est :

'Format Pivot
TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Le code ci-dessus appliquera des bandes de lignes et le style « Pivot Style Medium 9 », mais vous pouvez également utiliser un autre style à partir de ce lien .

Enfin, votre code est prêt à être utilisé.

[CODE COMPLET] Utilisez VBA pour créer un tableau croisé dynamique dans Excel – Macro à copier-coller

Sub InsertPivotTable()
'Macro By Exceladvisor.org

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
.Orientation = xlRowField
.Position = 2
End With

'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Revenue "
End With

'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

End Sub

Tableau croisé dynamique sur la feuille de calcul existante

Le code que nous avons utilisé ci-dessus crée un tableau croisé dynamique sur une nouvelle feuille de calcul, mais vous devez parfois insérer un tableau croisé dynamique dans une feuille de calcul déjà présente dans le classeur.

Dans le code ci-dessus (tableau croisé dynamique dans une nouvelle feuille de calcul), dans la partie où vous avez écrit le code pour insérer une nouvelle feuille de calcul, puis nommez-la. Veuillez apporter quelques modifications au code.

Ne t’inquiète pas; Je vais te montrer.

Vous devez d’abord spécifier la feuille de calcul (déjà dans le classeur) où vous souhaitez insérer votre tableau croisé dynamique.

Et pour cela, vous devez utiliser le code ci-dessous :

Au lieu d’insérer une nouvelle feuille de calcul, vous devez spécifier le nom de la feuille de calcul dans la variable PSheet.

Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets(“Data”)

Il y a un peu plus à faire. Le premier code que vous avez utilisé supprime la feuille de calcul du même nom (si elle existe) avant d’insérer le pivot.

Lorsque vous insérez un tableau croisé dynamique dans la feuille de calcul existante, il est possible que vous y ayez déjà un tableau croisé dynamique portant le même nom.

Ce que je dis, c’est que vous devez d’abord supprimer ce pivot.

Pour cela, vous devez ajouter le code qui doit supprimer le pivot du même nom de la feuille de calcul (s’il s’y trouve) avant d’en insérer un nouveau.

Voici le code que vous devez ajouter :

Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets(“Data”)
Worksheets("PivotTable").Activate
On Error Resume Next
ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Laissez-moi vous dire ce que fait ce code.

Tout d’abord, il définit simplement PSheet comme feuille de calcul dans laquelle vous souhaitez insérer le tableau croisé dynamique déjà dans votre classeur et définit les feuilles de calcul de données comme DSheet.

Après cela, il active la feuille de calcul et en supprime le tableau croisé dynamique « Tableau croisé dynamique des ventes ».

Important : si les noms des feuilles de calcul dans votre classeur diffèrent, vous pouvez les modifier à partir du code. J’ai mis en évidence le code où vous devez le faire.

À la fin,

En utilisant ce code, nous pouvons automatiser vos tableaux croisés dynamiques. Et la meilleure partie est qu’il s’agit d’une configuration unique ; après cela, nous avons juste besoin d’un clic pour créer un tableau croisé dynamique et vous pouvez gagner beaucoup de temps. Maintenant, dis-moi une chose.

Avez-vous déjà utilisé un code VBA pour créer un tableau croisé dynamique ?

S’il vous plaît partagez vos opinions avec moi dans la zone de commentaire; J’aimerais les partager avec vous et partager cette astuce avec vos amis.

Articles similaires :

• Ajouter ou supprimer le total général dans un tableau croisé dynamique dans Excel
• Ajouter le total cumulé dans un tableau croisé dynamique dans Excel
• Mettre à jour automatiquement un tableau croisé dynamique dans Excel
• Ajouter un champ et un élément calculés
• Supprimer un tableau croisé dynamique dans Excel