Tableau croisé dynamique en VBA

Version imprimable

Bonjour,

Je souhaite automatiser la mise en place d'un tableau croisé dynamique avec une Macro, j'ai pour habitude d'utiliser la fonction enregistrer une Macro pour avoir le gros du code avant de le décortiquer, mais pour le coup je sèche, il y a trop d'inconnus pour moi dans ce type de code :calim2:.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
Sub Macro2()
'
' Macro2 Macro
'
 
'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ventes aout!R1C1:R94C5", Version:=8).CreatePivotTable TableDestination:= _
        "Feuil2!R3C1", TableName:="Tableau croisé dynamique2", DefaultVersion:=8
    Sheets("Feuil2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique2")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").RepeatAllLabels _
        xlRepeatLabels
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("id_vdr")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "partenaire")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("contrat" _
        )
        .Orientation = xlColumnField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("com"), "Somme de com", _
        xlSum
End Sub

A vrai dire je bloque sur cette partie

Code:

1
2
3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ventes aout!R1C1:R94C5", Version:=8).CreatePivotTable TableDestination:= _
        "Feuil2!R3C1", TableName:="Tableau croisé dynamique2", DefaultVersion:=8

Le code doit être dynamique car la taille du tableau peut varier.

PS : Je ne veux pas juste du code tout fait quelques pistes et explications me conviendront parfaitement :)

17/08/2022, 13h48
mat955

Bonjour,

Si tu veux faire un TCD dont la source est dynamique, alors une bonne idée pourrait être de créer un tableau structuré pour y mettre tes données, et que ce tableau structuré deviennent la source de ton TCD.
Refais un enregistrement de macro après avoir fait cela, et VBE te montrera ainsi comment créer un TCD ayant une source dynamique.
17/08/2022, 14h18
Raccourcix

Bonjour
je confirme la suggestion de mat955, avec une source de données dans un tableau structuré, le TCD s'adaptera pour prendre en compte toutes les nouvelles données.

Il ne sera alors même plus nécessaire d'avoir du VBA, on construit le TCD au début et ensuite : clic droit, actualiser ou ALT+F5
Moins il y a de code, mieux c'est.

Stéphane
17/08/2022, 17h42
PetitBoutDeCode

Bonjour, et merci pour vos réponses rapides.

En fait le fichier peut être utilisé par plusieurs personnes au même moment, donc en suivant ce raisonnement, si un autre utilisateur actualise le tableau et qu'entre temps quelqu'un d'autre a analysé d'autres données, il devra recommencer, enfin si il n'est pas bloqué en lecture seule..?:aie:

Pour exposer le problème dans sa globalité, c'est un programme que je développe qui extrait les données avant de les insérer dans mon fichier qui contient la Macro (en lecture seule) pour l'enregistrer dans le répertoire de l'utilisateur, je fais ça pour que le fichier soit accessible à plusieurs personnes en même temps avec des données différentes.

A force de bidouiller j'ai fini par trouver ce qui n'allait pas,

Si jamais quelqu'un tombe sur ce sujet, voici le code qui permet de créer le TCD vierge

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    ws.Name = "nom"
    L = ws.Cells(Rows.Count, 1).End(xlUp).Row
    Sheets.Add
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.ActiveSheet
    ws2.Name = "Pivot"
    ws2.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "nom!R1C1:R" & L & "C5", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="Pivot!R3C1", TableName:= _
        "Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion10