Macro vba
Résolu
Bonjour, je cree tableau croisé dynamique à partir de ma base de données et je voudrais trouver une macro pour automatiser la mise à jour de mon tableau croisé dynamique quand ma base de données est mise à jour.comment faire??
Merci par avance
Merci par avance
41 réponses
Résumé de la discussion
Le problème concerne l’automatisation de la mise à jour d’un tableau croisé dynamique dès que la base de données est actualisée, via une macro. Les solutions évoquées privilégient une source de données dynamique par plage nommée, réalisée avec OFFSET (DECALER) et NBVAL, pour que le TCD s’étende automatiquement avec les nouvelles lignes. Des difficultés techniques apparaissent, notamment une erreur d’exécution 1004 liée à la définition de la plage et à l’adresse renvoyée, et des questions sur l’unicité des noms de champs et l’emplacement de la source (feuille Base). Les échanges insistent sur l’importance de définir une plage dynamique fiable et de vérifier les conditions d’extension, en particulier les noms de champs uniques et le découpage correct de la source.
-
Bonjour,
lorsque je dis quand ma base de données est mise à jour cela signifie que chaque fin de mois, je réactualise manuellement ma base de données avec de nouvelles données de chaque fin de mois c'est à dire décembre janvier février etc merci pour votre réponse. -
Bonjour,
En principe pas besoin de macro le TCD se mais à jour automatiquement.
Précisez ce que vous entendez par : quand ma base de données est mise à jour ?
-
Bonjour,
Je pense que lorsque Le Pingou vous demandait : " Précisez ce que vous entendez par : quand ma base de données est mise à jour ? " la question portait sur la base de données, plus que sur la mise à jour.
De quelle base de données s'agit-il ? Est-ce une base Access avec une extraction via une requête et transfert sur Excel pour traitement par TCD Excel, ou bien d'un tableau Excel que vous appelez Base de données ? Les réponses de LePingou seront différentes parce que les traitements des macros seront différents. Cordialement. -
Bonjour,
Merci pour les informations, cependant elles ne me permettent dans faire plus.
Désolé.
-
Bonjour,
quand je dis base de données je parle d'un tableau sous excel que j'ai crée avec des données et que je réactualise manuellement chaque fin de mois avec de nouvelles données. merci pour votre réponse -
Bonjour,
Merci.
Lors de la mise à jour le tableau est de même dimension (nombre de ligne et colonne identique) le TDC se met à jour automatiquement.
Dans le cas où le nombre de lignes varie (en plus ou moins) utiliser le TCD avec une plage nommée dynamique, par exemple :
Dans gestionnaire de noms / Nouveau nom et sous:
Nom : MonTableau
Fait référence à : =DECALER($A$1;;;NBVAL($A:$A);NBVA($1:$1))
Ensuite créer votre TCD avec la plage MonTableau : si vous rajoutez/supprimez des lignes/colonnes elles seront actualisées.
-
bonjour à vous!
J'ai essayé de créer un document avec un petit échantillon et des données voici le lien https://www.cjoint.com/?0AbwmOSGtT9 . J'aimerais créer une petite macro (une fois que mon tableau croisé dynamique avec 8 colonnes: types de produit, objet, devise, entité, septembre,variation septembre octobre, octobre, variation octobre, novembre, novembre etc est créer) me permettant d'automatiser la mise à jour de mon tableau croisé dynamique quand ma base de données est mise à jour( c'est à dire au fur à mesure que j'ai les données de décembre,janvier, février etc la petite macro que j'aurai créer me permettrait d'actualiser automatiquement mon tableau croisé dynamique tout en tenant compte des nouvelles données sur les mois à venir ( décembre, janvier, février, mars, etc) que j'aurai rajouté sur ma base de données de telles sorte qu'on actualisant mon TCD qu'en plus des données déjà existantes( variation septembre oct... etc) que j'ai les nouvelles données c'est à dire variation novembre décembre, décembre, variation décembre janvier, janvier etc...) Avez vous svp une idée de comment faire ma macro? Merci d'avance pour votre réponse. -
Bonjour,
Merci pour le fichier.
Pourquoi n'avez-vous pas mis le TCD correspondant aux 8 colonnes comme base de départ ?
Remarque, je pense qu'il faut tenir compte des différents type de produit et non pas uniquement [bonbon]
Salutations.
Le Pingou-
Bonjour,
merci pour votre réponse mais je n'ai pas trop compris votre question. Je sais faire une macro avec l'enregistreur macro mais le problème c'est que ma macro me permet de refaire exactement le TCD de base alors que c'est pas mon objectif. Mon objectif est de faire une macro me permettant d'actualiser mon TCD lorque ma bases de données contient de nouvelles données. Merci de votre réponse.
-
-
Bonjour,
mais je n'ai pas trop compris votre question
Eh bien sans votre TCD (existant) il n'est pas possible de créer une macro qui y corresponde et de plus selon sa configuration la macro n'est pas nécessaire.
En plus les valeurs du nouveau mois doivent-t-elles être ajouté au TCD ou prendre la place du dernier mois (exemple pour novembre : septembre, variation sept/oct, octobre, variation oct/nov
Devient à la place : octobre, variation oct/oct, novembre, variation nov/dec)
Ou en plus : septembre, variation sept/oct, octobre, variation oct/nov, novembre, variation nov/dec
Si vous avez déjà une macro, alors mettre une copie du code.
Salutations.
Le Pingou-
bonjour,
voici ma macro, mais j'ai procédé avec l'enregistreur macro, à mon avis des simplifications sont à faire en plus de trouver la bonne macro permettant d'actualiser mon TCD au fur et à mesure que ma base de données est mise à jour avec de nouvelles données
' Macro enregistrée le 03/01/2013 par jacinthelafleur
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base '!R4C2:R77C15").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
Array("type de produit", "Objet", "Devise", "Entité", "Données")
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Sept YTD")
.Orientation = xlDataField
.Caption = " Sept YTD"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Variation sept/oct €")
.Orientation = xlDataField
.Caption = " Variation sept/oct €"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Oct YTD" _
)
.Orientation = xlDataField
.Caption = " Oct YTD"
.Position = 3
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Variation oct/nov €")
.Orientation = xlDataField
.Caption = " Variation oct/nov €"
.Position = 4
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Nov YTD" _
)
.Orientation = xlDataField
.Caption = " Nov YTD"
End With
Range("E3").Select
Range("E3").Select
Range("E3").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
Range("E5:I73").Select
ActiveWindow.SmallScroll Down:=48
Range("E5:I109").Select
Range("E109").Activate
Selection.NumberFormat = "0.0000000"
Selection.NumberFormat = "0.000000"
Selection.NumberFormat = "0.00000"
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
ActiveWindow.SmallScroll Down:=-87
Selection.NumberFormat = "0.00"
ActiveWindow.SmallScroll Down:=-96
Range("J6").Select
ActiveWindow.SmallScroll Down:=-9
Rows("4:4").Select
Selection.Font.Bold = True
Rows("9:9").Select
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=27
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect _
"'type de produit'[All;Total]", xlDataAndLabel, True
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=-9
Rows("26:26").Select
Selection.Font.Bold = True
Rows("30:30").Select
Selection.Font.Bold = True
Rows("38:38").Select
Selection.Font.Bold = True
Rows("48:48").Select
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=30
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect _
"Objet[All;Total]", xlDataAndLabel, True
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=-60
Rows("4:4").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Rows("4:4").Interior.ColorIndex = xlNone
Range("A4:I4").Select
Range("I4").Activate
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Rows("4:4").Select
Selection.Font.ColorIndex = 2
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect _
"'type de produit'[All;Total]", xlDataAndLabel, True
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect _
"Objet[All;Total]", xlDataAndLabel, True
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
ActiveWindow.SmallScroll Down:=63
End Sub
-
-
Bonjour,
Merci pour le code enregistré.
Et pourquoi pas de réponse à :
En plus les valeurs du nouveau mois doivent-t-elles être ajouté au TCD ou prendre la place du dernier mois (exemple pour novembre : septembre, variation sept/oct, octobre, variation oct/nov
Devient à la place : octobre, variation oct/oct, novembre, variation nov/dec)
Ou en plus : septembre, variation sept/oct, octobre, variation oct/nov, novembre, variation nov/dec
Toutefois il y vraiment dommage que vous n'avez pas mis la macro existante !
-
Bonjour, "Toutefois il y vraiment dommage que vous n'avez pas mis la macro existante !" vous voulez plutôt dire " le TCD existant "?
En ce qui concerne les valeurs du nouveau mois effectivement elles doivent être ajoutées au TCD. Vous voulez que je vous envoie le TCD déjà existant?
Mercii pour votre réponse. -
voici une macro que j'ai crée en plus simple.
Sub Macro2()
'
' Macro2 Macro
' '
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base '!R4C2:R77C15").CreatePivotTable TableDestination:="", TableName:= _
"Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddFields RowFields:= _
Array("type de produit", "Objet", "Devise", "Entité", "Données")
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Sept YTD")
.Orientation = xlDataField
.Caption = " Sept YTD"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Variation sept/oct €")
.Orientation = xlDataField
.Caption = " Variation sept/oct €"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Oct YTD" _
)
.Orientation = xlDataField
.Caption = " Oct YTD"
.Position = 3
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Variation oct/nov €")
.Orientation = xlDataField
.Caption = " Variation oct/nov €"
.Position = 4
.Function = xlSum
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Nov YTD" _
)
.Orientation = xlDataField
.Caption = " Nov YTD"
End With
Range("E3").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
Range("E5:I109").NumberFormat = "0.00"
ActiveWindow.SmallScroll Down:=78
Range("F112").Select
ActiveWindow.SmallScroll Down:=-93
End Sub
cela ne règle pas mon problème car il me reproduit mon TCD existant. Merci pour votre réponse et aide.
-
-
moi ousi la meme a vair
-
Bonjour,
Merci pour les informations, cette fois c'est clair.
En résumé le TCD sera chaque mois augmenté de 2 colonnes... !
Une réponse va suivre.
-
Bonjour,
Proposition basée sur le TCD existant avec au minimum les valeurs pour 1 mois (mois YTD et Variation mois/mois+1). Le nombre de ligne est fixé à 77.
J'ai laissé la source de données sur plage [Base!$B$4:$J$77] comme cela vous pouvez faire un premier essai directement.
Le fichier : https://www.cjoint.com/?3AepGIGqtsM
-
merci beaucoup pour votre aide précieuse.
Mais quand j'ouvre le fichier que vous m'avez envoyé et que j'essaie juste de faire tourner la macro pour voir si elle fonctionne, je me rends compte qu'elle ne marche pas. Je vais sur deébogage alors pour voir d'où provient l'erreur, et là je m'aperçois que le problème vient de la ligne suivante Tcd.SourceData=adrnv vu que celle-ci est souligner en jaune.Que dois-je faire? Merci d'avance pou votre réponse. -
Re bonsoir, je vous remercie de votre aide.
Lorsque j'ai testé la macro sur le fichier que vous m'avez envoyé, sur une version excel 2003 celle-ci n'a pas fonctionné,
finalement en arrivant chez moi, j'ai testé la macro sur le même fichier et celle-ci marche parfaitement. Pensez vous que cela est du au changement de version excel? Si oui comment svp faire pour adapter ma macro à la version 2003?
Ensuite j'applique le code de la macro à mon vrai fichier initial sous excel version 2007 celle-ci fonctionne mais quand je rajoute des données sur ma base de donnée c'est à dire variation nov/dec, dec, et que je fais tourner ma macro celle-ci ne réactualise pas mon TCD avec les nouvelles données et un message apparaît "pas de changement de la taille de la source de données".
Voici la macro que j'ai écris en me servant de la votre.
Sub macro1()
Dim Recap As PivotTable
Dim adrsd As String, adrnv
Dim nbcodo As Integer, decosdac, decosdnv, c
Application.ScreenUpdating = False
Sheets("Recap").Select
Set Recap = Sheets("Recap").PivotTables(1)
' nombre colonne de données
nbcodo = Recap.DataLabelRange.Count
' dernière colonne base données en cours
adrsd = Recap.SourceData
decosdac = CInt(Mid(adrsd, InStr(InStr(1, adrsd, ":"), adrsd, "C") + 1, 4))
' dernière colonne base données en cours
decosdnv = Sheets("Recap").Cells(4, Columns.Count).End(xlToLeft).Column
If decosdnv > decosdac Then ' nouvelle base de la source de données
adrnv = Sheets("Base").Name & "!" & Range(Cells(4, 2), Cells(77, decosdnv)).Address(ReferenceStyle:=xlR1C1)
Recap.SourceData = adrnv
' Recap.Refresh
For c = 1 To decosdnv - decosdac
With Recap.PivotFields(Sheets("Base").Cells(4, decosdac + c).Value)
.Orientation = xlDataField
.Caption = " " & Sheets("Base").Cells(4, decosdac + c).Value
' .Position = 3
.Function = xlSum
End With
Next c
MsgBox "La mise à jour du TCD est terminée."
Else
MsgBox "Pas de changement de la taille de la source de données...!"
End If
Set Recap = Nothing
Range("a1").Select
Application.ScreenUpdating = True
End Sub
J'ai juste remplacé MTCD par Recap qui est le nom de mon tcd existant. Ai-je fais une erreur?
Merci d'avance pour votre réponse et de votre aide précieuse.
-
-
Bonjour,
C'est étonnant, le code fonctionne correctement chez moi.
Il serait bien de connaitre le message d'erreur ... !
Vous pouvez aussi faire comme suit, clic sur le bouton de mise à jour et sitôt le message d'erreur vous fates une copie d'écran ensuite sur débogage et nouvel copie d'écran et mettre les 2 sur un document Word que vous mettez sur https://www.cjoint.com/ et poster le lien.
J'attends...
-
-
Bonjour,
Je ne peux pas dire que cela provient de la version 2003, car vous ne m'avez pas fourni ce que j'avais demandé, c'est dommage pour vous.
Je prendrai la peine de regarder le code de votre message mais pas de suite.
Salutations.
Le Pingou -
Bonjour,
A première vue, ce n'est vraiment pas bien de mettre un nom de variable (Recap ---- Tcd) qui porte le même nom qu'une feuille [Sheets("Recap")], je n'ai pas testé mais je pense que vous aurez des problèmes.
Encore une fois, la réponse à ma demande ......poste 23 et rappel poste 26... !
Bon week-end.
-
-
bonjour,
je vous envoie le fichier https://www.cjoint.com/?3AgoqV0InLx
que j'ai créer pour que vous puissiez détecter où se trouve éventuellement mon erreur sur ma macro. Merci d'avance pour votre aide.
Bonne après midi -
-
-
-
-
Bonjour,
j'ai effectué les modifications sur le fichier suivant https://www.cjoint.com/?3AgrknbrTzK mais il y a toujours des erreurs sur ma macro merci d'avance pour votre aide. -
-
-
Bonjour,
Ce n'est pas forcément sur cette ligne surlignée en jaune,
Pourquoi vous avez un avertissement de sécurité ... dans votre classeur juste au-dessous du ruban... ?
Note : contrôler l'exactitude du nom de la feuille (Base)... !
Salutations.
Le Pingou -
Bonjour,
Eh bien en regardant plus attentivement je constate que vous travaillez avec un tableau de données sur la feuille [Base] et que tous les titres de colonne de [type de produit, Objet, ... Sept YTD, Variation ....,janv YTD] et suivi par un autre titre différent de valeur du mois ; c'est-à-dire que cela ne correspond pas à votre l'exemple car il y a encore d'autre colonne qui suivent ce qui va donner un résultat incohérent dans le TCD car vous vouliez la mise à jour des valeurs qui concerne le mois et la variation mois/mois+1.
Ce qui n'est pas prévu dans mon code .... ! Que faire... !.
-
C'est vrai je suis vraiment désolé je pensais avoir mis le bon exemple avec la bonne explication apparemment ce n'est pas le cas... Pouvez vous toujours m'aider s'il vous plait ? Encore désolé de ne pas avoir été plus clair dans mes explications. Je suis vraiment débutante dans le domaine. Merci d'avance et bonne soirée.
-
Par contre en regardant de plus près ma capture d'écran je m'aperçois qu'après la colonne janvier YTD, on voit à nouveau la colonne type de produit etc ceci n'est pas à prendre en compte dans la macro car au final j'aimerais juste que ma macro me permettent d'actualiser mon TCD quand la base de données est renseignée par de nouvelles données des colonnes variations nov dec, dec YTD, variation dec Janv, janv YTD,variation janv fevrier, fevrier YTD, je ne sais pas mes explications sont-elles claires? Merci d'avance.
-
- 1
- 2
- 3
Suivant
