[XL-2007] Changer la source de données dans plusieurs TCD [Résolu]

[yeahna]

Bnjour tout le monde,

Je travaille avec un fichier dans lequel j'ai une vingtaine de de TCD.
Chaque mois, ma source de données change et je dois donc faire un update des TCD une par une.

Je me doute qu'il y a moyen de faire cela à l'aide d'une macro, mais je ne sais absolument pas coder cela.

Pourriez-vous m'aider svp?? (et me faire gagner des heures entières chaque mois )

Merci mille fois,

Cordialement

Jenna

[Daniel.C]

Bonjour,

Le problème, c'est que ça dépend de l'emplacement de la plage de données. Pour mes tests, j'ai fait un exemple simple avec des données en colonne A et B de Feuil1. Copie cette macro dans le module de la feuille contenant les données source. J'ai fait les tests avec XL2010, aussi il faudra peut-être changer "xlPivotTableVersion14" en "xlPivotTableVersion12" :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PlageSource As Range, Sh As Worksheet, Pt As PivotTable
    With Sheets("Feuil1")
        Set PlageSource = .Range(.[A1], .Cells(.Rows.Count, 2).End(xlUp))
        If Not Intersect(PlageSource, Target) Is Nothing Or _
        (PlageSource.Cells.Rows.Count + 1 = Target.Row And _
        Target.Column <= PlageSource.Cells.Columns.Count) Then
            Application.EnableEvents = False
            Set PlageSource = .Range(.[A1], .Cells(.Rows.Count, 2).End(xlUp))
            For Each Sh In Worksheets
                For Each Pt In Sh.PivotTables
                    Pt.ChangePivotCache _
                        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                        "Feuil1!" & PlageSource.Address, Version:=xlPivotTableVersion14)
                    Pt.RefreshTable
                Next Pt
            Next Sh
            Application.EnableEvents = True
        End If
    End With
End Sub

[yeahna]

Bonjour Daniel,

J'ai essayé ton code, il a partiellement fonctionné, ça a changé les deux premières colonnes dans mon TCD, (tout le reste était blanc vu que ça n'a pas tenu compte des autres colonnes je suppose). Malheureusement excel a planté et depuis, j'ai beau essayer de nouveau maintenant plus moyen de faire fonctionner.

Voici un fichier excel, un exemple très simplifié de ce que j'utilise en général, çàd:
- Une feuille de donnée que j'actualise chaque avec un nombre variable de lignes en plus (le nombre de colonne restant toujours le même)
- Plusieurs autres feuilles contenant chacune plusieurs TCD.

Les feuilles que j'appelle "current 1 et 2" sont les feuilles avant que je n'ajoute mes données.

Les feuilles after: sont en fait celle que je voudrais avoir automatiquement. C'est à dire, lorsque je rajoute les données en gras ( c'est à dire mes données mensuelles qui ne sont pas toujours un nombre fixe de lignes), j'aimerais que mes TCD changent automatiquement pour me donner ce résultat sans que je ne doive à chaque fois aller sur chaque TCD, changer la source de donnés pour chacun d'eux, etc...

Est-ce que je suis assez claire dans mes explications??

Merci de ton aide,

Bien à toi,

Jenna

[Daniel.C]

Je ne suis pas sûr de bien comprendre :

Les feuilles after: sont en fait celle que je voudrais avoir automatiquement. C'est à dire, lorsque je rajoute les données en gras

Est-ce qu'il ne faut tenir compte que des données ajoutées en gras ? Le tableau de données sert-il à alimenter tous les TCD et la plage source est-elle la même pour tous les TCD (même nombre de colonnes par exemple ? Je pourrais vérifier moi-même, mais, si tu as la réponse, on gagnera du temps.

[yeahna]

J'ai mis les tables after 1 et 2 juste pour donner un aperçu de ce que je voulais, mais en temps normal, mon fichier se composerait des data, current 1 et current 2.

