Bonjour, Encore une fois je reviens vers le forum ! Je veux creer un tableau croise dynamique grace a VBA car mon fichier de base vari tous les mois (le nombre de lignes) J'ai fait plusieurs tentatives mais a chaque fois, cela beuggue quelque part. Ma derniere est la suivante : Option Explicit Sub TCD_recap() Dim plage As Range Dim num_ligne As Long Dim DerLig As Long num_ligne = Range("A1").CurrentRegion.Rows.Count DerLig = num_ligne - 1 With Sheets("base valabs").Select Set plage = .Range(.Cells(1, 1), .Cells(DerLig, 14)) End With Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ plage).CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="TCD_recap" End Sub Lors de l'execution, excel bloque ici : Set plage = .Range(.Cells(1, 1), .Cells(DerLig, 14)) Je ne comprends pas pourquoi ! Aidez-moi s'il vousp lait, je vais m'arracher les cheveux :( Je peux vous donner plus d'information si necessaire Merci d'avance Configuration: Windows XP / Internet Explorer 8.0

J'ai reussssi !!!! Il ne me reste qu'un soucis de format de TCD mais pour le moment, je vais me contenter de ca :) Je mets mon code si dessous si ca interesse quelqu'un : Option Explicit Sub TCD_recap() Dim plage As Range Dim DerLig As Long Dim NomFeuille As String NomFeuille = "base valabs" DerLig = Worksheets(NomFeuille).Range("A" & Rows.Count).End(xlUp).Row 'Selection de la plage source presente sur l'onglet "base valabs" Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14)) Sheets.Add.Name = "recap" 'Creation du TCD ActiveSheet.PivotTableWizard _ SourceType:=xlDatabase, _ SourceData:=plage, _ TableDestination:=Range("A3"), _ TableName:="TCD_recap" 'Mise en forme With ActiveSheet.PivotTables("TCD_recap") 'Ajoute un champ de lignes nommé "CZTDCS". .AddFields RowFields:="CZTDCS" 'Définit l'orientation du champ nommé "CZPN" en tant que Donnée. .PivotFields("CZPN").Orientation = xlDataField 'Définit l'orientation du champ nommé "VALEURECAR" en tant que Donnée. .PivotFields("VALEURECAR").Orientation = xlDataField 'Définit l'orientation du champ nommé "VALABS" en tant que Donnée. .PivotFields("VALABS").Orientation = xlDataField End With End Sub Encore merci a ce forum d'exister ! Manue

TCD plage variable avec VBA [Résolu]

Réponse 2 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
Modifié par Heliotte le 11/12/2012 à 11:35

Bonjour manueHN,
A ajouter au code, après "DerLig = num_ligne - 1":

If(DerLig<1) Then MsgBox "Le numéro de la dernière ligne est : " & DerLig & " !!!"

edit: faute de syntaxe !

manueHN
11 déc. 2012 à 11:43

Re,

Je ne comprends pas pourquoi il faut rajouter un message box ? Sur mon onglet ou se situe ma base (base valabs), il y a toujours plus d'une ligne.

En plus, la le message box ce fait sur l'onglet ou doit etre cree le tableau croise dynamyque, mais le TDC ne se cree pas. Toujours un beuggue au niveau de :
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

Cordialement

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
Modifié par Heliotte le 11/12/2012 à 11:50

Si je te demande d'ajouter TEMPORAIREMENT ce MsgBox, c'est uniquement pour voir le numéro de la dernière ligne que tu récupère avec la variable "DerLig".
C'est tout !

edit: maintenant, il faut voir si tu récupères ..
on pourrait faire autrement :

MsgBox "Le numéro de la dernière ligne est : " & DerLig & " !!!"

sans le

If(DerLig<1) Then

De cette façon on connaîtra le numéro de la dernière ligne !!!

manueHN
11 déc. 2012 à 11:47

Ah, desolee, je ne comprenais pas :(
Dans ces cas la, le MsdBox me renvoi le chiffre 0

Ca veut dire quoi ?

Réponse 3 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
10 déc. 2012 à 18:38

A la place de :

With Sheets("base valabs").Select
Set plage = .Range(.Cells(1, 1), .Cells(DerLig, 14))
End With

Essayez avec :

Dim plage As Range
    Dim NomFeuille As String
    NomFeuille = "base valabs"
    '
        Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(20, 14))

manueHN
11 déc. 2012 à 10:54

Bonjour,

