XL 2019 amélioration d'une macro (actualisation auto et automatisation)

[rh.finances]

Bonjour à tous les internautes de ce super forum,

le novice en macro que je suis rencontre un petit problème en vue d'actualiser et d'automatiser une macro quel que soit le nom de l'onglet.

je m'explique :
dans le fichier joint, j'ai une macro enregistrée en "Feuil 2(juin)" qui fonctionne très bien et qui est conçue comme suit :

Sub SansDoublonsTrié1()
  Dim temp()
  Set f = Sheets("juin")
  Set mondico = CreateObject("Scripting.Dictionary")
  a = Range(f.[ligne1_liste_tiers], f.[ligne_finale_liste_tiers].End(xlUp)).Value
  For Each c In a
     mondico(c) = ""
  Next c
  Set dest = f.Range("synthèse_1e_ligne")
  dest.Resize(mondico.Count, 1) = Application.Transpose(mondico.keys)
  dest.Resize(mondico.Count, 1).Sort Key1:=dest, Order1:=xlAscending
  Set mondico = Nothing ' libère mondico
End Sub

Toutefois, cette macro ne s'active que si je clique sur le bouton de commande "intégrer les données" figurant dans l'onglet "juin" - cellule M57
or, mon souhait serait que la macro s'active automatiquement sans avoir à cliquer sur un quelconque bouton.

par ailleurs, la macro comporte une commande intitulé Set f = Sheets("juin")
sachant que je compte utiliser la macro sur d'autres onglets qui seront intitulés "janvier", "février", etc., mon souhait serait que la macro intègre directement le nom de feuille afin de ne pas avoir à ressaisir à chaque fois le nom du mois tel que Set f = Sheets("janvier"), etc.

je vous remercie d'avance pour vos contributions.
bonne fin de journée.

Alex

 

[sylvanu]

Bonsoir rh.finances,

or, mon souhait serait que la macro s'active automatiquement sans avoir à cliquer sur un quelconque bouton.

Mais sur quel événement voulez vous que votre macro se déclenche ?

Si c'est quand on clique sur une feuille de mois, alors on peut faire comme macro :

Sub SansDoublonsTrié()
  Dim temp()
  Set f = ActiveSheet
  Set mondico = CreateObject("Scripting.Dictionary")
  a = Range(f.[$B$2], f.[$B$212].End(xlUp)).Value
  For Each c In a
     mondico(c) = ""
  Next c
  Set dest = f.Range("$P$60")
  dest.Resize(mondico.Count, 1) = Application.Transpose(mondico.keys)
  dest.Resize(mondico.Count, 1).Sort Key1:=dest, Order1:=xlAscending
  Set mondico = Nothing ' libère mondico
End Sub

et dans chaque feuille de mois :

Sub Worksheet_Activate()
    SansDoublonsTrié
End Sub

 

[Compte Supprimé 979]

Bonjour Alex,

Dans ThisWorkbook, vous mettez ce code

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ' Si la feuille n'est pas un mois, on sort
  If InStr(1, "janvier,février,mars,avril,mai,juin,juillet,août,septembre,novembe,décembre", Sh.Name, vbTextCompare) > 0 Then
    Call SansDoublonsTrié(Sh)
  End If
End Sub

Dans un module vous mettez

Sub SansDoublonsTrié(Sh As Worksheet)
  Dim temp()
  Set mondico = CreateObject("Scripting.Dictionary")
  a = Range(Sh.[ligne1_liste_tiers], Sh.[ligne_finale_liste_tiers].End(xlUp)).Value
  For Each c In a
     mondico(c) = ""
  Next c
  Set dest = Sh.Range("synthèse_1e_ligne")
  dest.Resize(mondico.Count, 1) = Application.Transpose(mondico.keys)
  dest.Resize(mondico.Count, 1).Sort Key1:=dest, Order1:=xlAscending
  Set mondico = Nothing ' libère mondico
End Sub

Vous supprimez cette même sub de toutes les feuilles ou vous l'avez

Et voilà, dès que vous changerez de feuille, si celle-ci est un mois le code est activé

A+

 

[rh.finances]

Bonjour Sylvanu et Bruno,

tout d'abord, je tiens à vous remercier pour votre contribution et vos explications.
en fait, je pense que j'ai du mal m'exprimer dans mon message et je m'en excuse.
Si vous reprenez mon fichier Excel initialement joint et que vous tapez par exemple "AAAA" dans la cellule B56
puis que vous cliquez sur le bouton de commande "intégrer les données" (situé sur la cellule M56), vous verrez apparaitre "AAAA" en cellule P60 devant "Amazon". si en revanche, vous tapez à la place "Bar tabac" en B56 et que vous cliquez sur "intégrer les données", vous verrez "bar tabac" apparaître en cellule P61 juste après "Amazon".
en fait, je voudrais continuer à faire exactement la même chose à chaque saisie entre les cellules B2 et B212 mais sans avoir à cliquer à chaque fois sur "intégrer les données" pour actualiser les résultats.
j'ai voulu le faire sur les fichiers que j'ai travaillé avec vos codes (voir docs joints) mais il n'y pas d'actualisation. si par exemple je tape "AAAA" en cellule B56, rien n'apparait dans la cellule P60.

