Créer un tableau croisé dynamique avec une plage dynamique, Excel VBA

Créer un tableau croisé dynamique avec une plage dynamique, Excel VBA

Voici un guide détaillé pour créer un tableau croisé dynamique avec une plage dynamique en VBA dans Excel. 

Étape 1 : Préparer les données 

Avant de créer un tableau croisé dynamique, assurez-vous que vos données sont organisées dans un format de tableau, avec des en-têtes dans la première ligne et des données cohérentes en dessous. 

Exemple de mise en page des données : 

Étape 2 : Définir une plage dynamique 

Une plage dynamique s’adapte automatiquement en fonction des données présentes dans votre feuille. En VBA, vous pouvez utiliser l’objet Range ou l’objet ListObject pour définir cette plage. 

Dans cet exemple, nous allons définir une plage dynamique en utilisant les propriétés End(xlDown) et End(xlToRight) pour déterminer la dernière ligne et colonne de données. 

Étape 3 : Créer un tableau croisé dynamique 

Une fois que la plage dynamique est définie, vous pouvez créer le tableau croisé dynamique. Nous utiliserons la méthode PivotTableWizard ou l’objet PivotTable pour cela. Ce dernier est plus moderne et offre plus de contrôle. 

Code VBA : 

Sub CreerTableauCroiseDynamique() 
    ' Étape 1 : Définir la feuille de données et la plage de données 
    Dim wsData As Worksheet 
    Set wsData = ThisWorkbook.Sheets("DataSheet") 
    ' Étape 2 : Définir une plage dynamique en fonction des données 
    Dim lastRow As Long 
    Dim lastCol As Long 
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row ' Dernière ligne de la colonne A 
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column ' Dernière colonne de la ligne 1 
    ' Définir la plage dynamique de A1 à la dernière ligne et colonne 
    Dim dataRange As Range 
    Set dataRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol)) 
    ' Étape 3 : Créer une nouvelle feuille pour le tableau croisé dynamique 
    Dim wsPivot As Worksheet 
    Set wsPivot = ThisWorkbook.Sheets.Add 
    wsPivot.Name = "PivotSheet" 
    ' Étape 4 : Créer le tableau croisé dynamique 
    Dim pivotTable As PivotTable 
    Set pivotTable = wsPivot.PivotTableWizard _ 
        (SourceType:=xlDatabase, SourceData:=dataRange, _ 
        TableDestination:=wsPivot.Cells(1, 1), _ 
        TableName:="SalesPivotTable") 
    ' Étape 5 : Personnaliser le tableau croisé dynamique (optionnel) 
    ' Exemple : Ajouter des champs dans les lignes, colonnes et valeurs 
    With pivotTable 
        .PivotFields("Produit").Orientation = xlRowField 
        .PivotFields("Produit").Position = 1 
        .PivotFields("Région").Orientation = xlColumnField 
        .PivotFields("Région").Position = 1 
        .PivotFields("Ventes").Orientation = xlDataField 
        .PivotFields("Ventes").Function = xlSum 
        .PivotFields("Ventes").NumberFormat = "#,##0" 
    End With 
    MsgBox "Tableau Croisé Dynamique Créé avec Succès!" 
End Sub

Explication du code : 

1. Préparer les données et définir la plage dynamique : 

• La feuille wsData est définie comme DataSheet, où les données brutes sont stockées. 
• Les variables lastRow et lastCol sont calculées pour trouver la dernière ligne et la dernière colonne dans les données. La plage dynamique est donc définie de A1 à la cellule correspondant à la dernière ligne et colonne de données. 

2. Créer un tableau croisé dynamique : 

• Une nouvelle feuille wsPivot est créée pour accueillir le tableau croisé dynamique. 
• La méthode PivotTableWizard est utilisée pour générer le tableau croisé dynamique à partir de la plage dynamique définie. 

3. Personnaliser le tableau croisé dynamique : 

• Les champs du tableau croisé dynamique sont configurés. Par exemple, le champ Produit est ajouté aux lignes, Région aux colonnes, et Ventes est utilisé comme champ de données avec la somme des ventes. 
• Le format des nombres pour le champ Ventes est défini pour afficher les chiffres avec des virgules. 

Exemple de résultat : 

Après l’exécution du code, une nouvelle feuille appelée PivotSheet sera créée, contenant un tableau croisé dynamique qui résume les ventes par produit et par région. 

Exemple de sortie : 

Remarques : 

• La plage dynamique s’ajuste automatiquement si vous ajoutez de nouvelles données dans la feuille DataSheet. 
• Vous pouvez personnaliser davantage le tableau croisé dynamique en ajoutant des filtres ou en modifiant les fonctions de synthèse (par exemple, Moyenne, Compte, etc.).