Duplication d'onglet Excel modification des Hyperlinks afin qu'ils ne renvoient plus au fichier d'origine mais au nouveau fichier

Poljol · 4 Février 2020

Bonjour,
Je suis sans recours sur un sujet..
J'ai un créé un onglet type "Projet 1" avec des hyperlinks qui me permettent de me déplacer dans cet onglet sans scroller.
J'ai régulièrement besoin de dupliquer cet onglet. Une fois dupliqué, le nouvel onglet "Projet 2" possède bien les hyperlinks, sauf que les hyperlinks me renvoient aux adresses dans le fichier "Projet 1" alors que je voudrais qu'ils soient automatiquement transférés dans le fichier "Projet 2".
Est-il possible de faire qqchose ?
Merci de votre aide,

danielco · 4 Février 2020

Bonjour,

Je n'arrive pas à reproduire ce problème. Peux-tu donner un exemple ?

Cordialement.

Daniel

Poljol · 4 Février 2020

Bonjour,
Merci de ton retour,
Mettons que tu ouvres un fichier excel vierge et que tu n'aies qu'un seul onglet.

1) tu crees un lien hypertexte qui te renvoies à la cellule AA958 de l'onglet 1 (unique onglet du fichier).
2) tu dupliques l'onglet, le nouvel onglet s'appelle onglet 2
3) le lien hypertexte qui est dans l'onglet 2 renvoie à la cellulle AA958 de l'onglet 1
4) ce que je veux c'est que le lien dans l'onglet 2 renvoie à la cellule AA958 de l'onglet 2 (celui qui est issu de la duplication du premier onglet.

En soit j'ai cette manip à faire sur beaucoup de lien, je ne peux pas modifier les liens un par un.

merci encore de ton aide et de ton temps

Paul

mapomme · 4 Février 2020

Bonjour @Poljol , @danielco,

Considérons la feuille à dupliquer. Sur cette feuille, considérons la cellule H10.

On va insérer en cellule H10, le texte "Vers la première cellule"
En H10, on mettra un lien vers la cellule A1 de la même feuille.

Pour cela, on va saisir la formule suivante en H10:

=LIEN_HYPERTEXTE(STXT(CELLULE("filename";$A$1);CHERCHE("[";CELLULE("filename";$A$1));99) & "!" & CELLULE("address";$A$1);"Vers la première cellule")

où $A$1 est la cellule cible

où "Vers la première cellule" est le texte du lien

Cette cellule peut-être copiée sur une autre feuille (ou bien la feuille peut être dupliquée). Les liens copiés pointeront bien au sein de la feuille copiée ou dupliquée et non vers la feuille origine.

NOTA: les noms des onglets doivent être sans espaces (sinon il faut changer la formule)

Poljol · 4 Février 2020

mapomme à dit:
Bonjour @Poljol , @danielco,

Considérons la feuille à dupliquer. Sur cette feuille, considérons la cellule H10.

On va insérer en cellule H10, le texte "Vers la première cellule"

En H10, on mettra un lien vers la cellule A1 de la même feuille.

Pour cela, on va saisir la formule suivante en H10:
=LIEN_HYPERTEXTE(STXT(CELLULE("filename";$A$1);CHERCHE("[";CELLULE("filename";$A$1));99) & "!" & CELLULE("address";$A$1);"Vers la première cellule")

où $A$1 est la cellule cible
où "Vers la première cellule" est le texte du lien

Cette cellule peut-être copiée sur une autre feuille (ou bien la feuille peut être dupliquée). Les liens copiés pointeront bien au sein de la feuille copiée ou dupliquée et non vers la feuille origine.

Hello merci beaucoup de ta réponse.
quand je duplique l'onglet j'ai un message de référence non valide qui apparait ...

Poljol · 4 Février 2020

mapomme à dit:
Bonjour @Poljol , @danielco,

Considérons la feuille à dupliquer. Sur cette feuille, considérons la cellule H10.

