Discussion :

[GSFBC]

Bonjour,

Avant toute chose, sachez que j'ai commencé à travailler pour la première fois de ma vie sous VBA ce matin. Je ne connais que très peu de fonctions et je ne suis pas non plus un grand habitué de la programmation, donc mon code est très loin d'être optimisé. Si vous avez le courage de me corriger sur l'optimisation en plus de mon problème, j'en serai ravi


Mon problème donc :
Je cherche à copier-coller la valeur d'une cellule d'une feuille nouvellement créée par ma macro, vers une cellule d'une feuille déjà existante.

J'ai déclaré trois variables String, je leur donne la valeur de la cellule correspondante, puis j'affecte la valeur de cette variable aux cellules dans lesquelles je veux les coller.
Pour la première variable tout marche très bien mais pour les deux autres, la variable semble vide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
 
Var1 = ActiveSheet.Range("D3").Name
Var2 = ActiveSheet.Range("D4").Name
Var3 = ActiveSheet.Range("D5").Name 'les cellules D3, D4 et D5 contiennent du texte au format "standard"
Sheets("Liste projets").Select 'La feuille "Liste projets" est celle où se trouvent les cellules où je veux coller mes valeurs
ActiveSheet.Range(Nom).Select 'La cellule "Nom" correspond à une autre variable déclarée et affectée en amont. Son emplacement est variable.
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Var1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Var2
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Var3

Dans ma feuille "Liste projets", seule la valeur de Var1 est collée dans sa cellule de destination. Les Var2 et Var3 remplacent le contenu de la cellule de destination par du vide, je suppose donc qu'elles sont vides alors qu'elles devraient contenir respectivement le texte des cellules D4 et D5.


Quelqu'un voit le problème ?

[Menhir]

Je pense que ton problème viens du fait que tu confonds "Name" et Value" dans les trois lignes du début qui renseignent tes variables.
De plus, tes trois assignation finales pointent sur la même cellule, celle à droite de ta cellule "Nom". Donc une seule cellule sera modifier. Il faut comprendre que Offset ne déplace pas la sélection mais pointe sur une cellule déportée, ce qui n'est pas la même chose.

Il y a beaucoup plus simple pour faire ça.

D'une manière générale, autant que tu le peux, je te conseille d'éviter les Select : ça fait perdre du temps en exécution, ça alourdit le code et ça donne souvent un affichage désagréable à l'exécution.

Quand tu veux copier la valeur d'une cellule dans une autre, tu utilise la synthaxe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Workbooks("MonClasseur2").Worksheets("mafeuille2".cells(X2,Y2).Value = Workbooks("MonClasseur1").Worksheets("mafeuille1".cells(X1,Y1).Value

Si la source est le classeur actif, tu peux éviter le premier Workbooks.
Si en plus la feuille source est active, tu peux aussi éliminer le premier Worksheets.

Bien sûr, la réciproque s'applique si c'est le classeur et la feuille destination qui sont actifs.

Tu peux remplacer les "MonClasseur" et "MaFeuille" par des variables String ou mieux, remplacer carément les Workbooks("MonClasseur") et Worksheets("MaFiche") par des variables de type Workbook et Worksheet.

Je te conseille aussi, lorsque tu pointe sur une seule cellule, d'utiliser Cells plutôt que Range.

Bref, ton code devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sheets("Liste projets").Range(Nom).Offset(0, 1).Value = ActiveSheet.Cells(3,4).Value
Sheets("Liste projets").Range(Nom).Offset(0, 1).Value = ActiveSheet.Cells(4,4).Value
Sheets("Liste projets").Range(Nom).Offset(0, 1).Value = ActiveSheet.Cells(5,4).Value

Je suis sûr qu'il est possible de simplifier ".Range(Nom).Offset(0, 1)" mais ne sachant pas comment est constitué "Nom", je ne sait pas quel serait le code.

Mais je pense que ça ne fera pas ce que tu souhaites parce que c'est la même cellule qui est modifiée trois fois.
A mon avis, dans les Offset, il faudrait remplacer les "1" des lignes 2 et 3 par des "2" et "3".

Mais, comme je le disais, il doit être beaucoup plus simple de faire ça en remplaçant le Range + Offset par un simple Cells.

