TCD plage variable avec VBA [Résolu/Fermé]

Signaler
-
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
-
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

11 réponses

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
4
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 56630 internautes nous ont dit merci ce mois-ci

Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
Bonsoir manueHN,
Félicitation et bonne continuation
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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 !
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
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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 !!!
Ah, desolee, je ne comprenais pas :(
Dans ces cas la, le MsdBox me renvoi le chiffre 0

Ca veut dire quoi ?
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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))
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
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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é !!!
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
manueHN,
Essaie ceci:
Dim DerLig As Long 
DerLig = Range("A" & Rows.Count).End(xlUp).Row
et tiens-moi au courant, merci
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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.
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 :)
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

???
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
A tester :
DerLig = Worksheets(NomFeuille).Range("A" & Rows.Count).End(xlUp).Row 
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
Quel est la dernière ligne VISIBLE en colonne "A" ?
Sur quel onglet dois-tu récupérer la valeur "DerLig" ?
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
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
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
J'ai fait la même chose que toi, sur classeur Excel, version 2003, et ça fonctionne .. bizarre !
Après le "Set ..", peux-tu mettre ce code
MsgBox "plage: " & plage.Address
et me dire ce qu'il y est écrit, merci
le MsgBox inscrit : "plage $A$1:$N$132"

Je te remets mon code pour voir si des choses different par rapport a ton code :

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


'Creation du TCD
Set plage = Worksheets(NomFeuille).Range(Cells(1, 1), Cells(DerLig, 14))
MsgBox "plage: " & plage.Address

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

NB : Je precise que la je travaille sur Excel 2007 mais qu'il est en mode compatibilite car au travail, je dois pouvoir l'utiliser sur Excel 2003.
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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é.
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
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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 ?
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
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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" ?
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 ?
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
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 ...
Messages postés
1491
Date d'inscription
vendredi 26 octobre 2012
Statut
Membre
Dernière intervention
28 janvier 2013
83
Il faut penser à renommer la nouvelle feuille, à mémoriser son nom, et l'activer pour pouvoir créer la table pivot, non ?
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