On va insérer en cellule H10, le texte "Vers la première cellule"

En H10, on mettra un lien vers la cellule A1 de la même feuille.

Pour cela, on va saisir la formule suivante en H10:
=LIEN_HYPERTEXTE(STXT(CELLULE("filename";$A$1);CHERCHE("[";CELLULE("filename";$A$1));99) & "!" & CELLULE("address";$A$1);"Vers la première cellule")

où $A$1 est la cellule cible
où "Vers la première cellule" est le texte du lien

Cette cellule peut-être copiée sur une autre feuille (ou bien la feuille peut être dupliquée). Les liens copiés pointeront bien au sein de la feuille copiée ou dupliquée et non vers la feuille origine.

NOTA: les noms des onglets doivent être sans espaces (sinon il faut changer la formule)

je suis désolé mais ca ne fonctionne toujours pas même en mettant un nom d'onglet sans espace ..

mapomme · 4 Février 2020

Poljol à dit:
je suis désolé mais ca ne fonctionne toujours pas même en mettant un nom d'onglet sans espace ..

Effectivement:
J'ai dupliqué ma feuille Feuil1 sur le quel se trouve le lien

Le nom de la feuille dupliquée est: Feuil1 (2) -> le lien ne fonctionne pas
J'ai changé le nom en Feuil1(2) -> le lien ne fonctionne toujours pas
J'ai retiré les parenthèses nom = Feuil12, -> le lien fonctionne
J'ai mis un tiret Feuil1-2 -> le lien ne fonctionne pas
J'ai mis à la place du tiret un 'underscore' Feuil1_2 -> le lien fonctionne.

En fait, il y a pas mal de caractères interdits dans l'écriture des liens.

Je vais étudier une macro pour le faire autrement.

mapomme · 4 Février 2020

Bonsoir @Poljol,

On duplique par macro.

on ajoute un bouton sur la feuille à dupliquer
on associe la macro DupliquerFeuilleEtLiens() à ce bouton
Pour dupliquer la feuille où se trouve le bouton, cliquer sur ce bouton (ne pas utiliser une autre méthode)

La code de la macro se trouve dans Module1.

VB:

Sub DupliquerFeuilleEtLiens()
Dim source As Worksheet, cible As Worksheet, hyp As Hyperlink, Cellule, SousAdresse, Texte

Set source = ActiveSheet
source.Copy After:=Sheets(Sheets.Count)
Set cible = ActiveSheet
For Each hyp In cible.Hyperlinks
   If hyp.SubAddress Like "Feuil" & "*" Then
      Set Cellule = hyp.Range
      SousAdresse = Replace(hyp.SubAddress, "Feuil1", "'" & ActiveSheet.Name & "'")
      Texte = hyp.TextToDisplay
      hyp.Delete
      cible.Hyperlinks.Add Anchor:=Cellule, Address:="", SubAddress:=SousAdresse, TextToDisplay:=Texte
   End If
Next hyp
End Sub

Poljol · 5 Février 2020

mapomme à dit:
Bonsoir @Poljol,

On duplique par macro.

on ajoute un bouton sur la feuille à dupliquer

on associe la macro DupliquerFeuilleEtLiens() à ce bouton

Pour dupliquer la feuille où se trouve le bouton, cliquer sur ce bouton (ne pas utiliser une autre méthode)

La code de la macro se trouve dans Module1.

VB:

Sub DupliquerFeuilleEtLiens() Dim source As Worksheet, cible As Worksheet, hyp As Hyperlink, Cellule, SousAdresse, Texte Set source = ActiveSheet source.Copy After:=Sheets(Sheets.Count) Set cible = ActiveSheet For Each hyp In cible.Hyperlinks If hyp.SubAddress Like "Feuil" & "*" Then Set Cellule = hyp.Range SousAdresse = Replace(hyp.SubAddress, "Feuil1", "'" & ActiveSheet.Name & "'") Texte = hyp.TextToDisplay hyp.Delete cible.Hyperlinks.Add Anchor:=Cellule, Address:="", SubAddress:=SousAdresse, TextToDisplay:=Texte End If Next hyp End Sub

