XL 2021 Onglet Récapitulation

[phil87]

Bonjour le Forum,
J'aimerai faire un onglet récapitulatif correspondant aux renseignements des differentes semaines de mes onglets.
Mais je n'y arrive pas!!!!!!
Un "bouton" commanderait et inscrirait les resultats des semaines 1 ou 2 ou 3 ou 4,......etc...52. des differents onglets.
Merci par avance.

 

[chris]

Bonjour
Il y a un manque d'homogénéité dans les onglets

• Les 2 1ers ont une sorte de code client en A1: client 54, client 55
• les suivants avec un nom en lettres n'ont que client en A1 mais un nom en A2
• ceux avec un juste un numéro n'ont rien en A2

On peut penser que le nom du client est celui de l'onglet mais le 1er n'a pas la même orthographe dans la récap et la plupart des autres ont aussi des différences entre nom d'onglet et A2

Par ailleurs regrouper 23 clients X 3 colonnes X 52 semaines cela fait 3588 colonne plus celle avec les produits.
Pas très lisible

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @phil87, bonjour @chris
Effectivement cela manque un peu d'homogénéité mais bon ...
@phil87, je t'alerte sur l'anonymisation des données, masquer une feuille ne rend pas ses données invisibles.

Bon n'ayant pas de goût prononcé pour les produits que tu proposes (peut-être de la provocation ?) je suis reparti d'un fichier vierge avec trois feuilles "Tables", "Récap" et "Modèle" (cette dernière, masquée sert de modèle pour recréer les feuilles client.)
J'utilise des fonctionnalités de 2021 (et 365), les fonctions matricielles dynamiques.

Dans la feuille tables tu retrouves :

• l'année en court (avec ses 52 ou 53 semaines),
• la liste des produits,
• la liste des clients (avec le nom de la feuille associée).

Lors du changement de l'année une macro propose de faire une RàZ des données des feuilles client.
Un bouton permet de créer ou recréer les feuilles client et d'ajuster la feuille Récap à la liste des clients.

Dans la feuille Récap tu peux choisir la semaine suivie.
Les feuilles sont protégées (sans mot de passe) pour éviter les effacements de formules intempestifs.

Dans un premier temps, mets à jour les tables, puis clique sur le bouton "Recréer les x feuilles client"
puis saisie des données et va voir dans la feuille Récap le résultat.

La macro sur changement de l'année :

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = [Année].Address Then
     
          rép = MsgBox("RàZ des données ?", vbYesNo)
          If rép <> vbYes Then Exit Sub
          Application.ScreenUpdating = False
          chaîne = WorksheetFunction.TextJoin(";", True, [Tb_Clients[Nom feuille]])
          Liste = Split(chaîne, ";")
          On Error Resume Next
          For Each sh In Liste
               Worksheets(sh).[Lst_Produits].Offset(0, 1).Resize(100, 53 * 3).ClearContents
          Next
          On Error GoTo 0
          Application.ScreenUpdating = True
     End If
End Sub

La macro pour recréer les feuilles client:

Sub Créer_Feuilles_Client()
 
     Application.DisplayAlerts = False
     Application.ScreenUpdating = False
 
     'Recréer les colonnes client en fonction du nouveau nombre de clients
     With Sh_Récap
          .Unprotect
     
          Der_Col = .Columns.Count
          Col_Déb = .[Lst_Clients].Offset(0, 3).Column
          Nb_Clients = [Tb_Clients[Nom feuille]].Rows.Count
     
          'Suppression des colonnes au delà du 1er client
          .Range(.Cells(1, Col_Déb), .Cells(1, Der_Col)).EntireColumn.Delete
     
          'Copie en 2 fois pour préserver les fusion de cellules et ne pas multiplier les mises en forme conditionneles
          .[Lst_Clients].Resize(1, 3).Copy Destination:=.[Lst_Clients].Offset(0, 1).Resize(1, 3 * (Nb_Clients - 1))
          .[Lst_Clients].Offset(1).Resize(103, 3).Copy Destination:=.[Lst_Clients].Offset(1).Resize(103, 3 * Nb_Clients)
          .[Liste_Produits].Offset(0, 1).Resize(100, 3).Copy
          .[Liste_Produits].Offset(0, 1).Resize(100, 3 * [Tb_Clients[Nom feuille]].Rows.Count).PasteSpecial Paste:=xlPasteFormats
     
          'redéfinir Lst_Clients
          Set rg = .[Lst_Clients].Resize(1, 3 * Nb_Clients)
          ThisWorkbook.Names.Add Name:="Lst_Clients", RefersTo:=rg
     
          .Protect
     End With
 
     'Recréer les feuilles clients (après suppression éventuelle)
     For Each F In ThisWorkbook.Worksheets
          'Supprimer les feuilles issues du modèle
          If F.CodeName Like "Sh_Client#" Or F.CodeName Like "Sh_Client##" Or F.CodeName Like "Sh_Client###" Then F.Delete
     Next
     'rendre le modèle visible
     Sh_Client.Visible = xlSheetVisible
     For Each c In [Tb_Clients[Nom feuille]].Cells
          'Supprimer les feuilles de même nom que celles que l'on va créer
          On Error Resume Next: ThisWorkbook.Worksheets(c.Value).Delete: On Error GoTo 0
          'Copie du modèle
          Sh_Client.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
          ActiveSheet.Name = c.Value
     Next
     'masquer le modèle
     Sh_Client.Visible = xlSheetHidden
 
     Sh_Tables.Activate
     Application.ScreenUpdating = True
     Application.DisplayAlerts = True
 
