TCD à plage variable

Version imprimable

Bonjour tout le monde ,
Dans le cadre de mon entreprise , je suis chargé de développer une macro ayant pour but de réaliser un tableau de bord automatiquement.
Pour information le tableau de bord est basé sur une feuille de données brutes à lignes variables et colonnes fixes.Et une dizaine de TCD est à créer

Ne m'y connaissant pas trop en VBA . Je me suis débrouiller comme j'ai pu jusqu'à maintenant. J'ai fait beaucoup de recherches avant de poster ici , j'ai vu la plupart des sujets concernant le même problème mais cela n'a pas résolu mon problème.

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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
Sub Rapport()
 
 
' Détermination de la limite du tableau
 
Dim limite As Integer
 
limite = ([B2].End(xlDown).Row)
Range("A1:A" & limite).Select
 
Application.DisplayAlerts = False
 
'Mise en forme première page
Dim CL As Range
Application.ScreenUpdating = False
 
    Range("A1").CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Cells.Font.Size = 8
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.ColumnWidth = 16.29
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("G1:G" & limite).Select
 
    Range("A1").Select
Application.ScreenUpdating = True
 
 
'Renommage de la feuille récapitulative
 
 Worksheets("Feuil1").Name = "Tableau Récapitulatif"
 
'Création des feuilles additionelles
 
 
 Worksheets("Feuil2").Name = "Calculs occupation par TECH"
 
 Worksheets("Feuil3").Name = "Calculs occupation par CLIENT"
 
 Sheets.Add.Move After:=Sheets(Sheets.Count)
 Sheets(Sheets.Count).Name = "Répartition TECH par CLIENT"
 
 Sheets.Add.Move After:=Sheets(Sheets.Count)
 Sheets(Sheets.Count).Name = "Répartion TECH par TYPE PB"
 
 
 
    Sheets.Add
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Feuil1!R1C1:R963C14", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Feuil4!R3C1", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion10
    Sheets("Feuil4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Tps passée"). _
        Orientation = xlHidden
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "ID intervenant")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlRowField
        .Position = 2
    End With
    Range("B4").Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("B3").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").PivotFields("Tps passée"), _
        "Nombre de Tps passée", xlCount
    Range("C10").Select
End Sub

L'erreur se situe sur :

Code:

1
2
3
4
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Feuil1!R1C1:R963C14", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Feuil4!R3C1", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion10

'Erreur d'execution '5' : Argurment ou appel de procédure incorrect.
Et lorsque je n'ai pas cette erreur , j'ai l'erreur d’exécution 1004.
Deplus , l'exemple du dessus n'est pas pour une plage variable car après de multiples essais, j'ai voulu faire plus simple donc merci de m'indiquer aussi comment résoudre ce problème .

Merci d'avance

Bonjour,

il y a au moins un problème ici :
Code:

1 2 3 'Renommage de la feuille récapitulative Worksheets("Feuil1").Name = "Tableau Récapitulatif"
car après tu adresses Feuil1 qui n'est plus ainsi nommée.
Code:

1 2 3 4 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Feuil1!R1C1:R963C14", Version:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Feuil4!R3C1", TableName:="Tableau croisé dynamique1", _ DefaultVersion:=xlPivotTableVersion10
ce qui ne peut que faire... craaaaaaaaaaaac.

Pour ce qui est du tableau variable, je suppose que seul le nombre de lignes est variable et donc il faut faire varier R963.

Pour ce faire voici un exemple qui génère un TCD en considérant le nombre de lignes actives de la feuille source, qui est la feuille active.
Code:

1 2 3 4 5 6 7 8 9 10 11 Dim lrow As Long Dim xlrow As String 'Create Pivot Table by Categ' lrow = ActiveSheet.UsedRange.Rows.Count xlrow = lrow Application.CommandBars("PivotTable").Visible = True ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "SQL_Reporting_AGENTS!R1C1:R" & xlrow & "C16").CreatePivotTable TableDestination:="", _ TableName:="Pivot_Categ"
Bonne chance.
La génération dynamique de TCD est fastidieuse si on n'est pas très rigoureux.

:calim2:

Tout d'abord merci de m'avoir répondu ,
J'ai suivi tes remarques et la même erreur apparait au même endroit :
'Erreur d'execution '5' : Argurment ou appel de procédure incorrect.

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
'Renommage de la feuille récapitulative
 
 Worksheets("Feuil1").Name = "Tableau Récapitulatif"
 
'Création des feuilles additionelles
 
 
 Worksheets("Feuil2").Name = "Calculs occupation par TECH"
 
 Worksheets("Feuil3").Name = "Calculs occupation par CLIENT"
 
 Sheets.Add.Move After:=Sheets(Sheets.Count)
 Sheets(Sheets.Count).Name = "Répartition TECH par CLIENT"
 
 Sheets.Add.Move After:=Sheets(Sheets.Count)
 Sheets(Sheets.Count).Name = "Répartion TECH par TYPE PB"
 
 
 
    Sheets.Add
    Dim lrow        As Long
    Dim xlrow       As String
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Tableau Récapitulatif!R1C1:R" & xlrow & "C16", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Calculs occupation par TECH!R3C1", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion10
    Sheets("Calculs occupation par TECH").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Tps passée"). _
        Orientation = xlHidden
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "ID intervenant")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlRowField
        .Position = 2
    End With
    Range("B4").Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
        "Tps passée")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("B3").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").PivotFields("Tps passée"), _
        "Nombre de Tps passée", xlCount
    Range("C10").Select
End Sub

Dans: Tableau Récapitulatif!R1C1:R" & xlrow & "C16"
A quoi correspond le C16 ?

Merci

24/05/2012, 13h05
Godzestla

Citation:

'ai suivi tes remarques

Non, ce n'est pas vrai.

Je ne comprends pas , je fais bien appel aux bonnes feuilles correctement nommées.
Edit : Autant pour moi , j'ai oublié les lignes d'attributions des variables
Edit²: Je ne sais pas si tu parlais de ça , mais cela ne résout pas cette erreur.
Edit3: J'ai voulu essayer avec ton code
Code:

1 2 3 4 5 6 7 8 9 Dim lrow As Long Dim xlrow As String lrow = ActiveSheet.UsedRange.Rows.Count xlrow = lrow Application.CommandBars("PivotTable").Visible = True ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Tableau Récapitulatif!R1C1:R" & xlrow & "C16").CreatePivotTable TableDestination:="'Calculs occupation par CLIENT'!R1C1", _ TableName:="Pivot_Categ"
Et la j'ai une erreur d’exécution 1004