Créer un TCD à partir d'une base de données évolutive

ti_mouton Messages postés 153 Statut Membre -
ti_mouton Messages postés 153 Statut Membre - 5 janv. 2017 à 14:55

Bonjour,

Je cherche un code VBA qui me permette de créer un TCD à partir d'une base de données qui est évolutive (le nombre de ligne et de colonne n'est pas fixe). Ma base de données est en feuille 1 et commence en cellule B6. Mon TCD doit être créé en feuille 2 en cellule B3.

J'ai trouvé sur différents forum des tuto pour créer un TCD à partir d'une base fixe, mais je n'ai pas assez de connaissance en VBA pour l'adapter à une base évolutive.

Merci pour votre aide !

Afficher la suite

3 réponses

Frenchie83 Messages postés 2254 Statut Membre 339

Bonsoir
Voici une idée
https://www.cjoint.com/c/EGwrc1VVWdw
Le principe, chaque fois que vous cliquerez sur le bouton "Créer TCD", le programme supprime le TCD existant(si il existe) et en créer un nouveau.
A adapter à votre fichier
Cdlt

ti_mouton Messages postés 153 Statut Membre

Bonjour Frenchie83,

Je viens d'utiliser ton lien qui a l'air de répondre parfaitement à mes attentes. J'ai cependant un petit blocage, peut être pourras tu m'aider...

Voici le code reprise dans ton fichier, que j'ai cherché à adapter au mien:

Sub CreerTCD()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo GestionErreur
    Sheets("BLOCS").Select
    LigDeb = 6
    DerLig = [B100000].End(xlUp).Row
    ColDeb = 2
    DerCol = [IV6].End(xlToLeft).Column
    Set DonneesSource = Range(Cells(LigDeb, ColDeb), Cells(DerLig, DerCol))
    If Not Sheets("Analyse BLOCS") Is Nothing Then SupprimerLeTCD
    
CreationTCD:
    Sheets.Add.Name = "Analyse BLOCS"
    'utiliser l'enregistreur de macro pour la suite, puis coller ci-dessous
    'puis dans la première ligne, remplacer les termes suivants  par exemple:"SourceData:=B6:G30" par "SourceData:=DonneesSource"
    
    '*********************************************************************************************************************


    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DonneesSource, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Analyse BLOCS!R4C2", TableName:="Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion14
    Sheets("Analyse BLOCS").Select
    Cells(4, 2).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Groupe Activité BPI")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Activité BPI")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Sous Activité BPI")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Article" _
        )
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Unité UMPV")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("CA"), "Somme de CA", _
        xlSum
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Marge"), "Somme de Marge" _
        , xlSum
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Quantité UMPV"), _
        "Somme de Quantité UMPV", xlSum
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "UNI_BASE")
        .Orientation = xlRowField
        .Position = 6
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coût de Prod / Tonne"), _
        "Nombre de Coût de Prod / Tonne", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coût de Prod / Tonne")
        .Caption = "Somme de Coût de Prod / Tonne"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Coût de Prod / Tonne").Orientation = xlHidden
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coût de Prod en €"), _
        "Nombre de Coût de Prod en €", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coût de Prod en €")
        .Caption = "Somme de Coût de Prod en €"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Marge sur Coûts de Prod en K€"), "Nombre de Marge sur Coûts de Prod en K€", _
        xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Marge sur Coûts de Prod en K€")
        .Caption = "Somme de Marge sur Coûts de Prod en K€"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coûts commerciaux en €") _
        , "Nombre de Coûts commerciaux en €", xlCount
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Somme de Marge"), _
        "Somme de Marge", xlSum
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coûts commerciaux en €")
        .Caption = "Somme de Coûts commerciaux en €"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Marge commerciale"), _
        "Nombre de Marge commerciale", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Marge commerciale")
        .Caption = "Somme de Marge commerciale"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coûts PARC en €"), _
        "Nombre de Coûts PARC en €", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coûts PARC en €")
        .Caption = "Somme de Coûts PARC en €"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Marge départ "), _
        "Nombre de Marge départ ", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Marge départ ")
        .Caption = "Somme de Marge départ "
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coûts Structure en €"), _
        "Nombre de Coûts Structure en €", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coûts Structure en €")
        .Caption = "Somme de Coûts Structure en €"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Marge sur Coûts de Structure"), "Nombre de Marge sur Coûts de Structure", _
        xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Marge sur Coûts de Structure")
        .Caption = "Somme de Marge sur Coûts de Structure"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Coûts Transport en  €"), _
        "Nombre de Coûts Transport en  €", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Coûts Transport en  €")
        .Caption = "Somme de Coûts Transport en  €"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("Marge Nette"), _
        "Nombre de Marge Nette", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Nombre de Marge Nette")
        .Caption = "Somme de Marge Nette"
        .Function = xlSum
    End With
    Range("E12").Select
    ActiveWorkbook.ShowPivotTableFieldList = False
    Columns("C:T").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Groupe Activité BPI").LayoutForm = xlTabular
    Range("C5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Activité BPI" _
        ).LayoutForm = xlTabular
    Range("D5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Sous Activité BPI").LayoutForm = xlTabular
    Range("E5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Article"). _
        LayoutForm = xlTabular
    Range("D5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Sous Activité BPI").Subtotals = Array(False, False, False, False, False, False, _
        False, False, False, False, False, False)
    Range("E7").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Article"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Range("D5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Sous Activité BPI").Subtotals = Array(True, False, False, False, False, False, False _
        , False, False, False, False, False)
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotSelect "CEN", _
        xlDataAndLabel + xlFirstRow, True
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Unité UMPV"). _
        LayoutForm = xlTabular
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotSelect _
        "'Unité UMPV'[All;Total]", xlDataAndLabel, True
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Unité UMPV"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Range("H4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Quantité UMPV").Caption = "Total Quantité UMPV"
    Range("I4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de CA").Caption = "Total CA"
    Range("J4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge").Caption = "Total Marge  sur matière"
    Range("K4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Coût de Prod en €").Caption = "Total Coût de Prod en €"
    Range("L4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge sur Coûts de Prod en K€").Caption = _
        "Total  Marge sur Coûts de Prod en K€"
    Range("M4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Coûts commerciaux en €").Caption = "Total Coûts commerciaux en €"
    Range("N4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge commerciale").Caption = "Total Marge commerciale"
    Range("O4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Coûts PARC en €").Caption = "Total Coûts PARC en €"
    Range("P4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge départ ").Caption = "Total Marge départ "
    Range("Q4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Coûts Structure en €").Caption = "Total Coûts Structure en €"
    Range("R4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge sur Coûts de Structure").Caption = _
        "Total Marge sur Coûts de Structure"
    Range("S4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Coûts Transport en  €").Caption = "Total Coûts Transport en  €"
    Range("T4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").DataPivotField.PivotItems( _
        "Somme de Marge Nette").Caption = "Total Marge Nette"
    Range("G5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").TableStyle2 = _
        "PivotStyleMedium9"
    Range("C26").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").TableStyle2 = _
        "PivotStyleMedium2"
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotSelect _
        "'1BI - BLOCS ISOLANTS' '1 - BLOCS'", xlDataAndLabel, True
    Range("C1").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotSelect _
        "'1 - BLOCS' '1BI - BLOCS ISOLANTS' '1BIAC - BLOCS ISO - ACC' 'GO20074 - ANG PONCEBLOC 500X200X250 PPL' CEN UNI_BASE[UNI] 'Total Marge Nette'" _
        , xlDataOnly + xlFirstRow, True
    Range("F87").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("F65").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("F43").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A21").Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "UNI_BASE")
        .PivotItems("#N/A").Visible = False
    End With
    Range("T20").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Activité BPI" _
        ).AutoSort xlDescending, "Total Marge Nette", ActiveSheet.PivotTables( _
        "Tableau croisé dynamique2").PivotColumnAxis.PivotLines(13), 1
    Range("T29").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Sous Activité BPI").AutoSort xlDescending, "Total Marge Nette", ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotColumnAxis.PivotLines(13), 1
    Range("T83").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("UNI_BASE"). _
        AutoSort xlDescending, "Total Marge Nette", ActiveSheet.PivotTables( _
        "Tableau croisé dynamique2").PivotColumnAxis.PivotLines(13), 1
    Range("V84").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("V62").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("V40").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("V18").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("T5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("UNI_BASE"). _
        AutoSort xlDescending, "Total Marge Nette", ActiveSheet.PivotTables( _
        "Tableau croisé dynamique2").PivotColumnAxis.PivotLines(13), 1
    Range("C9").Select
    
GestionErreur:
    SupprimerLeTCD
    On Error GoTo 0
    On Error Resume Next
    GoTo CreationTCD
    
End Sub


Sub SupprimerLeTCD()
    On Error GoTo Sortie
    Application.DisplayAlerts = False
    Sheets("Analyse BLOCS").Delete
Sortie:
End Sub

J'ai le message d'erreur suivant : Argument ou appel de procédure incorrect. Le débogage me surligne la partie suivante :

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DonneesSource, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Analyse BLOCS!R4C2", TableName:="Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion14

Une idée de ce que j'ai mal fait ?

merci pour ton aide

Réponse 2 / 3
Frenchie83 Messages postés 2254 Statut Membre 339

Bonjour
Le seul problème que j'ai détecté est lié au nom de la feuille du TCD, Dans tout le module, remplacer Analyse BLOCS par AnalyseBLOCS.
En effet, il n'aime pas l'espace entre les deux mots.
Essayez
Cdlt
0
1. ti_mouton Messages postés 153 Statut Membre
  
  Effectivement sans l'espace ça marche parfaitement ! Merci beaucoup
  
  0

ti_mouton Messages postés 153 Statut Membre

Bonjour,

Je cherche à réutiliser ce code qui fonctionnait très bien pour l'adapter à un autre fichier mais je rencontre un blocage.

Sub CreerTCD1()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo GestionErreur
    Sheets("globalJANVIER").Select
    LigDeb = 1
    DerLig = [A100000].End(xlUp).Row
    ColDeb = 1
    DerCol = [C1].End(xlToLeft).Column
    Set DonneesSource = Range(Cells(LigDeb, ColDeb), Cells(DerLig, DerCol))
    If Not Sheets("JANVIER") Is Nothing Then SupprimerLeTCDTRANSPORTEURS

CreationTCD:
    Sheets("JANVIER").Tab.ColorIndex = 40

    'utiliser l'enregistreur de macro pour la suite, puis coller ci-dessous
    'puis dans la première ligne, remplacer les termes suivants  par exemple:"SourceData:=B6:G30" par "SourceData:=DonneesSource"
    '**********************************************************************************************************************************

    Range("B2").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DonneesSource, Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="JANVIER!R2C2", TableName:= _
        "LeTCDTRANSPORTEURS", DefaultVersion:=xlPivotTableVersion15
    With ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").PivotFields( _
        "Transporteurs")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").AddDataField ActiveSheet. _
        PivotTables("LeTCDTRANSPORTEURS").PivotFields( _
        "Somme de Nombre de transport"), "Somme de Somme de Nombre de transport", xlSum
    ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").AddDataField ActiveSheet. _
        PivotTables("LeTCDTRANSPORTEURS").PivotFields("Somme de Km Parcourus"), _
        "Somme de Somme de Km Parcourus", xlSum
    Range("C2").Select
    ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").DataPivotField.PivotItems( _
        "Somme de Somme de Km Parcourus").Caption = "Km Parcourus"
    Range("D2").Select
    ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").DataPivotField.PivotItems( _
        "Somme de Somme de Nombre de transport").Caption = "Nombre de transport"
    Range("B2").Select
    ActiveSheet.PivotTables("LeTCDTRANSPORTEURS").CompactLayoutRowHeader = _
        "Analyse Transports"
    Range("B2").Select
   '******************************************************************************************************************************************
Exit Sub
GestionErreur:
    SupprimerLeTCDTRANSPORTEURS
    On Error GoTo 0
    On Error Resume Next
    GoTo CreationTCD

End Sub
Sub SupprimerLeTCDTRANSPORTEURS()
    On Error GoTo Sortie
    Application.DisplayAlerts = False
    With Sheets("JANVIER")
        .Select
    Set Plage = .Range(.Cells(2, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 4))
        Plage.Clear
    End With
Sortie:
End Sub

VBA m'indique une erreur à ce niveau du code

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DonneesSource, Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="JANVIER!R2C2", TableName:= _
        "LeTCDTRANSPORTEURS", DefaultVersion:=xlPivotTableVersion15

"erreur definie par l'application ou par l'objet"

Une idée pour m'aider ?

Merci :)

Newsletters