End Sub

Voir PJ
A bientôt

 

[phil87]

Bonjour à toutes & à tous, bonjour @phil87, bonjour @chris
Effectivement cela manque un peu d'homogénéité mais bon ...
@phil87, je t'alerte sur l'anonymisation des données, masquer une feuille ne rend pas ses données invisibles.

Bon n'ayant pas de goût prononcé pour les produits que tu proposes (peut-être de la provocation ?) je suis reparti d'un fichier vierge avec trois feuilles "Tables", "Récap" et "Modèle" (cette dernière, masquée sert de modèle pour recréer les feuilles client.)
J'utilise des fonctionnalités de 2021 (et 365), les fonctions matricielles dynamiques.

Dans la feuille tables tu retrouves :

• l'année en court (avec ses 52 ou 53 semaines),
• la liste des produits,
• la liste des clients (avec le nom de la feuille associée).

Lors du changement de l'année une macro propose de faire une RàZ des données des feuilles client.
Un bouton permet de créer ou recréer les feuilles client et d'ajuster la feuille Récap à la liste des clients.

Dans la feuille Récap tu peux choisir la semaine suivie.
Les feuilles sont protégées (sans mot de passe) pour éviter les effacements de formules intempestifs.

Dans un premier temps, mets à jour les tables, puis clique sur le bouton "Recréer les x feuilles client"
puis saisie des données et va voir dans la feuille Récap le résultat.

La macro sur changement de l'année :

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = [Année].Address Then
    
          rép = MsgBox("RàZ des données ?", vbYesNo)
          If rép <> vbYes Then Exit Sub
          Application.ScreenUpdating = False
          chaîne = WorksheetFunction.TextJoin(";", True, [Tb_Clients[Nom feuille]])
          Liste = Split(chaîne, ";")
          On Error Resume Next
          For Each sh In Liste
               Worksheets(sh).[Lst_Produits].Offset(0, 1).Resize(100, 53 * 3).ClearContents
          Next
          On Error GoTo 0
          Application.ScreenUpdating = True
     End If
End Sub

La macro pour recréer les feuilles client:

