Tableau croisé dynamique - Création

Bonjour,

Je cherche à créer un tableau croisé dynamique par VBA, or je tombe sur le message d'erreur suivant :

"Reference is not valid".

Sub Pivot_Table()

    Dim Data_Sheet, TCD_Sheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long

    Set Data_Sheet = Worksheets("Combined Table")
    Set TCD_Sheet = Worksheets("Calculation")

    LastRow = Data_Sheet.Cells(3, 1).End(xlUp).Row
    Set PRange = Data_Sheet.Cells(3, 1).Resize(LastRow, 61)

   'Suppression de tous les TCD existants dans la feuille

    'For Each PvtTCD In wshTCD.PivotTables
        'PvtTCD.TableRange2.Clear
    'Next PvtTCD

    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="PRange") _
                .CreatePivotTable(TableDestination:=TCD_Sheet.Cells(2, 2), TableName:="TCD_test_one")

    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=TCD_Sheet.Cells(1, 1), TableName:="TCD_test_one")

    With TCD_Sheet.PivotTables("TCD_test_one")

        With .PivotFields("Technical Service Creator")
            .Orientation = xlRowField
            .Position = 1
        End With

        With .PivotFields("Technical Service Creator")
            .Orientation = xlDataField
            .Function = xlCount
        End With

    End With

End Sub

Merci de votre aide.

Bonjour,

Ta plage que tu nommes PRange n'est qu'un objet VBA qui disparaît à la fin de l'exécution de ta macro et n’apparaît pas dans Excel. En conséquence, le code suivant ne sera pas interprété :

SourceData:="PRange"

Essaie plutôt :

SourceData:=PRange

Bonjour,

Essaie ceci car tu ne crées qu'un TCD !? :

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)    
Set PTable = PCache.CreatePivotTable(TableDestination:=TCD_Sheet.Cells(1, 1), TableName:="TCD_test_one")

Merci,

Alors en l'occurrence les guillemets sont enlevés, mais je suis à présent avec une erreur Type Mismatch sur les mêmes 2 lignes.

Re,

A qui t'adresses-tu ?

Cdlt.

Re,

Je ne m'adresse à personne en particulier, puisque dans les 2 cas de réponse vous proposez un code sans guillemets pour la partie considérée.

Donc même avec ta proposition Jean-Eric j'ai une erreur Type Mismatch.

Ici je ne cherche à créer qu'un seul TCD mais à terme je souhaite en créer plusieurs en effet.

Merci.

Re,

Essaie ceci, mais avant tout assure toi que toutes les colonnes aient un en-tête (valide).

Cdlt.

Option Explicit

Sub Create_Pivot_Table()
Dim wsData, wsPT As Worksheet
Dim lastCol As Long, LastRow As Long
Dim rngPT As Range
Dim PCache As PivotCache
Dim PT As PivotTable

    Set wsData = Worksheets("Combined Table")
    Set wsPT = Worksheets("Calculation")

    With wsData
        lastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngPT = .Cells(3, 1).Resize(LastRow - 2, lastCol)
    End With

    For Each PT In wsPT.PivotTables
        PT.TableRange2.Clear
    Next PT

    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngPT)
    Set PT = PCache.CreatePivotTable(TableDestination:=wsPT.Cells(3, 1), TableName:="TCD_test_one")

    With PT
        .AddFields RowFields:="Technical Service Creator"
        With .PivotFields("Technical Service Creator")
            .Orientation = xlDataField
            .Function = xlCount
        End With
    End With

End Sub

Cela ne fonctionne toujours pas, en revanche quand j'écris cela :

SourceData:="Combined Table!R2C1:R1631C61"

Et cela :

TableDestination:="Calculation!R1C1"

Là ça fonctionne.

Comment faire donc référence à l'adresse d'un range afin de mettre ce range à jour par la suite ?

Bonjour,

Pourquoi le recréer à chaque fois ?

En général on le crée manuellement, et si besoin on ne change que la source en vba.

eric

Je veux que tout se fasse en un seul bouton pour une personne qui ne souhaite pas passer plus de quelques secondes à mettre à jour les données. Vive le monde professionnel.

