Cloner une feuille en renommant tous ses objets

Le 01/04/2020 à 22:36

Salut le forum !

Je souhaite cloner une feuille de calcul Excel tout en renommant la quasi totalité des objets reliées à elle.

En fouinant un peu, j'ai trouvé quelques explications données par Microsoft sur le sujet. Ces explications ne parlent que du clonage de la feuille comme illustré ci-après:

[quote Microsoft]Cet exemple montre comment copier Feuil1 et placer la copie après Feuil3.

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

[/quote]

C'est déjà un bon départ. Mais, comment arriver à renommer tous les objets (tableau, références structurées, plages nommées, etc) de la feuille copiée par VBA pendant ce clonage ?

Merci de bien vouloir me répondre

Le 01/04/2020 à 23:48

Bonjour,

pour les Tableau, c'est simples, ils seront renommés automatiquement.

Pour tes noms classiques il faut les créer au niveau Feuille et non Classeur lors de la création :

2020 04 01 23 45 11

Tes noms seront dupliqués au niveau de la nouvelle feuille, et tous de niveau feuille.

En vba il faudra préfixer tes noms avec le nom de la feuille voulue :

'Feuil1'!ton_nom qu'il auquel tu t'adresses

eric

Le 02/04/2020 à 17:19

Merci Eriiic ! Excuse pour le retard de ma réponse

Alors, tu disais

eriiic a écrit :

Tes noms seront dupliqués au niveau de la nouvelle feuille, et tous de niveau feuille.

eric

Oui ! Le code Microsoft duplique tous les objets au niveau Feuille.

Cependant, dans le cas d'un classeur à 2 feuilles avec des calculs utilisant des variables référencées de l'une vers l'autre des feuilles, cela devient moins évident.

Prenons le cas d'un classeur a deux feuilles Feuil1 et Feuil2 que nous considererons comme model (ou gabarit) pour le clonage:

La cellule Feuil1!A1 est renommé Prix_Unitaire;

La cellule Feuil1!A2 est renommé Quantité;

Si en Feuil2!A1, on écrit la formule =Prix_Unitaire * Quantité, alors cette cellule affichera bien le produit escompté en regard des valeur saisies dans Feuil1. N'est-ce pas ?

Maintenant, utilisons le code VBA pour cloner les deux feuilles. On aura (comme dit plus haut) deux autres feuilles supplémentaires (Feuil3 et Feuil4 copies respectives de Feuil1 et Feuil2) dans ce même classeur.

Et même si les noms sont clonés aussi, Prix_Unitaire et Quantité n'auront qu'une portée restreinte à la Feuille dont elles dépendent directement. Ici il s'agit de Feuil1.

Ainsi, si nous modifions la valeur de A1 dans Feuil3 et que nous regardons en A1 dans Feuil4, la formule Prix_Unitaire * Quantité qui y est affichée n'est pas le montant en rapport avec Feuil3. Mais ici, ce sera encore le résultat de Feuil1!Prix_Unitaire * Feuil1!Quantité qui serait calculé.

Voyez-vous l'enjeu du traitement que je demande ?

En principe, l'on aurait souhaité avoir une formules en A1 de Feuil4 qui fasse référence aux données de la Feuil3 comme le fait A1 de Feuil2 sur A1 et A2 de Feuil1. Alors que faire ?

On est ainsi obligé de modifier/remplacer manuellement la formule de A1 dans Feuil4 par Feuil3!Prix_Unitaire * Feuil3!Quantité pour que tout rentre dans l'ordre.

Encore qu'ici, il n'y ait qu'une formule à modifier. Imaginez vous avoir cloné des feuilles qui regorgent des dizaines voir des centaines de formules, avec leurs variables structurées, à modifier à la main....

Hé binh ... c'est là tout l'enjeu du traitement en VBA que je demande.

Une piste serait de lancer une boucle

Sub ClonerGabarit()
Dim n As Byte, nomDeVariable As Name : n = Worksheets.Count

    Sheets(1).Copy After:=Sheets(n)           ' on copy la feuille gabarit 1
    Sheets(2).Copy After:=Sheets(n + 1)       ' on copy la feuille gabarit 2

    For Each feuille In ThisWorkBook.WorkSheets(array( 1 , 2 ))
        For Each nomDeVariable In feuille.Names 
         '... ici le code de modification de la variable, de son contexte, ...
         Next nomDeVariables
     '... ici on modifie les formules des nouvelles feuilles copiées
     Next feuille
