VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statistiques

[mubaze]

Bonjour,

je dispose d'un classeur excel ayant 200 feuilles excel (parfois plus parfois moins) avec le même nombre de colonne mais pas le même nombre de lignes sur chaque feuille.

je souhaite contruire un tableau croisé dynamique reprennant l'ensemble de mes feuilles. Ce tableau sera mis dans une nouvelle feuille dont le nom sera "synthèse".

Merci d'avance

PS: Est ce également possible que je vous pose des questions sur le code afin que je le comprenne (je débute en vba)

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

Je pense que cela est possible mais XL n'est, peut-être, pas l'application idoine pour traiter autant de feuilles (et nous ne connaissons pas le nombre de colonnes et de lignes).

Dépose deux feuilles ou trois dans le même classeur et avec une dizaine de lignes (on est d'accord que les colonnes sont identiques sur toutes les feuilles et toutes les feuilles sont dans le même classeur...)

A+ à tous

 

[fifi]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

200 feuilles c'est qu'il y a un problème assez important :/

peut être récapituler les feuilles sur une seule en fonction du nombre d'information a regrouper?

il faudrait que toute les feuilles aient la même structure.
4déjà faut faire une macro pour récapituler les infos sinon impossible 😛

à te lire.

 

[mubaze]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

bonjour,

oui la structure du fichiers est la même.

il peut y avoir 1 à plusieurs lignes par feuilles mais les colonnes sont toujours constantes. de A à AJ.

il y a une ligne d'en tête.

Pensez-vous que cela est possible de creer un tcd multifeuille ?

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

C'est tout à fait possible avec XL (mais comme déjà dit : une autre application serait surement plus performante)

Quel le nombre de lignes maxi par feuilles (200 x NB Lignes doit être < à 65536 pour les versions <= XL 2003)

Dans le TCD, que tu veux tu faire : Sommer, Nombrer ?

Nous attendons toujours tes deux ou trois feuilles ?

A+ à tous

 

[mubaze]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour,

Veuillez trouver ci-joint un exemple de classeur à plusieurs onglets. Pour répondre à ta question: le nombre d'onglet X le nombre de ligne n'excede pas le 50000 lignes. il y a en général en 10 et 60 lignes par onglets.

je souhaiterai récuperer le nombre de date de regroupement colonne AI

pour le tcd de synthèse : les entetes suivantes

en lignes: A, C, AE, AG, AJ,AI
en données: AI

j'aimerai également que ce tcd soit crée sur une nouvelle feuille qui sera appelé TCD_name où name = cellule A2 de l'entete colonne A

si j'ai bien compris la technique va etre de concatener toutes les feuilles en une seule puis de créér le tcd ? pourriez vous me commenter le code etant novice j'aimerai continuer mon apprentissage, je vous remercie beaucoup pour votre aide.


bien a vous !

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

Voila un essai (tu aurais pu déposé des données dans les feuilles....)

A+ à tous

 

[mubaze]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Merci beaucoup,


La macro a buggé car je n'avais pas de feuille Recap, j'ai donc creer une feuille recap. ensuite la macro se lance bien. Néanmoins, dans recap, il n'y a pas d'entete ?

Comment fais tu pour faire le tcd en macro ? je n'ai pas vu ton code ?

sinon c'est excatement ça que je voulais. Merci infiniment

j'ai d'autres questions:

1/ j'aimerai en effet, que la macro créé automatiquement le tcd. J'imagine que tu vas te baser sur l'onglet recap ? si oui comment as tu fais puisque ça ne recupere pas la ligne d'entete ?

2/ merci pour votre assistance !

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Nonjour à tous

Le TCD est construit une fois pour toutes
Il faut recopier la première ligne d'un des onglets que la feuille 'Recap'
Cette ligne devrait le faire pour les suivantes...

ShProv.Range("A1:AJ1").Copy .Cells(Derlig, 1) 'on copie les entêtes de l'onglet source

Les données du TCD sont nommées Base_TCD par Nom / Définir :

=DECALER(Recap!$A:$AJ;;;NBVAL(Recap!$A:$A))

A+ à tous

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

Voici le code pour la Consolidation des Onglets (Clique sur le GO Consolidation de la feuille 'Recap')

Option Explicit

Sub Extraction() ' d'aprés BH² sur XLD

Dim F As Byte 'déclaration de la variable F de 1 à 255
Dim Nb As String 'itou pour NB, qui représentera le nom de la feuille
Dim Derlig As Integer 'itou pour Derlig, qui calculera la première ligne vide
Dim ShProv As Object 'itou pour SHProv, qui sera la feuille source
Application.ScreenUpdating = False 'on annule le rafraichissement de l'écran
Sheets("Recap").Range("A2:AJ65000").Clear 'on efface tout dans l'onglet "Recap"
For F = 3 To Sheets.Count  'on va balayer tous les onglets
    Set ShProv = Sheets(F) 'on détermine l'onglet source
    ShProv.Range("A1:AJ1").Copy Sheets("Recap").Range("A1")
    With Sheets("Recap") 'sur l'onglet "Recap"
        Derlig = .[A65000].End(xlUp).Row + 1 'calcul de la première ligne vide
        ShProv.Range("A1:AJ1").Copy .Cells(Derlig, 1) 'on copie les entêtes de l'onglet source
        ShProv.Range("A1:AJ1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
            .Range(.Cells(Derlig, 1), .Cells(Derlig, 36)), Unique:=False
            'Ici, on copie toutes les données de l'onglet source dans l'onglet "Recap"
            'par le biais d'un filtre élaboré
            'on copie de la cellule A (première ligne vide) à la cellule AJ (première ligne vide)
        .Rows(Derlig).Delete 'on supprime les en-têtes qu'on venait de copier
        .Columns(35).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            'on supprime toutes les lignes dont les valeurs de la colonne AI sont vides
        With Rows("1:1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 90
        .ReadingOrder = xlContext
        .Rows.AutoFit
    End With
        .Cells.EntireColumn.AutoFit 'on ajuste la largeur des colonnes
    End With
Next F
ActiveWorkbook.RefreshAll
End Sub

Voici le code pour la création du TCD (Clique sur le GO TCD de la feuille 'Recap')

Option Explicit

Sub TCD()
ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:="=OFFSET(Recap!C1:C36,,,COUNTA(Recap!C1))"
Sheets.Add after:=Sheets(1)
    ActiveSheet.Name = "TCD"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Base_TCD").CreatePivotTable TableDestination:= _
        "'[Recap et TCD MultiFeuilles.xls]TCD'!R3C1", TableName:="TCD1"

ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("TCD1").PivotFields("Equipe TSE").Orientation = xlRowField
ActiveSheet.PivotTables("TCD1").PivotFields("Code entreprise").Orientation = xlRowField
ActiveSheet.PivotTables("TCD1").PivotFields("Salarié à conserver oui / non").Orientation = xlRowField
ActiveSheet.PivotTables("TCD1").PivotFields("Date d'envoi du mail à l'entreprise").Orientation = xlRowField
ActiveSheet.PivotTables("TCD1").PivotFields("Commentaires").Orientation = xlRowField

ActiveSheet.PivotTables("TCD1").AddDataField ActiveSheet. _
PivotTables("TCD1").PivotFields( _
        "Date de regroupement des doublons"), _
        "Nombre de Date de regroupement des doublons", xlCount
Range("A4").Select
    ActiveSheet.PivotTables("TCD1").PivotFields("Equipe TSE"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
 ActiveSheet.PivotTables("TCD1").PivotFields("Code entreprise").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("C4").Select
    ActiveSheet.PivotTables("TCD1").PivotFields("Salarié à conserver oui / non"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Range("D4").Select
    ActiveSheet.PivotTables("TCD1").PivotFields( _
        "Date d'envoi du mail à l'entreprise").Subtotals = Array(False, False, False, _
        False, False, False, False, False, False, False, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.RefreshAll
End Sub

A+ à tous

 

[mubaze]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

bonjour,

merci beaucoup ! ça fonctionne parfaitement pour la consolidation mais j'ai un petit soucis pour le tcd: ça bug

1/TCD: bug

le bug dit qu'il y a : erreur d'execution 5: argument ou appel de procedure incorrect.

la ligne du debogage est :

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Base_TCD").CreatePivotTable TableDestination:= _
"'[Recap et TCD MultiFeuilles.xls]TCD'!R3C1", TableName:="TCD1"

2/ consolidation

De plus, la consolidation ne fonctionne pas mon classeur où il y a 365 onglet, apparemment c'est du au stockage de la variable F en byte, non ? Néanmoins ça fonctionne bien pour mon classeur de 240 onglets.

3/ questions sur le code:

a) Qu'est ce que fait cette ligne de code ?

ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:="=OFFSET(Recap!C1:C36,,,COUNTA(Recap!C1))"

est ce que c'est en lien avec la zone nom que tu as défini ?

b) lorsque je clique le tcd GO, pour créer le tcd et que ça bug. si par la suite je reclique sur le tcd, il me sort une nouvelle erreur comme quoi une feuille ne peut porter deux fois le même nom. Comment inserer le code pour qu'il verifie et ecrase la tcd feuille tcd ?

cordialement,

Merci

PS: j'ai vraiment l'impression de progresser avec vous.
Merci du fond du coeur.

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

Il faut renommer cette ligne :

TableDestination:= "'[Recap et TCD MultiFeuilles.xls]TCD'!R3C1"

avec le nom exact de ton fichier...

Juste ça :

[SIZE=5][COLOR=Red]'[/COLOR][/SIZE][Recap et TCD MultiFeuilles.xls]

Ceci étant le nom de la feuille et l'adresse de la cellule de départ :

TCD[SIZE=5][COLOR=Red]'[/COLOR][/SIZE]!R3C1

NE PAS OUBLIER LES ' si le nom du fichier est composé d'espace(s)

Pour :

ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:="=OFFSET(Recap!C1:C36,,,COUNTA(Recap  !C1))"

C'est exact, cela crée la Base_TCD par Nom / Définir : Colonne A à AJ pour le nombre de lignes de la colonne A

Si la feuille TCD existe... Il suffit d'Actualiser le TCD

Pour le F :

Dim F As Integer 'déclaration de la variable F de - à  + 2 000 000 000

Pour la feuille TCD, rajoute ceci :

Sub TCD()
ActiveWorkbook.Names.Add Name:="Base_TCD", RefersToR1C1:="=OFFSET(Recap!C1:C36,,,COUNTA(Recap!C1))"
[B][COLOR=Blue]Application.DisplayAlerts = 0
If Sheets(2).Name = "TCD" Then Sheets("TCD").Delete
Application.DisplayAlerts = 1
[/COLOR][/B] Sheets.Add after:=Sheets(1)
    ActiveSheet.Name = "TCD"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Base_TCD").CreatePivotTable TableDestination:= _
        "[B][COLOR=Red]'[Recap et Creation TCD MultiFeuilles.xls][/COLOR][/B]TCD'!R3C1", TableName:="TCD1"

' le reste est identique

A+ à tous

 

[mubaze]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

coucou,

j'ai changé le byte en integer et cela me donne : erreur d'execution 1004:

nom de champ introuvable ou incorrect dans la plage d'extraction ?

la ligne de debogage est:

ShProv.Range("A1:AJ1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
.Range(.Cells(Derlig, 1), .Cells(Derlig, 36)), Unique:=False

questions:

est ce que c'est en liens le changement de byte en integer ? y a peut etre autre chose à changer ?

 

[JCGL]

Re : VBA création tableau croisé dynamique (TCD) à partir de 200 feuilles et statisti

Bonjour à tous,

Je viens de tester sur 299 onglets de données sans problème.

As-tu dans le feuille 'Recap' la ligne des champs nécessaires au filtre élaboré ?

J'ai rajouté une ligne qui vient les coller dans un de mes derniers messages.

ShProv.Range("A1:AJ1").Copy Sheets("Recap").Range("A1")

Merci de prendre les derniers codes déposés...

Combien pèse ton fichier de 380 onglets ?

A+ à tous