Blague à part, bien qu'avéré, je souhaite vraiment que tout se fasse automatiquement pour plusieurs raisons.

Re,

Envoie un fichier !...

Cdlt.

Donc si j'écris cela & cela :

        Set rngPT = .Cells(2, 1).Resize(LastRow, 61)
        RangeP = rngPT.Address

Suite :

SourceData:=RangeP

...là ça fonctionne.

En revanche si j'écris cela :

SourceData:=rngPT.Address

...cela ne fonctionne pas.

Des pistes ?

J'ai trouvé où se situe le problème mais pas comment y remédier.

En fait, pour compléter mon précédent message, cela fonctionne seulement si je suis sur la feuille d'où proviennent les données, c'est à dire sur "Data_Sheet" (ou "wsData"), si cette feuille est donc active.

Si j'exécute la macro en étant sur la feuille où les données vont être reçus, donc sur l'autre ("wsPT"), là ça plante.

Comment puis-je donc modifier le code de sorte que cela fonctionne quel que soit la feuille active ?

Merci bien.

Alors active la feuille source avant le SourceData:=

Le filtre avancé a le même défaut et pas d'autre solution pour lui.

Je veux que tout se fasse en un seul bouton pour une personne qui ne souhaite pas passer plus de quelques secondes à mettre à jour les données. Vive le monde professionnel.

Ca change quoi ?

Il colle ses données et à l'activation de la feuille TCD tu modifies juste la source et tu actualises...

Même plus de bouton.

eric

Ok merci de l'info Eric, en effet cela semble optimal.

La question semble bête mais je n'y arrive pas : qu'écrire, et où, au niveau du code pour rendre wsData la feuille active ?

Merci bien.

Quelque part avant de t'en servir !

wsData.Activate

Lol ok.

Du coup ma question c'est est-ce que le code tel qu'écrit va "activer" une autre feuille à un moment donné ?

Je risque pas de l'activer "trop tôt" wsData si après la macro travaille sur l'autre feuille ("wsPT") ?

Une fois que tu as fini avec cette feuille, tu peux activer l'autre qui t'intéresse, ou mieux, préciser la feuille de chaque objet par un code type :

With Sheets("wsPT")
     .Range("A1") = "Toto"
     .Cells(1, 2) = 0
     '...
End With

En effet, à quelques exceptions près, il n'est pas nécessaire d'activer une feuille pour y faire des opérations (à l'inverse de ce que fait l'enregistreur de macro).

Re,

Cette procédure peut être lancée de Feuil1 ou Feuil2 sans erreur !...

Donc si tu as une erreur, c'est lors de la définition de rngPT.

Cdlt.

Sub Create_Pivot_Table()
Dim wsData As Worksheet, wsPT As Worksheet
Dim lastCol As Long, LastRow As Long
Dim rngPT As Range
Dim PCache As PivotCache
Dim PT As PivotTable

    Set wsData = Worksheets("Feuil1")
    Set wsPT = Worksheets("Feuil2")

    With wsData
        lastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngPT = .Cells(3, 1).Resize(LastRow - 2, lastCol)
    End With

    For Each PT In wsPT.PivotTables
        PT.TableRange2.Clear
    Next PT

    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngPT)
    Set PT = PCache.CreatePivotTable(TableDestination:=wsPT.Cells(3, 1), TableName:="TCD_1")

    MsgBox "TCD cr?e !...", 64, "Information"

End Sub

Merci pour vos réponses,

Je comprends absolument ce que vous me dites, cependant je bloque toujours :

Avec le même code, notamment ici :

        Set rngPT = .Cells(3, 1).Resize(LastRow - 2, lastCol)

J'ai toujours une erreur "Type Mismatch" ici :

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngPT)

... au niveau du rngPT.

On a vraiment le même code (aussi pour la définition de rngPT), alors je ne comprends vraiment pas :

• pourquoi une erreur Type Mismatch sur rngPT.
• pourquoi la macro ne s'active que sur une seule feuille.

Merci encore.