Récemment, j’ai eu dans un projet à mettre à jour des tableaux croisés dynamiques (TCD) avec une macro.

En général je préfère limiter l’utilisation de code VBA à l’automatisation d’opérations en boucle qui seraient fastidieuses à réaliser à la main. Aujourd’hui Excel permet de réaliser énormément de choses sans passer par du code, et cela évite de maintenir le code, car l’utilisateur du fichier Excel n’a pas forcément l’expérience ni le temps pour le faire.

Mais pour ce projet là, le cahier des charges était d’avoir une base de données créée de manière dynamique en fonction de colonnes de variables que l’utilisateur choisissait. Cette base est ensuite exploitée par un TCD pour des fins d’analyse.

Pour modifier le TCD de manière dynamique, j’ai utilisé les macro VBA ci-dessous.

Pour les utiliser dans votre code, copier-coller simplement les macros dans un module Visual Basic (Alt + F11).

Vous pouvez aussi télécharger le fichier d’exemple en fin d’article.

Créer un tableau croisé dynamique

Sub CreerTCD(Optional ByVal tcdNom As String = "Tableau croisé dynamique 1")
' Créer un tcd dans une feuille existante à partir d'une plage de cellules

Dim tcd As PivotTable, tcdCache As PivotCache
Dim sRngStart As String, sRngAddress As String

' Plage de la source de données
sRngAddress = ActiveSheet.Range("A1:R100").Address(ReferenceStyle:=xlR1C1, External:=True)

' Cellule où insérer le tcd
sRngStart = ActiveSheet.Range("T1").Address(ReferenceStyle:=xlR1C1, External:=True)

' Créer le cache du tcd
Set tcdCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=sRngAddress)

' Créer le tcd à partir du cache
Set tcd = tcdCache.CreatePivotTable( _
    TableDestination:=sRngStart, _
    TableName:=tcdNom)

End Sub

Ajouter un champ de tableau croisé dynamique

Sub AjouterChampTCD(ByVal sChamp As String, ByVal sZoneTcd As String, Optional ByVal pos As Variant = 1, Optional ByVal sNombreFormat As String = vbNullString, Optional ByVal tcdNom As String = vbNullString)
' Ajoute un champ au tcd dans la zone spécifiée: filtre, ligne, colonne ou valeur

Dim tcd As PivotTable, sTxtChamp As String

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

If tcd.PivotFields(sChamp).Orientation = xlHidden Then

    Select Case sZoneTcd
    
        ' Ajoute un champ en Filtre, Ligne, Colonne
        
        Case "Filtre"
        tcd.PivotFields(sChamp).Orientation = xlPageField
        
        Case "Colonne"
        tcd.PivotFields(sChamp).Orientation = xlColumnField
        
        Case "Ligne"
        tcd.PivotFields(sChamp).Orientation = xlRowField
        
        ' Ajoute un champ en Valeur
        
        Case "Somme"
        sTxtChamp = "Somme de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlSum
        
        Case "Nombre"
        sTxtChamp = "Nombre de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlCount
        
        Case "Moyenne"
        sTxtChamp = "Moyenne de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlAverage
        
        Case "Max"
        sTxtChamp = "Max de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlMax
        
        Case "Min"
        sTxtChamp = "Min de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlMin
        
        Case "Produit"
        sTxtChamp = "Produit de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlProduct
        
        Case Else
        ' par défaut
        
    End Select
    
End If

End Sub

Supprimer un champ d’un tableau croisé dynamique

Sub SupprimerChampTCD(ByVal sChamp As String, Optional ByVal tcdNom As String = vbNullString)
' Supprime un champ du tcd

Dim tcd As PivotTable

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

If tcd.PivotFields(sChamp).Orientation <> xlHidden Then
    tcd.PivotFields(sChamp).Orientation = xlHidden
End If

End Sub

Actualiser un tableau croisé dynamique

Sub ActualiserTCD(Optional ByVal tcdNom As String = vbNullString)
' Actualise un tcd

Dim tcd As PivotTable

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

' Actualise un seul tcd
tcd.PivotCache.Refresh
 
' Actualise tous les tcd
'ActiveWorkbook.RefreshAll
    
End Sub

Supprimer un tableau croisé dynamique

Sub SupprimerUnTCD(Optional ByVal tcdNom As String = vbNullString)
' Supprime un tcd
On Error GoTo Erreur

If tcdNom <> vbNullString Then
    ActiveSheet.PivotTables(tcdNom).TableRange2.Clear
Else
    ActiveSheet.PivotTables(1).TableRange2.Clear
End If

Exit Sub

Erreur:
    MsgBox "Le tableau croisé dynamique ''" & tcdNom & "'' n'a pas été trouvé.", vbExclamation, "Suppression annulée"
    
End Sub

Changer la source de données d’un tableau croisé dynamique

Sub ChangerSourceTCD(Optional ByVal tcdNom As String = vbNullString)
' Change la source de données d'un tcd à partir d'une plage de cellules

Dim tcd As PivotTable, SrcData As String

On Error GoTo Erreur
If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

' Détermine la nouvelle source de données du tcd
SrcData = ActiveSheet.Range("A1:R200").Address(ReferenceStyle:=xlR1C1, External:=True)

' Change la source de données
tcd.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
 SourceType:=xlDatabase, _
 SourceData:=SrcData)
Exit Sub
Erreur:
   MsgBox "Impossible de changer la source du TCD.", vbExclamation
End Sub

Regarder la vidéo

Voici une courte vidéo qui montre l’utilisation des macros VBA pour modifier un TCD.

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Modifier les tableaux croisés dynamiques avec VBA

Ce fichier Excel montre comment modifier un tableau croisé dynamique Excel à l'aide de macros VBA.

Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Dites-moi en commentaire s’il y a d’autres types de modifications de TCD que vous souhaiteriez automatiser (ou que vous réalisez) avec une macro VBA!