MERCI BEAUCOUP CA FONCTIONNE !!!!

Poljol · 5 Février 2020

Poljol à dit:
MERCI BEAUCOUP CA FONCTIONNE !!!!

Merci encore, en revanche je rencontre un problème. Je ne peux pas modifier le nom des onglets. Lorsque je le fais les liens ne fonctionnent plus...

mapomme · 5 Février 2020

Bonjour @Poljol ,

Poljol à dit:
Merci encore, en revanche je rencontre un problème. Je ne peux pas modifier le nom des onglets. Lorsque je le fais les liens ne fonctionnent plus...

Je ne pourrai voir cela que ce soir au plus tôt.

Poljol · 5 Février 2020

mapomme à dit:
Bonjour @Poljol ,

Je ne pourrai voir cela que ce soir au plus tôt.

Merci beaucoup, c'est déjà top si tu trouves une solution ce soir.

mapomme · 6 Février 2020

Bonjour @Poljol ,

Poljol à dit:
Je ne peux pas modifier le nom des onglets. Lorsque je le fais les liens ne fonctionnent plus...

En fait, la macro n'a rien à voir dans ce comportement. Dans le fichier joint (sans aucune macro), 3 liens ont été crées sur Feuil1.

Si on renomme cette feuille "toto" (par exemple), les liens sont HS (erreur : "Référence non valide.").

Si on renomme la feuille "Feuil1", les liens sont à nouveau opérationnels.

Pour l'instant, ça me laisse perplexe

mapomme · 6 Février 2020

Bonjour @Poljol ,

Bien, après des recherche sur la toile, je n'ai pas détecté de solution satisfaisante concernant le renommage des onglets et la corruption des liens.

Je me suis résolu à créer ma propre fonction de lien au sein de la même feuille de sorte que:

les liens de la feuille dupliquée pointent bien vers cette nouvelle feuille
on puisse renommer les feuilles sans perte de ces liens

Dans la feuille dite "Modèle" que vous dupliquerez par la suite, insérez des pseudo-liens à l'aide de la fonction personnalisée: =TexteEtLien("Vers toto";A6)

Cette fonction affiche dans la cellule où elle se trouve le texte Vers toto. Le lien quand on sélectionnera cette cellule sera la cellule A6 (de la même feuille). Cette fonction ne fait rien de plus.

Pour dupliquer cette feuille "Modèle", passez obligatoirement par le menu contextuel des onglet:

Clique-droit sur l'onglet de la feuille à dupliquer
puis choisir le menu "Déplacer ou copier…"

Pendant la conception de votre feuille "Modèle", il est gênant de ne pas pouvoir sélectionner les cellules avec la fonction TexteEtLien. Pour inhiber temporairement le fonctionnement de ces liens:

rendez vous dans le module "Module1"
attribuez la valeur TRUE à la constante MiseAuPoint (les liens sont inhibés)
n'oubliez pas ensuite de repasser la constante MiseAuPoint à FALSE pour réactiver les liens

D'autres explications se trouvent dans le fichier joint.

Dans l'attente d'un retour,

Poljol · 7 Février 2020

C'est vraiment génial merci beaucoup.
Votre temps m'aide beaucoup, je suis très reconnaissant !

Duplication d'onglet Excel modification des Hyperlinks afin qu'ils ne renvoient plus au fichier d'origine mais au nouveau fichier

Poljol

XLDnaute Nouveau

danielco

XLDnaute Accro

Poljol

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

Pièces jointes

Poljol

XLDnaute Nouveau

Poljol

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Poljol

XLDnaute Nouveau

Poljol

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

Poljol

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

Poljol

XLDnaute Nouveau