Créer et modifier dynamiquement un tableau croisé dynamique (TCD), Excel VBA

Créer et modifier dynamiquement un tableau croisé dynamique (TCD), Excel VBA
Voici une explication détaillée du code VBA qui crée et modifie dynamiquement un tableau croisé dynamique (TCD) dans Excel, avec un exemple complet et des commentaires.
Explication du Code
Un tableau croisé dynamique (TCD) dans Excel est un outil puissant pour résumer, analyser et explorer de grandes quantités de données. Grâce à VBA, nous pouvons automatiser la création et la modification de ces TCD.
Voici les étapes couvertes dans cet exemple de code VBA :
1. Définir la plage de données : Spécifier la plage des données à utiliser pour le TCD.
2. Créer un TCD : Créer un tableau croisé dynamique à partir des données spécifiées.
3. Modifier le TCD : Ajouter ou supprimer des champs dynamiquement dans le TCD.
4. Actualiser le TCD : Mettre à jour le TCD lorsque les données changent.
Nous utiliserons les méthodes PivotTableWizard ou PivotTable.Add, qui nous permettent de contrôler la structure du TCD, notamment où placer les champs (lignes, colonnes, valeurs, filtres).
Code VBA pour Créer et Modifier Dynamiquement un TCD

Sub CreateAndModifyPivotTable()
    ' Déclaration des variables nécessaires
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range
    Dim pivotRange As Range
    Dim lastRow As Long
    Dim lastCol As Long
    ' Définir la feuille contenant les données
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    ' Trouver la dernière ligne et colonne des données
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    ' Définir la plage des données (en supposant que les données commencent en A1)
    Set dataRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol))
    ' Créer une nouvelle feuille pour le TCD (si elle n'existe pas déjà)
    On Error Resume Next
    Set wsPivot = ThisWorkbook.Worksheets("PivotSheet")
    On Error GoTo 0
    If wsPivot Is Nothing Then
        Set wsPivot = ThisWorkbook.Worksheets.Add
        wsPivot.Name = "PivotSheet"
    End If
    ' Effacer tout contenu existant dans la feuille PivotSheet
    wsPivot.Cells.Clear
    ' Créer un cache de Pivot Table à partir de la plage de données
    Set ptCache = ThisWorkbook.PivotTableWizard(dataRange)
    ' Créer un nouveau TCD à partir du cache
    Set pt = wsPivot.PivotTableWizard(SourceType:=xlDatabase, SourceData:=dataRange)
    ' Positionner le TCD à la cellule A1 dans la feuille PivotSheet
    Set pivotRange = wsPivot.Range("A1")
    pt.TableRange2.Cut Destination:=pivotRange
    ' Modifier le TCD : Ajout des champs
    With pt
        ' Ajouter 'Product' aux lignes
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Product").Position = 1
        ' Ajouter 'Region' aux colonnes
        .PivotFields("Region").Orientation = xlColumnField
        .PivotFields("Region").Position = 1
        ' Ajouter 'Sales' aux valeurs (somme des ventes)
        .PivotFields("Sales").Orientation = xlDataField
        .PivotFields("Sales").Function = xlSum
        .PivotFields("Sales").NumberFormat = "#,##0"
        ' Ajouter 'Date' en filtre (Page Filter)
        .PivotFields("Date").Orientation = xlPageField
        .PivotFields("Date").Position = 1
    End With
    ' Actualiser le TCD pour appliquer les changements
    pt.RefreshTable
    ' Formatage du TCD pour améliorer la lisibilité
    With pt.TableRange1
        .Font.Size = 10
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    ' Facultatif : Ajuster automatiquement la largeur des colonnes
    wsPivot.Columns.AutoFit
    ' Afficher un message pour informer l'utilisateur que le TCD a été créé
    MsgBox "Le Tableau Croisé Dynamique a été créé et modifié avec succès !", vbInformation
End Sub

Explication du Code
1. Déclaration des Variables :

• wsData : Cette variable représente la feuille de calcul contenant les données à partir desquelles le TCD sera créé.
• wsPivot : Cette variable représente la feuille où le TCD sera créé.
• ptCache : Un cache pour le tableau croisé dynamique.
• pt : Un objet PivotTable.
• dataRange : La plage de données qui sera utilisée pour le TCD.
• pivotRange : La plage où le TCD sera placé.
• lastRow et lastCol : Déterminent la dernière ligne et la dernière colonne de la plage de données.

2. Définir la Plage de Données :

• lastRow et lastCol permettent de trouver la taille de la plage de données.
• dataRange est définie en fonction de ces valeurs (en partant de la cellule A1).

3. Créer la Feuille Pivot :

• Si la feuille PivotSheet existe déjà, elle est réutilisée. Si non, une nouvelle feuille est créée.
• Le contenu de la feuille PivotSheet est effacé avant de créer un nouveau TCD.

4. Créer le TCD :

• Un cache de TCD est créé à partir de la plage de données (ptCache).
• Le TCD est ensuite généré en utilisant la méthode PivotTableWizard.

5. Modifier le TCD :

• • Les champs sont ajoutés au TCD :
    • Lignes : Le champ « Product » est ajouté à la section des lignes.
    • Colonnes : Le champ « Region » est ajouté à la section des colonnes.
    • Valeurs : Le champ « Sales » est ajouté à la section des données (les ventes sont sommées).
    • Filtres : Le champ « Date » est ajouté à la section des filtres (Page Field).

6. Actualiser le TCD :

• Après avoir modifié le TCD, la méthode RefreshTable est utilisée pour appliquer les changements.

7. Formatage :

• La police du TCD est modifiée (taille et police) pour améliorer la lisibilité.
• La largeur des colonnes est ajustée automatiquement avec AutoFit.

8. Message de Confirmation :

• Un message apparaît pour informer l’utilisateur que le TCD a été créé et modifié avec succès.

Modifications supplémentaires possibles :

• Changer l’agrégation : Vous pouvez changer l’agrégation des données dans les valeurs en remplaçant Function = xlSum. Par exemple, vous pouvez utiliser xlAverage pour obtenir la moyenne des données.
• Ajouter plus de filtres : Vous pouvez ajouter davantage de filtres en ajoutant d’autres champs à la section PageField.
• Plage dynamique : Vous pouvez rendre la plage de données dynamique en utilisant TableRange ou même interroger une plage nommée si vos données changent fréquemment.

Conclusion
Ce code VBA montre comment créer et modifier dynamiquement un tableau croisé dynamique dans Excel. Vous pouvez adapter la structure et l’apparence du TCD selon vos besoins en modifiant les champs et les options du TCD. Ce processus vous permettra de créer des TCD interactifs et adaptés à vos données.