ca fait 3 fois que je reecris ce message ! lol
Merci pour ta reponse rapide. J'ai essaye ta solution ce matin et Excel bloque a l'endroit suivant :
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

Je ne sais pas pourquoi. C'est pourtant ce que tu m'avais suggere, sauf que j'ai change Cells(20, 14) par Cells(DerLig, 14) car ma derniere ligme active est variable en fonction des mois.

Vois-tu ou est l'erreur ?
Merci encore pour ton aide.

Je te joins mon code en entier :

Option Explicit

Sub TCD_recap()

Dim plage As Range
Dim num_ligne As Long
Dim DerLig As Long
Dim NomFeuille As String

NomFeuille = "base valabs"
num_ligne = Range("A1").CurrentRegion.Rows.Count
DerLig = num_ligne - 1

'Creation du TCD
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
plage).CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="TCD_recap", DefaultVersion:=xlTable4

'Mise en forme
With ActiveSheet.PivotTables("TCD_recap")
'Ajoute un champ de lignes nommé "CZTDCS".
.AddFields RowFields:="CZTDCS"
'Définit l'orientation du champ nommé "CZPN" en tant que Donnée.
.PivotFields("CZPN").Orientation = xlDataField
'Définit l'orientation du champ nommé "VALEURECAR" en tant que Donnée.
.PivotFields("VALEURECAR").Orientation = xlDataField
'Définit l'orientation du champ nommé "VALABS" en tant que Donnée.
.PivotFields("VALABS").Orientation = xlDataField

End With

End Sub

Réponse 4 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 11:53

manueHN,
Cela veut dire,
Soit, qu'il n'y a pas de ligne remplie en "A",
Soit que le code que tu utilise pour récupérer la ligne est erroné !!!

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 11:55

manueHN,
Essaie ceci:

Dim DerLig As Long 
DerLig = Range("A" & Rows.Count).End(xlUp).Row

et tiens-moi au courant, merci

Réponse 6 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 11:57

manueHN,

J'ai oublié de te précisé ..
le "A" peut être changé .. par exemple tu es certain que toutes les lignes sont "UTILISÉE" dans la colonne "D" mais pas sûr dans la colonne "A" .. alors tu changes cette lettre, tout simplement.

manueHN
11 déc. 2012 à 12:09

re,

J'ai essayer ta proposition et ca beuggue toujours sur cette ligne. Quand je passe mon curseur sur DerLig ca me met "DerLig=1", hors il devrait me renvoyer 132.

Je te met mon code car j'ai peut etre fait des betises :

Option Explicit

Sub TCD_recap()

Dim plage As Range
Dim DerLig As Long
Dim NomFeuille As String

NomFeuille = "base valabs"
DerLig = Range("A" & Rows.Count).End(xlUp).Row

'Creation du TCD
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
plage).CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="TCD_recap", DefaultVersion:=xlTable4

'Mise en forme
With ActiveSheet.PivotTables("TCD_recap")
'Ajoute un champ de lignes nommé "CZTDCS".
.AddFields RowFields:="CZTDCS"
'Définit l'orientation du champ nommé "CZPN" en tant que Donnée.
.PivotFields("CZPN").Orientation = xlDataField
'Définit l'orientation du champ nommé "VALEURECAR" en tant que Donnée.
.PivotFields("VALEURECAR").Orientation = xlDataField
'Définit l'orientation du champ nommé "VALABS" en tant que Donnée.
.PivotFields("VALABS").Orientation = xlDataField

End With

End Sub

Merci :)

Réponse 7 / 11

manueHN
11 déc. 2012 à 12:03

Mais le MsgBox a l'air de ce faire sur l'onglet du TCD qui est vide actuellement puisque je n'arrive pas a le creer et non sur l'onglet ou se situe la base (base valabs).

En fait, pour tout t'expliquer, je suis obligee de passer par la car sur mon onglet de base, la derniere ligne est composee de formule (somme, nbval). Evidemment, je ne veux pas la prendre pour mon TDC.

C'est pourquoi, j'ai pense pouvoir passer par une variable qui me trouve le dernier numero de ligne : num_ligne = Range("A1").CurrentRegion.Rows.Count
Puis, lui demander de prendre le nombre de ligne precedent pour ne pas prendre les totaux : DerLig = num_ligne - 1

Cette variable fonctionnait avec ce code :
Set plage = .Range(.Cells(1, 1), .Cells(DerLig, 14))
DerLig me renvoyait bien ce que je cherchais : ligne 132