[GSFBC]

Effectivement ça me ferait économiser pas mal de lignes mais ça ne fonctionne pas du tout

J'explique un peu plus en détail ce que je veux faire :

J'ai une feuille "Liste projets" dans laquelle je référence des projets de bâtiment par leur nom, leur zone climatique, la réglementation auxquels ils sont soumis, et l'usage prévu pour le bâtiment.

Lorsque j'entre le nom d'un nouveau projet dans la cellule "Nom", une nouvelle feuille est créée (copie d'une feuille "Modèle vide") dans laquelle l'utilisateur remplit de nombreuses informations DONT la zone climatique, la réglementation et l'usage. Par la suite, je veux que ces trois informations soient reportées dans ma feuille "Liste projets" dans les cellules décalées respectivement d'une, deux et trois colonnes par rapport à la cellule "Nom" qui contient le nom du projet que je viens d'entrer. Les informations doivent se mettre à jour en dynamique, si je les modifie dans la feuille nouvellement créée, elles doivent se mettre à jour dans la feuille "Liste projets".

J'ai entré le code que tu m'as suggéré (J'ai effectivement remplacé les "1" des lignes 2 et 3 par des "2" et "3" pour obtenir ce que je veux), mais là rien ne change dans ma feuille "Liste projets". Rien n'apparaît, rien ne disparaît, rien ne change.

Je ne pense pas qu'on puisse réduire le Range + Offset parce que "Nom" est une variable qui peut correspondre à plusieurs cellules différentes, je suis obligé de m'en servir comme référence. Pour info, la variable "Nom" est également reportée dans le titre de la nouvelle feuille.


Voici mon code complet, j'ai sauté deux lignes au début de la partie qui me pose problème.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim Nom As String
Dim NewSheet As Worksheet
Dim ZoneCli As String
Dim Usage As String
Dim Reg As String
 
If Target.Column <> 1 Then Exit Sub 'Si on clique sur une autre colonne que la A, ne rien faire
On Error Resume Next
Nom = Target.Value
If Nom = "" Then Exit Sub 'Si la case cliquée est vide, ne rien faire
If Nom = "Projet" Then Exit Sub 'Si on clique sur la case du titre, ne rien faire
For Each NewSheet In Worksheets
    If NewSheet.Name = Nom Then
        Sheets(Nom).Select 'Si la case cliquée correspond à une feuille déjà existante, atteindre cette feuille
        Exit Sub
    End If
Next NewSheet
 
Sheets("Modèle vide").Copy after:=Sheets(2) 'Copier la feuille "modèle vide" sur une nouvelle feuille
ActiveSheet.Name = Nom 'Renommer la nouvelle feuille avec le contenu de la cellule cliquée
Sheets(Nom).Unprotect 'Lever la protection de la nouvelle feuille
 
 
'ICI la partie qui me pose problème
 
Sheets("Liste projets").Range(Nom).Offset(0, 1).Value = ActiveSheet.Cells(7, 4).Value 'L'emplacement de mes cellules sources à changé entretemps
Sheets("Liste projets").Range(Nom).Offset(0, 2).Value = ActiveSheet.Cells(9, 4).Value
Sheets("Liste projets").Range(Nom).Offset(0, 3).Value = ActiveSheet.Cells(8, 4).Value
 
 
End Sub

J'ai aussi essayé de remplacer ActiveSheet.Cells(7, 4).Value par Sheets(Nom).Cells(7, 4).Value mais là encore ça ne change rien (la feuille dont je tire mes données s'appelle aussi "Nom").

Edit :

Après quelques tests, je viens de comprendre aussi que pour que ma cellule se mette à jour de manière dynamique, je dois affecter le nom de ma cellule source en valeur de ma cellule de destination. Sinon seule la valeur est copiée, mais quand la cellule source est modifiée, celle de destination ne l'est pas.

Mais sinon le problème ne change pas, dans le cas présent rien n'est collé nulle part.

[Menhir]

Tu devrais mettre en pièce jointe un fichier modèle parce que là, je ne parviens pas à tirer de tes explications les informations nécessaires.

Il est sûr que le code que je t'ai donné ne peut pas fonctionné puisque "Nom" désigne un nom de Worksheet et non une plage comme pouvait le laisser supposer le fait que tu le mettes dans un Range.

Et commence par virer de ton code le On Error Resume Next. Quand on est en phase de développement, on ne vire pas les messages d'erreur qui peuvent apporter une aide précieuse.

[GSFBC]

Pour le "On Error Resume Next" j'avais pompé un bout de code que j'ai trafiqué et ça en faisait déjà partie, donc comme ça marchait très bien j'ai préféré ne rien changer^^' Mais maintenant que je sais à quoi ça sert, ok je l'ai retiré.

"Nom" désigne d'abord la valeur d'une cellule, cette valeur est ensuite reportée en tant que nom de feuille, donc j'ai deux entités différentes, le nom d'une feuille et la valeur d'une cellule qui portent "Nom".

J'ai mis en pièce jointe le fichier avec mon code original qui fonctionnait seulement pour la première variable mais pas pour les deux autres. L'idée est que dans la feuille "Liste projets", on écrive le nom d'un nouveau projet dans la colonne "Projet", puis il faut double-cliquer sur la cellule où on vient d'écrire un nom pour créer une feuille à remplir portant ce même nom. J'ai configuré de sorte que la nouvelle feuille soit préremplie pour l'exemple.

J'ai mis deux exemples "TEST" et "TESTI" pour lesquels ça fonctionne en dynamique, lorsque je change une donnée sur la feuille "Nom" (qui s'appelle "TEST" ou "TESTI" ici), ça modifie automatiquement la donnée "Zone climatique" sur la feuille "Liste projets", mais pas les données "Usage, ni "RT en vigueur" alors que ça devrait. Remarque que lorsque j'essaie de remplacer les "select" par un code moins lourd, plus aucune des trois données n'est reportée.

Je patauge là.

[Menhir]

Essaye ça, ça devrait le faire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
   Dim Nom As String
   Dim NewSheet As Worksheet
   Dim Ligne as Integer
 
   If Target.Column <> 1 Then Exit Sub 'Si on clique sur une autre colonne que la A, ne rien faire
 
   Nom = Target.Value
   If Nom = "" Then Exit Sub 'Si la case cliquée est vide, ne rien faire
   If Nom = "Projet" Then Exit Sub 'Si on clique sur la case du titre, ne rien faire
 
   For Each NewSheet In Worksheets
       If NewSheet.Name = Nom Then
           Sheets(Nom).Select 'Si la case cliquée correspond à une feuille déjà existante, atteindre cette feuille
           Exit Sub
       End If
   Next NewSheet
 
   Ligne = Target.Row
 
   Sheets("Modèle").Copy after:=Sheets(2) 'Copier la feuille "modèle" sur une nouvelle feuille
   ActiveSheet.Name = Nom 'Renommer la nouvelle feuille avec le contenu de la cellule cliquée
   Sheets(Nom).Unprotect 'Lever la protection de la nouvelle feuille
 
   Worksheets(Nom).cells(7,4).Value = Worksheets("Liste projets").cells(Ligne,2)
   Worksheets(Nom).cells(8,4).Value = Worksheets("Liste projets").cells(Ligne,3)
   Worksheets(Nom).cells(9,4).Value = Worksheets("Liste projets").cells(Ligne,4)
 
End Sub

Cela dit, c'est faisable aussi sans macro (excepté la copie et le nom de l'onglet).
On peut mettre dans les trois cellules du modèles une formule qui utilise le nom de l'onglet pour aller rechercher la bonne ligne dans le tableau source et en récupérer la valeur qui va bien. L'avantage d'une formule, c'est que ça resterait dynamique (une modification des données sources serait immédiatement retranscrite dans la feuille fille).

[GSFBC]

Je comprends ton code mais je veux copier-coller en sens inverse, le tableau source est dans la feuille "Nom" et la destination est dans la feuille "Liste projet". Mais même en inversant ça fonctionne mais ça reste statique et j'ai besoin que ça soit dynamique. Si ça ne fait que copier les valeurs au moment de la création du tableau (donc lorsqu'il est vide) ça ne résout pas mon problème :/

Du coup, l'idée de la formule est intéressante mais comme la feuille source a un nom variable il faut que ce soit ma macro qui l'écrive.

