Compil onglets en récap selon son nom

Bonjour à tous,

Après avoir souvent trouvé mon bonheur sur le forum lorsque je cherchais du code VBA, formules, et j'en passe, je viens aujourd'hui solliciter votre aide pour traiter un sujet qui a déjà été abordé, mais qui - je pense - ne correspondait pas exactement à la fonction recherchée (quelques variables en plus).

J'en profite pour remercier également les nombreux contributeurs ayant aidés d'autres personnes car ce forum est une belle mine d'or.

Voici ce que je veux faire :

• Compiler des onglets excel ayant la même structure dans un onglet récap (jusque là rien de compliqué)
• le nombre de ligne peut être différent d'un onglet à l'autre (déjà trouvé aussi sur le forum ^^)
• compiler les onglets selon leur nom : j'ai plusieurs récap, et je veux que chacun des récap cherche la donnée en fonction du nom de l'onglet excel : les onglets qui finissent par _PO1 doivent alimenter le récap PO1, les onglets qui finissent par _PO2 le récap PO2, et ainsi de suite (j'ai mis 3 exemples différents et 2 onglet par type à compiler à la fois).

Il s'agit d'un fichier de suivi de calcul de bonus, chaque onglet est visible selon le nom et le mot de passe de l'utilisateur, et étant l'admin, je veux faire des récap par type de bonus (seul les admin y ont accès)

(merci Steelson btw pour le fichier c'est super utile!!; https://forum.excel-pratique.com/viewtopic.php?t=108763)

Le nombre de commerciaux peut évoluer dans mon fichier, je vais être amener à rajouter des onglets (trigramme avec 3 lettres + 2 chiffres composant la 1ère partie du nom de l'onglet), et j'ai plusieurs types de bonus "_POX"

Je vous joins le fichier, voir si ce que je veux réalisé est bien réalisable, si vous avez des pistes je suis preneur (pas très bon en VBA mais j'arrive à adapter le code )

Merci pour votre aide

Hello

Quelqu’un aurait-il une idée pour répondre au pb rencontré? Ça peut être une autre astuce/ un autre moyen de s’y prendre

Merci à vous !

Bonjour,

je n'avais pas vu ton post, tu as raison de relancer

je vais regarder d'ici demain matin

Bonjour,

Une proposition Récupérer et transformer (Power Query).

Pour actualiser les requêtes : Ctrl + m (VBA)

Cdlt.

Pour ma part, plus classique, mais une toute petite macro pas bien longue !

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Not Sh.Name Like "Récap*" Then Exit Sub
    Sh.Range("B6").CurrentRegion.Offset(1, 0).ClearContents
    For Each sw In Worksheets
        If Not sw.Name Like "Récap*" Then
            If Right(sw.Name, 3) = Right(Sh.Name, 3) Then
                Set plage = sw.Range("B6").CurrentRegion.Offset(1, 0).Resize(sw.Range("B6").CurrentRegion.Rows.Count - 1, sw.Range("B6").CurrentRegion.Columns.Count)
                plage.Copy Destination:=Sh.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        End If
    Next
End Sub

Merci à tous les deux pour votre aide

@Jean-Eric

Je ne connais pas encore PowerQuery je vais essayer d'apprendre notamment car j'utilise des base de données accès, je pense que cela pourra être utile d'apprendre ça avec power pivot. J'ai essayé de lancer la macro pour rafraîchir mais rien ne se passe, et quand je modifie manuellement voici ce que j'ai comme message (cf capture1)

alors que dans power query j'ai l'impression que l'ajout des lignes a bien fonctionné (cf capture2)

@Steelson

Je ne vois pas la macro dans le fichier, et lorsque je copie le code dans une nouvelle macro et que je le lance une fenêtre s'ouvre (cf capture3)

Merci encore

Re,

Une petite modification (!?).

Ne t'inquiète pas pur le message d'information.

Ctrl + m pour actualiser.

A te relire pour un complément d'informations.

Cdlt.

Super puissant!

Je viens de tester en ajoutant de nouvelles lignes et des nouveaux onglets, je vois que j'ai juste à modifier le nom du tableau de l'onglet que je créé et cela se met à jour, au top!!!

Je comprends un peu comment ça fonctionne comme j'ai déjà fait un peu d'access, mais par contre comment mes tables sont directement associées au champ custom?

Je vais sûrement être amené à ajouter d'autres PO, type _POX et aussi une table "cooptation" donc je suppose que je copie la structure de chaque requête et que je change le filtre dans une table je pourrai avoir le même résultat ?

EDIT: j'ai compris que ça prenait le nombre de colonne mais j'ai d'autres _POX que je vais ajoutés qui ont le même nombre de colonne que le PO3

N'est-il pas possible d'utiliser une table pour relier le nom de l'onglet avec un chiffre (ou autre) qui nous permettra d'appliquer le filtre que tu as fait? :

Du type si ça fini par "_PO1" alors 1 (je complique peut être la chose)

Je ne suis pas du tout à l'aise pour le moment avec PowerQuery

J'ai un peu traîné sur cet exercice pourtant simple pour comprendre comment faire le récap et maîtriser le fichier.

1/ Pourquoi autant de noms de tables dans le fichier, à quoi cela correspond?

2/ J'ai refait la requête pour récap PO1, en suivant 2 méthodes:

• 1ère méthode en utilisant un filtre personnalisable ou je viens relier le nom d'un onglet avec un champ qui servira de filtre plus tard (pas vraiment utile au final?)
• une méthode la plus simple possible (v2)

Est ce que vous trouvez cela correct? Y a t(il des étapes inutiles?

@Steelson je suis tjrs intéressé pour exécuter le code VBA que tu as envoyé, pour comparer un peu les 2 méthodes!

Merci!

JReilly a écrit :

@Steelson

Je ne vois pas la macro dans le fichier, et lorsque je copie le code dans une nouvelle macro et que je le lance une fenêtre s'ouvre (cf capture3)

La macro se situe dans le module ThisWorkBook

Tu n'as rien à faire, elle s'exécute toute seule quand tu sélectionnes un onglet "Récapxxxxxx"

Bonjour,

j'ai revu l'ensemble en fonction de tes nouvelles données.

J'ai ajouté une feuille (Aide) avec des explications sur la construction de la requête Tables.

A te relire.

Cdlt.

Nota: si soucis enregistrer le fichier en xlsm (et non pas en xlsb).

Super clair Jean-Eric merci je pense que ça va me pousser à utiliser Power Query et à repenser certains fichiers

Pour le fichier je vais finalement utiliser la macro de Steelson, ce sera plus facile d'utilisation pour que les personnes Admin du fichier ci-joint puissent facilement le mettre à jour/ le personnaliser.

A ce propos @Steelson, je suis reparti d'un fichier que tu avais construit pour que chaque personne n'ait accès qu'aux onglets le concernant.

Etant un des Admin du fichier, j'aurais accès à tous les onglets du fichier.

Pour accéder au fichier en PJ, il faut saisir "Admin" en utilisateur et "ADMN" en mot de passe:

1/ Pourquoi le document s'ouvre et demande le nom d'utilisateur avant que je clique sur "Accéder au document" ?

2/ N'y a-t-il pas un moyen dans l'onglet "Admin" de pouvoir accéder à tous les onglets sans les saisir dans la table en colonne D à E ? J'imagine notamment un utilisateur qui changerait le nom d'un onglet/ ou une mauvaise manip, et je ne pourrais plus voir l'onglet en question il disparaîtrait

3/ Comment intégrer ton code VBA envoyé hier Steelson au fichier en question? Je l'ai ajouté et j'ai l'impression que le fichier ne s'ouvrait plus

4/ Est-il possible d'accéder au code VBA qui permet de restreindre l'accès aux onglets?

Encore merci pour votre aide à vous 2, c'est vraiment top!

Bonjour,

Merci pour ce retour.

je te laisse entre les mains de Steelson (que je salue),

Bonne continuation et au plaisir de te revoir pour d'autres questions.

Cdlt.

@JReilly ... voir ma réponse plus haut https://forum.excel-pratique.com/viewtopic.php?p=881735#p881735

Tu as 2 solutions et tu choisis celle qui te convient le mieux bien sûr !

Mes salutations à Jean-Eric, le crack de PowerQ

edit : je n'ai lu que le message de Jean-Eric, ok je reprend ton message et regarde cela

JReilly a écrit :

A ce propos @Steelson, je suis reparti d'un fichier que tu avais construit pour que chaque personne n'ait accès qu'aux onglets le concernant.

Pour accéder au fichier en PJ, il faut saisir "Admin" en utilisateur et "ADMN" en mot de passe:

EDIT : j'avais mal lu le mdp !

JReilly a écrit :

1/ Pourquoi le document s'ouvre et demande le nom d'utilisateur avant que je clique sur "Accéder au document" ?

JReilly a écrit :

2/ N'y a-t-il pas un moyen dans l'onglet "Admin" de pouvoir accéder à tous les onglets sans les saisir dans la table en colonne D à E ? J'imagine notamment un utilisateur qui changerait le nom d'un onglet/ ou une mauvaise manip, et je ne pourrais plus voir l'onglet en question il disparaîtrait

JReilly a écrit :

3/ Comment intégrer ton code VBA envoyé hier Steelson au fichier en question? Je l'ai ajouté et j'ai l'impression que le fichier ne s'ouvrait plus

Seul celui qui a accès à tout le document pourra alors faire la compilation.

JReilly a écrit :

4/ Est-il possible d'accéder au code VBA qui permet de restreindre l'accès aux onglets?

Je découvre aussi un onglet qui se termine par COOPT ... que faut-il en faire ?

JReilly a écrit :

Le nombre de commerciaux peut évoluer dans mon fichier, je vais être amener à rajouter des onglets (trigramme avec 3 lettres + 2 chiffres composant la 1ère partie du nom de l'onglet), et j'ai plusieurs types de bonus "_POX"

1/ ce n'est pas un vraiment un sujet/pb au final

2/ via la macro actuelle c'est possible?

3/ Je l'ai insérée comme-ci je créais une macro, j'ai copié collé ton code mais lorsque je sauvegarde et que je ré-ouvre le fichier rien ne se passe/ je ne vois rien quand je clique sur "Visual Basic"

4/ Je ne vois pas comment visualiser le contenu de la macro, voici ce qu'il s'affiche (fichier ouvert avec le mdp "ADMN")

Merci bcp Steelson!

Steelson a écrit :

Je découvre aussi un onglet qui se termine par COOPT ... que faut-il en faire ?

JReilly a écrit :

Le nombre de commerciaux peut évoluer dans mon fichier, je vais être amener à rajouter des onglets (trigramme avec 3 lettres + 2 chiffres composant la 1ère partie du nom de l'onglet), et j'ai plusieurs types de bonus "_POX"

Normalement c’est pour être compilé comme les POX, mais je pourrais modifier le nom par soucis de simplicité

Voici

Encore une fois, tu ne peux pas "voir" la macro par Alt+F8 quand tu cherches les macros ... c'est une macro événementielle qui est dans le module ThisWorkBook, elle se déclenche toute seule quand on sélectionne un onglet Récapxxxxx !

Fais Alt+F11 et cliques sur ThisWorkBook.

Il y a un soucis ... tu as renseigné la ligne 5 des onglets !! du coup la région (currentregion) va de 5 à l'∞ et non plus de 6 à l'∞.

Si tu pouvais déplacer cette ligne en 4 et laisser un espace, sinon je modifie la macro.

Edit : j'ai modifié la macro pour tenir compte d'un double en-tête lignes 5 et 6.

Attention, si l'un est supprimée tu risques de perdre la première ligne de données.

Wooow merci Steelson c'est magnifique

J'avais compris qu'il s'agissait d'une macro événementielle mais je n'arrivais juste pas à l'insérer dans le fichier avec mot de passe..!

Un grand merci encore je suis ravi!