Mais plage etait egal a "Nothing"

Je me demande si ce n'est pas plutot un probleme avec :
Dim plage As Range

???

Réponse 8 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 12:21

A tester :

DerLig = Worksheets(NomFeuille).Range("A" & Rows.Count).End(xlUp).Row

manueHN
11 déc. 2012 à 12:24

DerLig est maintenant egal a 132
Mais mon code continu a bloquer au niveau :
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

plage=nothing

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 12:35

ok et la valeur de "DerLig " ?

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 12:38

Quel est la dernière ligne VISIBLE en colonne "A" ?
Sur quel onglet dois-tu récupérer la valeur "DerLig" ?

manueHN
11 déc. 2012 à 12:39

Quand je passe mon curseur dessus dans la partie de code suivant :
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))

DerLig=132

manueHN
11 déc. 2012 à 12:42

La derniere ligne visible en colonne A est 132, en colonne C, c'est 133.

La valeur DerLig doit etre recuperee sur l'onglet "base valabs" afin de definir ma plage, soit
de la cellule A1 a N & DerLig

Réponse 9 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 13:25

manueHN,

Tout ce passe bien jusqu'à la création de la feuille .. il crée une nouvelle feuille qu'il place en première position.

Ensuite, il ne veux pas :

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
plage).CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="TCD_recap", DefaultVersion:=xlTable4

Erreur d'exécution 1004 : Erreur définie par l'application ou par l'objet

Il ne veut pas créer le tableau croisé dynamique .. désolé, mais là, je ne vais pas pouvoir t'être d'une grande utilité.

manueHN
11 déc. 2012 à 13:29

Chez moi, le code me fait une erreur a "set...."
et une, au niveau de "ActiveWorkbook..." comme tu l'indiques. Par contre, il ne me met pas de type d'erreur.

Peux-tu me mettre le code que toi tu as essaye chez toi ?
En tout cas merci pour ton aide.
J'espere reussir a debbeugguer le code pour la creation du TCD
Cordialement

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 13:33

Tu m'avais dit que dans le msgbox, le résultat était "plage $A$1:$N$132".
- Donc, si tu es passé ce message, le "Set" devrait être bon ?!?
- Est-ce qu'il te crée une nouvelle feuillé ?
- Et à quelle position te met-il cette nouvelle feuille ?

manueHN
11 déc. 2012 à 13:36

Il me cre une feuille devant la feuille ou se situe ma base (base valabs)

et en effet, je n'ai plus de probleme avec "Set ..." sauf si je ne supprime pas la nouvelle feuille cree et que je reexecute la macro

Réponse 10 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 13:40

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
plage).CreatePivotTable TableDestination:=ActiveSheet.Range("A3"), TableName:="TCD_recap", DefaultVersion:=xlTable4

As-tu une feuille qui se nomme "TCD_recap" ?

manueHN
11 déc. 2012 à 13:42

non, je n'en ai pas. Je pensais que sous "TableName" , il fallait indiquer le nom que l'on souhaitait donner a notre TCD

Je me trompe ?

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 13:48

Le soucis est que mes connaissances en ce qui concerne les tableaux croisés dynamique ne sont .. pas au top.

Par contre, je sais que, quand on crée une feuille, Excel lui donne un nom quelconque directement .. et après .. pour aller la rechercher, il faut faire un boucle sur la collection de feuille pour, par exemple, la renommer à sa façon perso.

La question que je me pose : comment peut-on créer une table pivot dans une feuille inconnue ? car, je pense que cette nouvelle feuille crée par le code, dont le nom lui est totalement inconnu ...

Réponse 11 / 11

Heliotte Messages postés 1491 Date d'inscription vendredi 26 octobre 2012 Statut Membre Dernière intervention 28 janvier 2013 92
11 déc. 2012 à 13:49

Il faut penser à renommer la nouvelle feuille, à mémoriser son nom, et l'activer pour pouvoir créer la table pivot, non ?

manueHN
11 déc. 2012 à 13:54

Pour moi, avec la partie du code :
TableDestination:=ActiveSheet.Range("A3")

il devrait me creer le TCD dans la feuille active (pour moi la nouvelle feuille creer) en cellule A3.

Par contre, je ne suis pas sur que pour lui, la nouvelle feuille cree soit bien la feuille active

TCD plage variable avec VBA

11 réponses

Discussions similaires