Renommer des plages nommées

[Chubby]

Bonjour à tous,

J'ai une feuille de travail sur laquelle j'ai nommé des plages. Ce fichier doit être renouvelé en août. J'aimerai pouvoir profiter de sa structure pour l'année suivante. le problème est que j'ai nommé plus de 100 plages et je me vois mal repalucher tout ça toutes les années.
Avez-vous une idée pour me faciliter la tâche?
Merci à vous.
Je vous joins le fichier en question ... formules/gestionnaire de noms et là c'est un bouillon de culture de plages nommées. Alors je suis ouvert à toutes les idées qui me sauveraient des heures de travail vraiment pas passionnant.

 

[JCGL]

Re : Renommer des plages nommées

Bonjour à tous,
Salut Chubby,

Tu ne dis pas Quoi remplacer par Quoi...

Ce petit code pour lister les Noms définis, la plage de Référence et le commentaire associé :

Option Explicit
Sub ChercheNoms()
    Dim N As Name, X&
    X = 2
    Range("A2:F500").ClearContents
    For Each N In ActiveWorkbook.Names
    On Error Resume Next
        N.Visible = True
        Cells(X, 1) = N.Name
        Cells(X, 2) = "'" & N.RefersToR1C1
        Cells(X, 3) = N.Comment
        X = X + 1
    Next
    'Range("E2").ListNames
End Sub

A+ à tous

 

[Chubby]

Re : Renommer des plages nommées

Bonsoir a toi JCGL
Bonsoir le forum et les zotres ,

Merci JCGL pour ton intérêt a mon problème. Je vais faire court car je suis sans pc mais avec un tel capricieux. Oui je suis toujours aussi etourd et ce qui me paraissait évident ne l'est pas effectivement. Pour etre plus explicite, il s'agit de pouvoir renommer les colonnes 2012 en 2013, les 2011 en 2012, les 2010 en 2011. Voilà le principe du topo.
J'essayerai ta macro demain matin JCGL merci

 

[Chubby]

Re : Renommer des plages nommées

Bonjour JCGL et les zotres,

J'ai collé le code ChercheNoms que tu m'a laissé. Je dois m'y prendre comme à mon habitude car je n'observe rien de nouveau.
Je l'ai collé sur la fenêtre général de VBA. Est ce la bonne démarche? Merci de tes indications.

 

[job75]

Re : Renommer des plages nommées

Bonjour Chubby, salut Jean-Claude 🙂

A mon avis il faut procéder en deux temps.

1) Lancer cette macro par Ctrl+R (une fois suffit) :

Sub Renommer()
Dim nom As Name, t$
'---modification des noms définis---
For Each nom In ThisWorkbook.Names
  t = Replace(nom.Name, "_2012", "_A1")
  t = Replace(t, "_2011", "_A2")
  t = Replace(t, "_2010", "_A3")
  t = Replace(t, "_2009", "_A4")
  ThisWorkbook.Names.Add t, nom.RefersTo
  nom.Delete
Next
'---modification des noms dans la feuille---
[C:DL].Replace "_2012", "_A1", xlPart
[C:DL].Replace "_2011", "_A2"
[C:DL].Replace "_2010", "_A3"
[C:DL].Replace "_2009", "_A4"
End Sub

Elle permet de remplacer, dans les noms définis, 2012 2011 2010 2009 par A1 A2 A3 A4.

2) Lancer cette macro par Ctrl+A pour créer la feuille de l'année suivante :

Sub NouvelleAnnée()
Dim t$
t = ActiveSheet.Name
If Not t Like "####-####*" Then Exit Sub
t = Val(t) + 1 & "-" & Val(t) + 2 & Mid(t, 10)
On Error Resume Next
If IsError(Sheets(t)) Then
  '---création de la feuille---
  Application.ScreenUpdating = False
  ActiveSheet.Copy After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = t
  '---translation des données---
  [BQ:CN].Copy [CO1]
  [AS:BP].Copy [BQ1]
  [U:AR].Copy [AS1]
  '---nouvelles années---
  [A1:AR5].Replace Val(t) - 1, Val(t), xlPart
  [A6:A65000].Replace Val(t), Val(t) + 1 'à voir
  [A6:A65000].Replace Val(t) - 1, Val(t) 'à voir
  '---vidage des données numériques---
  [A6:AR65000].SpecialCells(xlCellTypeConstants, 1).ClearContents