Est-ce qu'il ne faut tenir compte que des données ajoutées en gras ?

Pour les tables after 1 et 2 il faut tenir compte de toutes les données de la feuille data: les anciennes données + les nouvelles données. (j'ai mis du gras afin de distinguer les anciennes, des nouvelles données).

Le tableau de données sert-il à alimenter tous les TCD et la plage source est-elle la même pour tous les TCD (même nombre de colonnes par exemple ?

Le tableau de données sert en effet à alimenter tous les TCD et la plage source est la même pour tous les TCD.

Lorsque je change ma feuille data en faisant une mise à jour des données, j'ajoute des lignes, mais le nombre de colonnes reste le même.
Et les TCD se baseront toutes sur la nouvelle plage de donnée.

Est-ce plus claire?

[Daniel.C]

Mets la macro suivante dans le module de la feuille "data" :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PlageSource As Range, Sh As Worksheet, Pt As PivotTable
    With Sheets("data")
        Set PlageSource = .Range(.[A1], .Cells(.Rows.Count, 6).End(xlUp))
        If Not Intersect(PlageSource, Target) Is Nothing Or _
        (PlageSource.Cells.Rows.Count + 1 = Target.Row And _
        Target.Column <= PlageSource.Cells.Columns.Count) Then
            Application.EnableEvents = False
            Set PlageSource = .Range(.[A1], .Cells(.Rows.Count, 6).End(xlUp))
            For Each Sh In Worksheets
                For Each Pt In Sh.PivotTables
                    Pt.ChangePivotCache _
                        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                        "data!" & PlageSource.Address, Version:=xlPivotTableVersion14)
                    Pt.RefreshTable
                Next Pt
            Next Sh
            Application.EnableEvents = True
        End If
    End With
End Sub

[yeahna]

Je vais essayer cela merci
Encore juste une petite question, si jamais j'ai plus de colonnes il suffit de changer le '6'?

Bien à toi,

Jenna

[Daniel.C]

Exact.

[yeahna]

Merci mille fois Daniel. Au fait, encore une petite question, (hum oui j'ai l'art de faire des fichiers excel compliqués) Si dans mon fichier, j'ai des TCD qui se bases sur d'autres sources de données que la feuille data, mais dans le même fichier. Je suppose que cela posera problème pour la macro.
Y a t il un moyen simple de résoudre cela?

Encore merci merci merci!!

Jenna

[Daniel.C]

Il faut que je connaisse le nom des feuilles contenant les datas.

[yeahna]

MT price

(à préciser, cette feuille sera aussi à mettre à jour et entraînera la mise à jour automatique des TCDs qui lui sont liés--> traitement identique que data)

[Daniel.C]

Je vais te mettre à contribution, car ça devient complexe. Donc tu vas définir pour chaque source de données une plage dynamique. Avec ton exemple, la plage de la feuille "data sera définie par le nom :

Code :

=DECALER('data'!$A$1:$F$1;;;NBVAL('data'!$A:$A))

Tu pourrais aussi utiliser les nouveaux tableaux XL2007 (Insertion, tableau).

Une fois que tu as défini les plages sources, tu cliques dans un tableau, sur l'onglet "Options", sur "changer la source de données" et tu remplaces la plage par le nom que tu viens de définir. Ces plages sont dites dynamiques car elles se dimensionnent selon que tu ajoutes ou retranches des données. Dans le module "thisworkbook", ajoute :

Code :

1
2
3
4
5
6
7
8
9
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Plage As String, Pt As PivotTable
    Application.EnableEvents = False
    If Sh.Name = "MT Price" Or Sh.Name = "data" Then
        ThisWorkbook.RefreshAll
    End If
    Application.EnableEvents = True
 
End Sub

[Daniel.C]

Bizarrement, ta dernière réponse n'apparaît pas. Peux-tu mettre ton classeur en pièce jointe ?