End Sub

Voilà ! c'est l'idée que je nourri depuis hier mais que je n'arrive pas à transcrire en VBA

Si quelqu'un a l’expérience nécessaire pour le réaliser, je suis preneur

Merci et excusez pour la lecture

Le 02/04/2020 à 17:52

Bonjour,

parce que tu n'as pas fait ce que je te disais : créer tes noms au niveau feuille

Et ça sur toutes, y compris celles d'origine. Obligation de passer par le gestionnaire de nom. Tu les supprimes tous avant de les créer correctement.

Déjà parce que tu n'as pas le choix, ensuite parce que c''est dangereux de mélanger le même nom au niveau feuille et au niveau classeur.

En restant sur la même feuille tu n'es pas obligé de l'indiquer dans le nom. S'il ne trouve pas ce que tu penses lui indiquer, excel va se rabattre sur le nom niveau classeur et ne te fera pas d'erreur. Et toi tu n'y verras que du feu, en utilisant une valeur erronée se trouvant sur une autre feuille...

Et ta formule doit être :

=Feuil1!Prix_Unitaire * Feuil1!Quantité

Ce que tu m'as mis montre que tu es resté au niveau classeur

De plus, comme tes feuilles sont liées entre elles, il faut faire une sélection 3D avant de les copier :

clic sur la première, shift+clic sur la dernière pour sélectionner toutes celles qui sont liées.

Ensuite tu peux faire ta copie 3D.

Ta formule sur la copie devient :

='Feuil1 (2)'!Prix_Unitaire * 'Feuil1 (2)'!Quantité

Ensuite libre à toi de renommer ta 'Feuil1 (2)' 'toto'', la formule suivra.

Copie 3D en vba :

Sub test()
    Sheets(Array("Feuil1", "Feuil2")).Copy After:=Sheets(Sheets.Count)
End Sub

eric

5classeur1.xlsm (17.66 Ko)
Le 02/04/2020 à 20:57

Waouh ! Ça marche comme sur des roulettes...

Très bien vu l'idée de créer les noms au niveau de la feuille via le Gestionnaire des noms

Wao ! je suis très heureux ! Alors, sujet résolu !

Merci infiniment Eriiic !

A+

Le 06/04/2020 à 00:01

Juste pour faire un retour d'expérience. Alors, tu disais

eriiic a écrit :

parce que tu n'as pas fait ce que je te disais : créer tes noms au niveau feuille

Et ça sur toutes, y compris celles d'origine. Obligation de passer par le gestionnaire de nom. Tu les supprimes tous avant de les créer correctement.

J'étais écœuré d'être obligé de supprimer entièrement puis à recréer entièrement de nouveau tous les noms dans le gestionnaire de nom. Cependant, j'ai remarqué que le code de copie fonctionnait en créant les mêmes noms mais au niveau feuille. Du coup, plutôt que d'effacer tous les noms puis les recréer manuellement, je me suis fais aider par la fonction de copie de feuille. Voici la procédure :

1) Lancer la fonction de copie. Elle recrée l'ensemble des feuilles avec des noms au niveau feuille;

2) Modifier l'ensemble des formules dans les feuilles copies par des références niveau feuille du genre Feuiln!Prix_Unitaire * Feuiln!Quantité pointant entre elles (les feuilles copies);

3) Supprimer les feuilles d'origine pour ne conserver que les copies.

Et, voilà ! moins de travail pour un résultat très très satisfaisant. Et tout ça grâce à tes conseils !

Merci infiniment Eriiic

Le 06/04/2020 à 00:08

Bonjour,

merci pour le retour mais tu aurais dû lire mon point de vue là dessus :

c''est dangereux de mélanger le même nom au niveau feuille et au niveau classeur.

En restant sur la même feuille tu n'es pas obligé de l'indiquer dans le nom. S'il ne trouve pas ce que tu penses lui indiquer, excel va se rabattre sur le nom niveau classeur et ne te fera pas d'erreur. Et toi tu n'y verras que du feu, en utilisant une valeur erronée se trouvant sur une autre feuille...

Mais bon, si tu es sûr à 100% que ça n'arrivera jamais et que jamais personne n'en supprimera...

Tu pouvais le faire par macro aussi

eric

Rechercher des sujets similaires à "cloner feuille renommant tous objets"