End If
End Sub

Fichier joint.

A+

 

[job75]

Re : Renommer des plages nommées

Re,

Je viens de simplifier le vidage des données numériques.

A+

 

[Chubby]

Re : Renommer des plages nommées

Bonsoir Job75, bonsoir les zotres,

Purée, c'est la touche du Maitre. Je n'ai que peu de temps là mais un clic et tout semble rouler. Je regarde de plus près demain matin. mais déjà, un grand merci, un immense merci. Même si ce n'est pas la fonction de ce site de donner du clé en main, c'est vraiment un beau travail.
Bonne soirée et promis je te tiens au courant de l'utilisation de ce travail.

 

[job75]

Re : Renommer des plages nommées

Re,

Sur WinXp-Excel 2003 pas de problème avec les macros du post #5.

Par contre sur Win7-Excel 2010, quand on crée une nouvelle feuille, des noms ne font pas référence à la bonne feuille.

Pour l'instant je n'ai pas de solution, est-ce un bug d'Excel 2010, quid sur Excel 2013 ?

Bonne nuit et A+

 

[job75]

Re : Renommer des plages nommées

Re,

Après réflexion, il est possible que la constatation du post précédent ne pose pas de problème sur Excel 2010.

En fait les noms créés dans toutes les feuilles apparaissent dans le gestionnaire de noms.

Il faudrait vérifier en modifiant les données des plages, ce que je n'ai pas fait.

Edit : je viens de tester sur le nom VDTRg_07_A1, en effet pas de problème...

Donc c'est OK sur Excel 2010.

Re-bonne nuit.

 

[job75]

Re : Renommer des plages nommées

Bonjour Chubby, JC, le forum,

Au lieu de créer systématiquement une nouvelle feuille, on peut laisser le choix d'écraser l'année en cours :

Sub NouvelleAnnée()
Dim t$, rep As Byte
t = ActiveSheet.Name
If Not t Like "####-####*" Then Exit Sub
t = Val(t) + 1 & "-" & Val(t) + 2 & Mid(t, 10)
On Error Resume Next
If IsError(Sheets(t)) Then
  rep = MsgBox("Voulez-vous écraser l'année en cours ?", 3, "Nouvelle année")
  If rep = 2 Then Exit Sub 'bouton Annuler
  Application.ScreenUpdating = False
  If rep = 7 Then ActiveSheet.Copy After:=Sheets(Sheets.Count) 'nouvelle feuille
  ActiveSheet.Name = t
  '---translation des données---
  [BQ:CN].Copy [CO1]
  [AS:BP].Copy [BQ1]
  [U:AR].Copy [AS1]
  '---nouvelles années---
  [A1:AR5].Replace Val(t) - 1, Val(t), xlPart
  [A6:A65000].Replace Val(t), Val(t) + 1 'à voir
  [A6:A65000].Replace Val(t) - 1, Val(t) 'à voir
  '---vidage des données numériques---
  [A6:AR65000].SpecialCells(xlCellTypeConstants, 1).ClearContents
End If
End Sub

Le nombre de noms définis n'augmente pas si l'on ne crée pas une nouvelle feuille.

Fichier (2).

A+

 

[Chubby]

Re : Renommer des plages nommées ... (résolu avec brio!)

Bonsoir Job75, bonsoir JC et les zotres,