Sub Créer_Feuilles_Client()
 
     Application.DisplayAlerts = False
     Application.ScreenUpdating = False
 
     'Recréer les colonnes client en fonction du nouveau nombre de clients
     With Sh_Récap
          .Unprotect
    
          Der_Col = .Columns.Count
          Col_Déb = .[Lst_Clients].Offset(0, 3).Column
          Nb_Clients = [Tb_Clients[Nom feuille]].Rows.Count
    
          'Suppression des colonnes au delà du 1er client
          .Range(.Cells(1, Col_Déb), .Cells(1, Der_Col)).EntireColumn.Delete
    
          'Copie en 2 fois pour préserver les fusion de cellules et ne pas multiplier les mises en forme conditionneles
          .[Lst_Clients].Resize(1, 3).Copy Destination:=.[Lst_Clients].Offset(0, 1).Resize(1, 3 * (Nb_Clients - 1))
          .[Lst_Clients].Offset(1).Resize(103, 3).Copy Destination:=.[Lst_Clients].Offset(1).Resize(103, 3 * Nb_Clients)
          .[Liste_Produits].Offset(0, 1).Resize(100, 3).Copy
          .[Liste_Produits].Offset(0, 1).Resize(100, 3 * [Tb_Clients[Nom feuille]].Rows.Count).PasteSpecial Paste:=xlPasteFormats
    
          'redéfinir Lst_Clients
          Set rg = .[Lst_Clients].Resize(1, 3 * Nb_Clients)
          ThisWorkbook.Names.Add Name:="Lst_Clients", RefersTo:=rg
    
          .Protect
     End With
 
     'Recréer les feuilles clients (après suppression éventuelle)
     For Each F In ThisWorkbook.Worksheets
          'Supprimer les feuilles issues du modèle
          If F.CodeName Like "Sh_Client#" Or F.CodeName Like "Sh_Client##" Or F.CodeName Like "Sh_Client###" Then F.Delete
     Next
     'rendre le modèle visible
     Sh_Client.Visible = xlSheetVisible
     For Each c In [Tb_Clients[Nom feuille]].Cells
          'Supprimer les feuilles de même nom que celles que l'on va créer
          On Error Resume Next: ThisWorkbook.Worksheets(c.Value).Delete: On Error GoTo 0
          'Copie du modèle
          Sh_Client.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
          ActiveSheet.Name = c.Value
     Next
     'masquer le modèle
     Sh_Client.Visible = xlSheetHidden
 
     Sh_Tables.Activate
     Application.ScreenUpdating = True
     Application.DisplayAlerts = True
 
End Sub

Voir PJ
A bientôt

Bonjour le forum, AtTheOne et Chris

Merci pour cette superbe macro AtTheOne.
Pour ce qui est des produits ce sont des produits licites!!!
Une petite question tout de meme.
Si je crée un nouveau client au cours de l'année, il me semble que ça supprime les récap anterieures lorsque l'on appuie sur le bouton "recréer les 23 feuilles client".
Ne pourrait on pas conserver les commandes des anciens clients dans Récap???
Encore merci tout de meme AtTheOne.

 

[AtTheOne]

Bonsoir à toutes & à tous, bonsoir @phil87
Oui, je n'ai pas équipé le classeur pour ajouter un client, mais on pourrait ...

Pour l'instant il faut procéder manuellement :

1. En bas du tableau"Tb_Clients" de la feuille "Tables" (sous la dernière ligne) saisir a minima le nom du nouveau client et le nom de la feuille associée (le tableau s'étend tout seul)
2. Copier la feuille Modèle (l'afficher puis en faire une copie).
3. La renommer du nom de feuille choisi dans la feuille "Table".
4. Dans l'onglet "Récap", ôter la protection, recopier les trois dernières colonnes (colonnes entières) juste à droite du tableau, remettre la protection.

Le tour est joué !
Je reviendrai vers toi avec une macro pour automatiser cette tâche

A bientôt

 

[AtTheOne]

Re-Bonsoir tout le monde
Je viens de créer la macro pour ajouter des clients sans effacer les anciennes données.
Elle est accessible via un clic droit dans la colonne "Client" de la feuille table (seulement s'il y a des nouveaux clients). On peut exécuter cette macro pour plusieurs clients en une seule fois.
Bon courage et à bientôt

 

[phil87]

Bonsoir à toutes & à tous, bonsoir @phil87
Oui, je n'ai pas équipé le classeur pour ajouter un client, mais on pourrait ...

Pour l'instant il faut procéder manuellement :

1. En bas du tableau"Tb_Clients" de la feuille "Tables" (sous la dernière ligne) saisir a minima le nom du nouveau client et le nom de la feuille associée (le tableau s'étend tout seul)
2. Copier la feuille Modèle (l'afficher puis en faire une copie).
3. La renommer du nom de feuille choisi dans la feuille "Table".
4. Dans l'onglet "Récap", ôter la protection, recopier les trois dernières colonnes (colonnes entières) juste à droite du tableau, remettre la protection.

Le tour est joué !
Je reviendrai vers toi avec une macro pour automatiser cette tâche

A bientôt

Bonjour AtTheOne,

Merci encore pour ton implication.
A bientôt.

 

[phil87]

Bonjour AtTheOne,

Merci encore pour ton implication.
A bientôt.