encore merci à vous pour votre aide !!

Alex

 

[sylvanu]

Re,
Alors il suffit de modifier votre macro Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

Range("C:C").WrapText = True

If Not Application.Intersect(Target, Range("TIERS")) Is Nothing Then
    Application.EnableEvents = False
    Target = UCase(Target)
    SansDoublonsTrié
End If
Application.EnableEvents = True
End Sub

 

[job75]

Bonsoir à tous,

En plaçant ces macros dans ThisWorkbook c'est plus simple :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsDate("1/" & Sh.Name) Then
    Sh.Range("C:C").WrapText = True
    If Not Application.Intersect(Target, Sh.[B2].Resize(211)) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each Target In Target.Areas 'si entrées ou effacements multiples
            Target = UCase(Target)
        Next
        SansDoublonsTrié1 Sh
        Application.EnableEvents = True
    End If
End If
End Sub

Sub SansDoublonsTrié1(Sh As Object)
With Sh.[P60].Resize(211)
    .Value = Sh.[B2].Resize(211).Value
    .RemoveDuplicates Columns:=1, Header:=xlNo
    .Sort .Columns(1), xlAscending, Header:=xlNo
End With
End Sub

Le code fonctionne sur toutes les feuilles des mois.

A+

 

[rh.finances]

Merci Sylvanu pour le retour et merci également Job75 pour cette nouvelle proposition qui fonctionne tout aussi bien.
a l'usage, je viens de me rendre compte d'un bug inattendu sur une autre de mes macros.
En effet, si par exemple je vais dans l'onglet "juil" et que je copie ensemble les cellules A3 et B3 ("CB" Bar tabac cathédrale") pour les coller en cellule A56 - B56 de l'onglet "juin", j'ai une erreur d'exécution 13.
idem si par exemple je supprime en même temps les lignes B54 et B55 de l'onglet "juin".
il s'agit d'un erreur par rapport à ma macro de conversion de lettres en majuscules et dès que l'erreur d'exécution s'affiche, les macros que vous avez établis ne fonctionnent plus.
sauriez vous comment résoudre ce problème ?

Encore merci pour votre aide

Alexandre

 

[sylvanu]

Re,
Il suffit de rajouter en début de macro :

If Target.Count > 1 Then Exit Sub

Ainsi si une plage est sélectionnée la macro n'est pas exécutée.
En règle générale, sauf exception où on veut traiter une plage, c'est sécurisant de mettre cette ligne en début de sub événementielle.

 

[job75]

Je croyais avoir réglé la question des entrées multiples mais ce n'est pas le cas.

Il peut y avoir bug avec le code du post #6.

Celui-ci va bien il me semble :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsDate("1/" & Sh.Name) Then
    Sh.Range("C:C").WrapText = True
    Set Target = Application.Intersect(Target, Sh.[B2].Resize(211))
    If Not Target Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each Target In Target.Areas 'si entrées ou effacements multiples
            Target.Name = "Plage" 'plage nommée
            Target = [UPPER(Plage)]
        Next
        SansDoublonsTrié1 Sh
        Application.EnableEvents = True
    End If
End If
End Sub

Sub SansDoublonsTrié1(Sh As Object)
With Sh.[P60].Resize(211)
    .Value = Sh.[B2].Resize(211).Value
    .RemoveDuplicates Columns:=1, Header:=xlNo
    .Sort .Columns(1), xlAscending, Header:=xlNo
End With
End Sub

 

[rh.finances]

Encore une fois, merci beaucoup à vous Sylvanu et Job75.
je crois que cela correspond vraiment à ce que je cherche.

juste quelques petites questions Job75 sur le fonctionnement de la macro : le fait que celle-ci soit enregistrée dans ThisWorkbook, est ce que cela n'aura pas d'incidence sur les feuilles telles que celle prénommée "donnée consolidée" (la macro a en fait vocation à s'activer sur les seuls onglets comportant un nom de mois) ?
a noter que j'avais créer un nom "ligne_finale_liste_tiers" en cellule B212 de l'onglet "juin". Est-il possible d'envisager d'intégrer ce nom dans la macro afin de parer à l'éventualité d'insertion de nouvelles lignes dans le tableau ?

Encore merci pour tout. c'est vraiment sympa de pouvoir savourer votre expertise.

Alex

 

[job75]

Bonjour rh.finances, le forum,

juste quelques petites questions Job75 sur le fonctionnement de la macro : le fait que celle-ci soit enregistrée dans ThisWorkbook, est ce que cela n'aura pas d'incidence sur les feuilles telles que celle prénommée "donnée consolidée" (la macro a en fait vocation à s'activer sur les seuls onglets comportant un nom de mois) ?

Dans la macro il y a le test If IsDate("1/" & Sh.Name) Then...

Ainsi seules les feuilles des mois sont traitées.

A+

 

[rh.finances]

Bonjour rh.finances, le forum,

Dans la macro il y a le test If IsDate("1/" & Sh.Name) Then...

Ainsi seules les feuilles des mois sont traitées.

A+

merci beaucoup pour ce retour Job75