Vraiment super boulot Job! J'ai collé sur ta page mes nouvelles données en prenant garde de respecter le nouveau millésime A1 encore inexistant. Et résultat tout gaz parfaitement. ton astuce de nommer les plages avec A1, A2 est super. Elle permet de pouvoir adapter à n'importe quelle année. Vraiment un beau travail qui m'épargne des heures de travail bien peu épanouissant.
J'en ai profité pour avoir toutes les plages nommées, sans exception. J'avais fait court avant ça, arffff.
Je crois que tu m'a tiré une super épine du pied comme à ton habitude.
Bonne soirée à toi et si jamais j'ai un bug (c'est toujours ce qui m'effraye avec les macro) je me permettrais de te contacter.
Mille merci!

 

[job75]

Re : Renommer des plages nommées

Bonjour Chubby, le forum,

La manière dont je faisais la translation des données n'allait pas car il ne faut pas toucher aux formules.

Cette macro doit convenir :

Sub NouvelleAnnée()
Dim t$, rep As Byte, ar, a, c As Range
t = ActiveSheet.Name
If Not t Like "####-####*" Then Exit Sub
t = Val(t) + 1 & "-" & Val(t) + 2 & Mid(t, 10)
On Error Resume Next
If IsError(Sheets(t)) Then
  rep = MsgBox("Voulez-vous écraser l'année en cours ?", 3, "Nouvelle année")
  If rep = 2 Then Exit Sub 'bouton Annuler
  Application.ScreenUpdating = False
  If rep = 7 Then ActiveSheet.Copy After:=Sheets(Sheets.Count) 'nouvelle feuille
  ActiveSheet.Name = t
  '---nouvelles années---
  [1:5].Replace Val(t) - 1, Val(t), xlPart
  [1:5].Replace Val(t) - 2, Val(t) - 1
  [1:5].Replace Val(t) - 3, Val(t) - 2
  [1:5].Replace Val(t) - 4, Val(t) - 3
  [A6:A65000].Replace Val(t), Val(t) + 1 'à voir
  [A6:A65000].Replace Val(t) - 1, Val(t) 'à voir
  '---translation des constantes numériques---
  ar = Array("BQ6:CN65000", "AS6:BP65000", "U6:AR65000")
  For Each a In ar
    Range(a).Offset(, 24).SpecialCells(xlCellTypeConstants, 1).ClearComments
    Range(a).Offset(, 24).SpecialCells(xlCellTypeConstants, 1).ClearContents
    For Each c In Range(a).SpecialCells(xlCellTypeConstants, 1)
      c.Copy c(1, 25)
    Next
  Next
  '---RAZ---
  [A6:AR65000].SpecialCells(xlCellTypeConstants, 1).ClearContents
End If
End Sub

Fichier (3).

A+

 

[Chubby]

Re : Renommer des plages nommées

Bonjour Job, bonjour les zotres,

Je ne capte pas trop la différence entre cette macro et la précédente. Tu parles des formules que j'utilise dans les colonnes D:O? ou d'autres formules?
De sorte de mettre à profit ton travail et ce que j'avais fait entre les deux macro je vais coller ta macro à la place de l'autre. Tout devrait tomber juste je suppose.
Dis m'en plus que je comprenne vraiment de quoi il s'agit.
Pour cette macro, je la copie juste et la colle à la place de l'autre n'est ce pas?
Merci encore à toi.

 

[MJ13]

Re : Renommer des plages nommées

Bonjour à tous

Je n'ai pas trop suivi l'affaire, mais je trouve qu'il y a peut-être une piste simple à creuser avec les touches F3 et Alt+L qui permet normalement de voir tous les noms de la feuille.

A tester, par exemple, si c'est pour changer juste une année, c'est facile à modifier avec Ctrl+H, puis renommer avec Ctrl+Shif+F3.

 

[job75]

Re : Renommer des plages nommées

Re, salut Michel,

Je ne capte pas trop la différence entre cette macro et la précédente. Tu parles des formules que j'utilise dans les colonnes D:O? ou d'autres formules?

Créez une nouvelle année sur le fichier (2) du post #10.

La formule de la cellule U198 qui contient un nom défini est translatée en AS198, ce qui donnera un résultat erroné...

A+