Tableau croisé dynamique VBA | Étapes pour créer un tableau croisé dynamique dans VBA

Tableau croisé dynamique VBA Excel

Les tableaux croisés dynamiques sont au cœur de la synthèse du rapport d'une grande quantité de données. Nous pouvons également automatiser le processus de création d'un tableau croisé dynamique grâce au codage VBA. Ils sont une partie importante de tout rapport ou tableau de bord, dans Excel, il est facile de créer des tableaux avec un bouton, mais dans VBA, nous devons écrire des codes pour automatiser notre tableau croisé dynamique, avant Excel 2007 et ses anciennes versions en VBA, nous n'avions pas besoin de créer un cache pour les tableaux croisés dynamiques, mais dans Excel 2010 et ses versions plus récentes, des caches sont nécessaires.

VBA peut nous faire gagner beaucoup de temps sur notre lieu de travail, même si la maîtrise n'est pas si facile mais vaut la peine de passer du temps à l'apprendre. J'ai mis 6 mois pour comprendre le processus de création de tableaux croisés dynamiques via VBA. Vous savez ce que ces 6 mois ont fait des merveilles pour moi parce que j'ai fait tellement d'erreurs en essayant de créer le tableau croisé dynamique.

Mais la chose réelle est que j'ai appris de mes erreurs et maintenant j'écris cet article pour vous montrer comment créer des tableaux croisés dynamiques en utilisant du code.

D'un simple clic sur un bouton, nous pouvons créer des rapports.

Étapes pour créer un tableau croisé dynamique dans VBA

Vous pouvez télécharger ce modèle de tableau croisé dynamique VBA ici - Modèle de tableau croisé dynamique VBA

Pour créer un tableau croisé dynamique, il est important de disposer de données. Pour cela, j'ai créé des données factices, vous pouvez télécharger le classeur à suivre avec moi avec les mêmes données.

Étape 1: le tableau croisé dynamique est un objet pour référencer le tableau croisé dynamique et déclarer la variable en tant que tableaux croisés dynamiques.

Code:

 Sub PivotTable () Dim PTable As PivotTable End Sub

Étape 2: Avant de créer un tableau croisé dynamique, nous devons d'abord créer un cache pivot pour définir la source des données.

Dans le tableau croisé dynamique de la feuille de calcul ordinaire, sans nous déranger, nous créerons un cache pivot en arrière-plan. Mais dans VBA, nous devons créer.

Pour cela, définissez la variable un PivotCache.

Code:

 Dim PCache comme PivotCache

Étape 3: Pour déterminer la plage de données pivot, définissez la variable comme une plage.

Code:

 Dim PRange As Range

Étape 4: Pour insérer un tableau croisé dynamique, nous avons besoin d'une feuille distincte pour ajouter une feuille de calcul pour le tableau croisé dynamique déclarer la variable comme feuille de calcul.

Code:

 Dim PSheet comme feuille de calcul

Étape 5: De même, pour référencer les données contenant la feuille de calcul, déclarez une autre variable en tant que feuille de calcul.

Code:

 Dim DSheet comme feuille de calcul

Étape 6: Enfin, pour trouver la dernière ligne et colonne utilisée, définissez deux autres variables comme Long.

Code:

 Dim LR As Long Dim LC As Long

Étape 7: Nous devons maintenant insérer une nouvelle feuille pour créer un tableau croisé dynamique. Avant cela, s'il y a une feuille de pivotement, nous devons la supprimer.

Étape 8: définissez maintenant la variable d'objet PSheet et DSheet sur Pivot Sheet et Data Sheet respectivement.

Étape 9: Recherchez la dernière ligne et la dernière colonne utilisées dans la feuille de données.

Étape 10: définissez maintenant la plage de pivot en utilisant la dernière ligne et la dernière colonne.

Cela définira parfaitement la plage de données. Il sélectionnera automatiquement la plage de données même s'il y a un ajout ou une suppression de données dans la fiche technique.

Étape 11: Avant de créer un tableau croisé dynamique, nous devons créer un cache pivot. Définissez la variable de cache pivot à l'aide du code VBA ci-dessous.

Étape 12: Créez maintenant un tableau croisé dynamique vide.

Étape 13: Après avoir inséré le tableau croisé dynamique, nous devons d'abord insérer le champ de ligne. Je vais donc insérer le champ de ligne comme colonne de pays.

Remarque: téléchargez le classeur pour comprendre les colonnes de données.

Étape 14: Maintenant, un autre élément que je vais insérer dans le champ de ligne comme deuxième élément de position. J'insérerai le produit comme deuxième élément de ligne dans le champ de ligne.

Étape 15: Après avoir inséré les colonnes dans le champ de ligne, nous devons insérer des valeurs dans le champ de colonne. J'insérerai le «segment» dans le champ de la colonne.

Étape 16: Nous devons maintenant insérer des nombres dans le champ de données. Insérez donc «Ventes» dans le champ de données.

Étape 17: Nous en avons terminé avec la partie récapitulative du tableau croisé dynamique, nous devons maintenant formater le tableau. Pour formater le tableau croisé dynamique, utilisez le code ci-dessous.

Remarque: pour avoir plus de styles de tableau différents, enregistrez-les macro et obtenez les styles de tableau.

Pour afficher les éléments de valeurs classées par ligne sous forme de tableau, ajoutez le code ci-dessous en bas.

Ok, nous avons terminé si nous exécutons ce code en utilisant la touche F5 ou manuellement, nous devrions obtenir le tableau croisé dynamique comme celui-ci.

Comme cela, en utilisant le codage VBA, nous pouvons automatiser le processus de création d'un tableau croisé dynamique.

Pour votre information, j'ai donné le code ci-dessous.

 Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub