Aide macro automatisation définition de noms de plages

[zibouf]

Bonjour à tous,

Je reviens vers vous suite à mon dernier message d'hier. Au passage, encore un grand merci à Dranreb pour son aide.

sujet ici https://www.excel-downloads.com/thr...oms-des-plages-sur-plusieurs-colonnes.185014/

Dranreb m'a donné la solution de macro suivante...
Cependant, et ne voulant pas non plus trop le solliciter j'aimerai savoir comment modifier cette macro de manière à ce qu'elle me créer des noms mais en zone étendue sur le "Classeur" et nom pas sur la feuille active, car j'aurai besoin de ces noms pour les intégrer dans des formules sur d'autres feuilles du même classeur...
Il faudrait donc que cette macro nomme mes plages sur l'étendue du classeur...


Sub déf_noms()
Dim Cel As Range, Nom As String, Réf As String
For Each Cel In ActiveSheet.Rows(1).Cells
Nom = Replace(Cel.Value, " ", "_")
If Nom <> "" Then
Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
On Error Resume Next
ActiveSheet.Names.Add Nom, Réf
If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
On Error GoTo 0
End If
Next Cel
End Sub


Merci pour votre aide précieuse... car niveau vba, pour ma part je ne m'en sors pas vraiment

Cordialement.

 

[Robert]

Re : Aide macro automatisation définition de noms de plages

Bonjor Zibouf, bonjour le forum,

Peut-être comme ça (non testé) :

Sub déf_noms()
Dim Cel As Range, Nom As String, Réf As String
Dim sh As Object

For Each sh In Sheets
    For Each Cel In sh.Rows(1).Cells
        Nom = Replace(Cel.Value, " ", "_")
        If Nom <> "" Then
        Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
        On Error Resume Next
        ActiveSheet.Names.Add Nom, Réf
        If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
        On Error GoTo 0
        End If
    Next Cel
Next sh
End Sub

 

[zibouf]

Re : Aide macro automatisation définition de noms de plages

Bonjour Robert et merci pour ta réponse,

Je viens de tester, mais malheureusement ca ne fonctionne pas... En fait ta macro nomme TOUTES les entêtes de TOUTES mes feuilles de mon classeur avec une zone étendue restreinte à la feuille sur laquelle j'active la macro... lol...

En fait, j'aimerai que cette macro nomme les entêtes de la feuille en cours (celle ou j'active la macro) sur une zone étendue au classeur tout entier.
Actuellement cette macro nomme mes entêtes sur une zone étendue limitée à la feuille sur laquelle ma macro est activée...

Cordialement.

 

[Robert]

Re : Aide macro automatisation définition de noms de plages

Bonjour Zibouf, bonjour le forum,

En fait, j'aimerai que cette macro nomme les entêtes de la feuille en cours (celle ou j'active la macro) sur une zone étendue au classeur tout entier.
Actuellement cette macro nomme mes entêtes sur une zone étendue limitée à la feuille sur laquelle ma macro est activée...

Alors il faut que tu expliques ce que tu appelles classeur tout entier parce que je comprends pas ta requête...

 

[zibouf]

Re : Aide macro automatisation définition de noms de plages

re,

Désolé, je n'ai pas été assez clair effectivement.

En fait, la macro que j'utilise actuellement fonctionne très bien. Elle nomme bien automatiquement mes plages en fonctions de l'entête de colonne... Ca impec !

Par contre, comme le montre l'image ci dessous, elle limite mes définitions de noms à la feuille active où se trouve la macro.
Ce que j'aimerai, si c'est possible, c'est que cette macro définisse les noms de mes plages comme elle le fait actuellement, mais à l'étendue du "Classeur" et pas à l'étendue de la feuille active (dans l'exemple, ma macro limite mes noms de plages à la feuille active "BDD clients") alors que j'aimerai que l'étendue soit à tout le classeur. Donc en fait, pour être plus clair, dans l'étendue (cf image ci-dessous), à la place de "BDD clients", j'aimerai, si possible, "Classeur".



Espérant avoir été plus clair.

Merci pour votre aide.

Cordialement.

 

[Robert]

Re : Aide macro automatisation définition de noms de plages

Bonjour Zibouf, bonjour le forum,

Désolé Zibouf mais je ne sais pas faire. Je pense que ce n'est pas faisable car il me semble qu'une plage nommée ne peut s'appliquer qu'à un seul onglet. Mais il est fort possible qu'un balèze de ce forum vienne prouver le contraire...

 

[zibouf]

Re : Aide macro automatisation définition de noms de plages

Merci quand même...

Bonne continuation.

@+

 

[Littledave]

Re : Aide macro automatisation définition de noms de plages

Salut tout le monde,

Pour définir en vba un nom sur le classeur :

Le classeur en cours

ActiveWorkbook.Names.Add Name:="test2", RefersToR1C1:="=Feuil1!R13C8"

Un classeur spécifique

Workbooks("Classeur1").Names.Add Name:="test2", RefersToR1C1:="=Feuil1!R13C8"

Pour définir en vba un nom sur une feuille

Sur la feuille active

ActiveSheet.Names.Add Name:="test3", RefersToR1C1:="=R13C8"

ou sur une feuille spécifique

Worksheets("Feuil1").Names.Add Name:="test3", RefersToR1C1:="=R13C8"

J'espère que ca correspond à ta demande
@++
David

PS : Pour définir un nom sur un classeur remarque qu'il faut décliner la feuille de la/les cellules

 

[Guest]

Re : Aide macro automatisation définition de noms de plages

Bonjour à tous,

Pour qu'un nom renvoie aux mêmes plages sur chaque feuille:

 ThisWorkbook.Names.Add Name:="test2", RefersToR1C1:="=!R13C8"

A+

 

[Littledave]

Re : Aide macro automatisation définition de noms de plages

Re,

Pour ton problème on remplace activesheet.names.add par activeworkbook.names.add soit le code suivant :

Sub déf_noms()
Dim Cel As Range, Nom As String, Réf As String
Dim sh As Object


For Each sh In Sheets
    For Each Cel In sh.Rows(1).Cells
        Nom = Replace(Cel.Value, " ", "_")
        If Nom <> "" Then
        Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
        On Error Resume Next
        ActiveWorkBook.Names.Add Nom, Réf
        If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
        On Error GoTo 0
        End If
    Next Cel
Next sh
End Sub

Teste ce code et dis nous si cela est bon ?

@++
David

 

[zibouf]

Re : Aide macro automatisation définition de noms de plages

Houlalal, merci à vous tous... pour vos réponses...

Littledav, je viens de tester ton code, apparemment on s'en approche si ce n'est que ta macro renomme TOUTES mes entêtes de Toutes mes feuilles de mon classeur... par contre, elle construit bien les noms à l'étendue "Classeur" comme je le souhaitais et plus à l'étendue de la feuille active de la macro...

Hors, il faudrait que cette macro ne définisse que les plages de noms des entêtes de la feuille sur laquelle je l'active, actuellement
elle le fait pour toutes les feuilles de mon classeur...

Exemple:

J'ai un classeur X (peu importe le nom)
Il contient 10 feuilles. Chaque feuille à des entêtes.
Je souhaiterai que la macro définisse les plages de noms avec les entêtes de la feuille 1. Je crée donc un bouton avec macro sur cette feuille 1...
J'aimerai donc que la macro suite à son activation définisse comme le fait ta macro, les plages de noms mais seulement de la feuille 1, si activer sur la feuille 1, ou de la feuille 2 si activer sur la feuille 2 etc... Ta macro, elle, définit les noms de plages de TOUTES les feuilles de mon classeur. cf image ci-dessous...



Merci à vous tous du temps que vous me consacrez.

 

[Guest]

Re : Aide macro automatisation définition de noms de plages

Re,

Il me semble, si j'ai bien compris qu'il faille enregistrer tes références de noms comme je le disais plus haut. avec un ! après le =

A+

 

[Littledave]

Re : Aide macro automatisation définition de noms de plages

Re Zibouf,

Alors si j'ai bien tout compris tu souhaites avoir un nom du style : nomfeuille.nom

Si tel est le cas, j'ai ajouté une ligne de code qui modifie le nom à définir par
nom = activesheet.name & "." & nom

Sub déf_noms()
Dim Cel As Range, Nom As String, Réf As String
Dim sh As Object


For Each sh In Sheets
    For Each Cel In sh.Rows(1).Cells
        Nom = Replace(Cel.Value, " ", "_")
        If Nom <> "" Then
        Réf = "=OFFSET(" & Cel.Address(True, True, xlA1, True) & ",1,0,COUNTA(" & ActiveSheet.Columns(1).Address(True, True, xlA1, True) & ")-1,1)"
        On Error Resume Next
        Nom = ActiveSheet.Name & "." & Nom
        ActiveWorkbook.Names.Add Nom, Réf
        If Err Then MsgBox "Impossible de nommer """ & Nom & """ la réf:" & vbLf & Réf, vbCritical, "Noms dynamiques"
        On Error GoTo 0
        End If
    Next Cel
Next sh
End Sub

Est-ce bien ta demande ?

@++
David

 

[zibouf]

Re : Aide macro automatisation définition de noms de plages

Bonjour Hasco,

Merci également pour ton aide...

Dsl, mais en VBA je ne gère absolument rien... Pour être tout à fait franc, je ne comprends pas ce que tu veux que je fasse, bien que j'ai essayé de modifier la macro comme tu me le suggérais...

Une idée peut être avec ce code que l'on doit à BoisGautier, cf pièce jointe.

Sa macro pour champs dynamique correspondrait apparemment, mais comment l'intégrer à mon classeur, car j'ai fait plusieurs tests mais la macro bogue...

Sub déf_noms()
For Each c In Range([A1], [IV1].End(xlToLeft))
If Not IsEmpty(c.Offset(1, 0)) Then
ActiveWorkbook.Names.Add Name:=c, RefersTo:= _
"=OFFSET(" & c.Address & ",,,COUNTA(" & c.EntireColumn.Address & ")-1)"
End If
Next
End Sub

Pour rappel, je souhaite créer une macro qui automatise mes définitions de noms pour une colonne entière en les nommant par leur entête respective... Ces noms de champs doivent pouvoir être utilisables dans tout le classeur, c'est à dire sans restriction sur l'Etendue", (terme utilisé dans onglet définitions de noms. cf photos ci-dessus ), mais la macro doit s’exécuter seulement sur la feuille active. Actuellement, la macro proposée par LittleDave répond à tous les critères sauf qu'elle s'exécute sur toutes les feuilles de mon classeur...

De plus cette macro doit intégrer la formule suivante pour définir les noms: =DECALER('Feuille active'!$B$1;1;0;NBVAL('Feuille active'!$A:$A)-1;1)

Feuille active étant la feuille sur laquelle s'exécute la macro.

Espérant avoir été clair.

Merci.

 

[Guest]

Re : Aide macro automatisation définition de noms de plages

Re,

Sans classeur exemple, personnellement je ne me lancerai pas dans autres choses que de simples suggestions. Ce que j'ai fait plus haut.

A+