J'ai essayé comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "=Nom!$D$7"

Mais il n'a pas l'air d'accepter le "Nom", est-ce qu'il existe une syntaxe pour intégrer une variable déclarée dans l'écriture d'une formule ?


Edit :

Après une petite recherche sur la syntaxe des variables dans les formules, je pensais tenir le bon bout en écrivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "=" & Nom & "!" & "$D$7"

Mais j'obtiens une "Erreur 1004 : Erreur définie par l'application ou par l'objet". Sachant que "Nom" est un String, je ne vois pas ce qui cloche.

[Menhir]

Cela confirme que décrire correctement le problème, c'est 50% de la solution.

Mais même en inversant ça fonctionne mais ça reste statique et j'ai besoin que ça soit dynamique.

Dans ce cas, il ne faut pas copier une valeur mais il faut coller dans ton tableau une formule.
Dans ce cas, passer par une macro n'a plus d'intérêt.

Du coup, l'idée de la formule est intéressante mais comme la feuille source a un nom variable il faut que ce soit ma macro qui l'écrive.

Non, ce n'est pas une obligation. Il est possible de récupérer le nom de l'onglet dans la première colonne.
Regarde dans l'aide Excel la fonction ADRESSE().

Par contre, je ne pense pas qu'il soit possible de renseigner la liste des onglets dans ta colonne 1 automatiquement sans macro.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "=Nom!$D$7"

Mais il n'a pas l'air d'accepter le "Nom", est-ce qu'il existe une syntaxe pour intégrer une variable déclarée dans l'écriture d'une formule ?

Sort Nom des guillemet. Ta formule doit indiquer le contenu de la variable et non le texte "Nom".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "="&Nom&"!$D$7"

[GSFBC]

J'ai testé la fonction ADRESSE() mais elle ne fait que récupérer l'adresse d'une cellule, pas sa valeur, donc ce n'est pas ce que je veux faire (à moins de stocker les adresses voulues puis d'utiliser ma macro pour les copier-coller dans une formule ?)

J'ai essayé la syntaxe que tu as indiqué :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "="&Nom&"!$D$7"

mais j'obtiens "Erreur de syntaxe", et si j'essaie d'ajouter des espaces

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "=" & Nom & "!$D$7"

J'ai "Erreur définie par l'application ou par l'objet".


Désolé que mon problème soit plus compliqué que prévu et merci pour ton acharnement

[Menhir]

J'ai testé la fonction ADRESSE() mais elle ne fait que récupérer l'adresse d'une cellule, pas sa valeur

Je me suis trompé : ce n'est pas ADRESSE mais INDIRECT.

[GSFBC]

J'ai déclaré une variable "Formule" en tant que String :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formule = "=" & Nom & "!$D$7"

Puis j'ai tenté :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2).FormulaR1C1 = "=" & Formule

Mais j'obtiens toujours le même message d'erreur.


Après moult tests, j'ai fini par trouver une solution au problème mentionné dans mon tout premier post. La cellule D7 avait été renommée à mon insu alors que D8 et D9 non (clic droit, définir un nom). Je les ai renommées avec des noms au hasard et ma macro veut bien me les copier en dynamique...Je ne comprends pas pourquoi mais au moins ça marche.

Voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ZoneCli = Worksheets(Nom).Cells(7, 4).Name
   Worksheets("Liste projets").Cells(Ligne, 2) = ZoneCli
   Usage = Worksheets(Nom).Cells(8, 4).Name
   Worksheets("Liste projets").Cells(Ligne, 3) = Usage
   RT = Worksheets(Nom).Cells(9, 4).Name
   Worksheets("Liste projets").Cells(Ligne, 4) = RT

Remarque encore une fois que si je me passe des variables et que j'écris directement ceci, ça me renvoie l'adresse de la cellule source au lieu de sa valeur, et ça je ne comprends pas non plus.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Liste projets").Cells(Ligne, 2) = Worksheets(Nom).Cells(7, 4).Name

Mais bon le problème est résolu, merci de ton aide


Edit :

Je me suis trompé : ce n'est pas ADRESSE mais INDIRECT

Ah, du coup je vais essayer, ça permettra au moins d'alléger le code x)

Merci encore une fois !