XL 2016 Consolider onglet sauf 1,2,ou 3

[Scorpio]

Bonjour à tous,
J'ai un classeur et j'aimerais bien l'utiliser, mais j'aimerais faire une petite modification, ce que je ne suis pas capable de faire.

En fait, j'aimerais consolider ou regrouper la feuille: Filiale1, Filiale2, Filiale3
Et, j'aimerais éviter de consolider ou regrouper la feuille: Feuil1, Perso, Conso.

Est-ce que un membre pourrais me donner un petit coup de pouce, s'il vous plaît, je le remercie d'avance.
A++++

 

[chris]

Bonjour

Tu as 2016 pas Excel 5 alors autant utiliser les possibilités de 2016 à savoir PowerQuery... et les tableaux structurés

Exemple ci-joint : pas de code : une simple actualisation suffit

 

[Scorpio]

Salut Chris
Oui, c'est Excel 2016, mais j'ai pas bien compris ton explication.
Est-ce qu'il s'agit de Power Pivot dans le Ruban ?

 

[chris]

Re

Non PowerQuery qui est intégré et accessible depuis l'onglet Données

PowerPivot ne sert qu'aux TCD

 

[Scorpio]

RE,
A très bien, merci de ton info.
Comme je ne connais pas encore le system Power Query,
je vais essayé d'en savoir un peut plus, sur la marche a suivre surtout.

 

[chris]

Re

Pour commencer mettre les tableaux à consolider sous forme de tableaux et les nommer :
J'ai mis T_Filiale1 etc et pour les autres à ne pas consolider TA_Nom.

La requête PowerQuery que tu peux voir en allant dans l'éditeur est une requête un peu plus complexe que ce qu'on fait habituellement (PowerQuery permet plein de choses incroyables en quelques clics)

Ici il faut

• lancer l'éditeur PowerQuery depuis l'onglet Données
  
• aller dans l'éditeur avancé à partir d'une requête vide et mettre
  

  let
          Source = Excel.CurrentWorkbook(),
          myTable = Table.AddColumn(Source, "Colonnes", each Table.ColumnNames([Content]))
      in
          myTable

  pour avoir la liste des tableaux structurés du classeur
  
• ensuite via la liste déroulante de la colonne Name, filtrer ce qui ne commence pas par TA
• puis cliquer sur la double flèche en haut de la colonne Content et décocher "Utiliser le nom de la colonne d'origine..." pour lister la consolidation
• supprimer les deux dernières colonnes
• Sortir par le bouton en haut à gauche : Fermer et charger dans, choisir Table et nouvel onglet
• On est de retour dans Excel. Et il y a des lignes vides dans le tableau obtenu : renommer ce tableau TA_Conso (ou autre nom commençant par TA de façon à ne pas consolider la consolidation puis actualiser.
• Modifier la requête pour classer selon la colonne Nom (on ne peut le faire avant en raison de la consolidation de consolidation faite tant qu'on n'a pas renommé)

Ensuite tout tableau ajouté et dont le nom ne commence pas par TA sera automatiquement consolidé et tout tableau dont le nom commence par TA sera ignoré...

 

[Scorpio]

RE,
Ho, super, Merci d'avoir pris un peut de ton temps pour m'expliquer toute la marche à suivre.
Apparemment tu en a l'expérience du Power Query.
Je te remercie, je vais faire des tests, et on vas voir.
Merci encore à toi Chris.
A++++

 

[zebanx]

Bonsoir à tous.

On se retrouve avec Chris sur un cheminement identique (Power Query / VBA boucle recap) sur un post de cette semaine.
Proposition par VBA pour consolider les feuilles qui contiennent le mot "Filiale".

Bonne soirée
zebanx

Sub conso()
Sheets("CONSO").[A1].CurrentRegion.Offset(1, 0).Clear
For s = 2 To Sheets.Count
If Sheets(s).Name Like "*Filiale*" Then
   Range(Sheets(s).[A2], Sheets(s).[A65000].End(3).Offset(1, 0).End(2)).Copy [A65000].End(3).Offset(1, 0)
End If
Next s
On Error Resume Next
[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Scorpio]

Bonsoir zebanx,
Je dois dire, je nage avec ce Power Query, cela ne dois pas être très compliqué, mais pour moi, c'est la première, et là, je nage.
J'ai juste une question si tu me permet:
La feuille "CONSO" je peux la déplacer ou je veux dans la barre des onglets, puis consolider, puisque seul les onglets contenant le mot "Filiale" seront consolider.
Si j'ai bien compris

 

[zebanx]

Re-

Oui, j'ai juste modifié "2" par "1" sur la boucle for.
Ca devrait être ok.

@+

Sub conso()
Sheets("CONSO").[A1].CurrentRegion.Offset(1, 0).Clear
For s = 1 To Sheets.Count
If Sheets(s).Name Like "*Filiale*" Then
   Range(Sheets(s).[A2], Sheets(s).[A65000].End(3).Offset(1, 0).End(2)).Copy [A65000].End(3).Offset(1, 0)
End If
Next s
On Error Resume Next
[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Scorpio]

Heu, non, j'ai dis une bétizzzzzzze,
il faut mettre l'onglet tous à gauche

 

[Scorpio]

RE zebanx,
Ce qui veut dire, je peux mettre l'onglet ou je veux ??

 

[zebanx]

Re-

Ca ne change rien... L'important c'est que tu ne mettes pas "Conso Filiales" comme nom d'onglet.
Je rajoute un Lcase pour ne pas être embêter par la casse du nom des onglets comme tu le verras.

@+

Sub conso()
Sheets("CONSO").[A1].CurrentRegion.Offset(1, 0).Clear
For s = 1 To Sheets.Count
If LCase(Sheets(s).Name) Like "*filiale*" Then
   Range(Sheets(s).[A2], Sheets(s).[A65000].End(3).Offset(1, 0).End(2)).Copy [A65000].End(3).Offset(1, 0)
End If
Next s
On Error Resume Next
[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Scorpio]

RE,
C'est fantastique, tous qui marche à merveille je te remercie infiniment zebanx.
Au plaisir d'un autre message, merci encore
A+++

 

[chris]

RE

...
On se retrouve avec Chris sur un cheminement identique (Power Query / VBA boucle recap) sur un post de cette semaine.
Proposition par VBA pour consolider les feuilles qui contiennent le mot "Filiale".

Dommage avec 2003 tu ne sais pas ce que tu perds à ne pas pouvoir découvrir et utiliser PowerQuery.
Tu aimerais sûrement .
J'utilise majoritairement 2010 (avec les add on) et occasionnellement 2016 : pour rien au monde je ne reviendrais à 2003....
J'espère que tu as gardé Win7 car avec Win10, 2002 et 2003 ne marchent plus depuis les dernières MAJ.
(On peut bidouiller certaines mises à jour mais cela ne tiendra pas longtemps comme avec Windows mail qui saute à chaque MAJ...)