Sheets ou pas sheets avant Range(Cells())

Version imprimable

28/05/2020, 22h35
laurent_42

Sheets ou pas sheets avant Range(Cells())

Bonjour,

Cher forum, je n'ai plus d'autres solutions que m'en remettre à toi. Merci d'avance !!!!

Je veux copier nb_var colonnes à partir de la colonne 3 (incluse) de la 2ième ligne de la feuille 2 de mon classeur sur la feuille 3 de ce même classeur à partir de la colonne 2 (toujours ligne 2). Par exemple si nb_var=2, je veux copier les cellules C2 et D2 de la feuille 2 sur les cellules B2 et C2 de la feuille 3.
Une première étape dans la macro me permet de définir la valeur de nb_var.

Voici mon code :

Code:

Range(Cells(2, 3), Cells(2, nb_var)).Copy Sheets(3).Range("B2")

Lorsque je lance la macro depuis la feuille 2, ça marche.

Pour éviter de devoir être nécessairement sur la feuille 2, j'ai modifié le code en :

Code:

Sheets(2).Range(Cells(2, 3), Cells(2, nb_var_inc)).Copy Sheets(3).Range("B2")

Et là, il me dit : Erreur 1004 Erreur définie par l'application ou par l'objet.

Et là... et là... ben je comprends plus rien !!! Pouvez-vous me dire où je fais une bêtise ? Si en plus vous pouvez me dire ce que je ne comprends pas, là c'est le top !!!

Merci vraiment beaucoup...

Bonne soirée/journée
28/05/2020, 22h53
anasecu

Bonjour,

Citation:

Envoyé par laurent_42

Et là... et là... ben je comprends plus rien !!!

Ce que tu ne dis pas c'est où est situé ton code.
S'il est dans un module cela devrait fonctionner mais je pense que ton code est dans la 'sheets(2)'.

Ce n'est d'ailleurs pas très sécurisant d'utiliser les index des onglets car s'ils sont déplacés ton code risque de ne pas faire ce que tu souhaites.
28/05/2020, 23h10
laurent_42
Merci pour cette 1ère réponse.

Citation:

Envoyé par anasecu

Ce que tu ne dis pas c'est où est situé ton code.
S'il est dans un module cela devrait fonctionner mais je pense que ton code est dans la 'sheets(2)'.

Ben si : mon code est bien dans un module de mon classeur.

Citation:

Envoyé par anasecu

Ce n'est d'ailleurs pas très sécurisant d'utiliser les index des onglets car s'ils sont déplacés ton code risque de ne pas faire ce que tu souhaites.

Effectivement : en recopiant mon code, j'ai remplacé les noms des feuilles par leur index. Dans mon vrai code, j'ai :

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var)).Copy Sheets("Data_analyse").Range("B2")

Je suis passé par un Range(Cells()) parce que je ne dispose que du nombre de colonnes à copier. Mais si vous avez mieux à me proposer, je suis preneur.

Je précise qu'avec une boucle For sur les colonnes, ça marche.
Code:

1 2 3 For i = 1 To nb_var Sheets("Data_triee").Cells(2, 2 + i).Copy Sheets("Data_analyse").Cells(2, i + 1) Next i
Mais j'aurais bien aimé éviter une boucle For...

Merci de votre aide quoiqu'il en soit.

Bonne soirée
28/05/2020, 23h21
Philippe Tulliez

Bonjour,
Dans la ligne de code que tu as publiée, tu ne relies pas les objets Cells à leur parent Sheets(2). C'est la raison du message d'erreur que tu obtiens dans le cas où cette feuille n'est pas la feuille active

Citation:

Code:

Sheets(2).Range(Cells(2, 3), Cells(2, nb_var_inc)).Copy Sheets(3).Range("B2")
28/05/2020, 23h42
laurent_42

Oh punaise !!! Alors ça !!! Je confirme, je ne comprends plus rien ! :calim2:

Je pensais que le Sheets(2) précédant le Range permettait de lier les cellules à la feuille à partir de laquelle je veux travailler. Donc j'en conclus que le Sheets(2) précédant le Range est inutile.

Je viens de faire le test et effectivement :

Code:

Range(Sheets("Data_triee").Cells(2, 3), Sheets("Data_triee").Cells(2, nb_var)).Copy Sheets("Data_analyse").Range("B2")

fonctionne.

Vraiment quelque chose m'échappe !!!

Mais merci infiniment.

(Je mets la discussion en résolu mais si quelqu'un se sent le courage de m'expliquer le pourquoi du comment, je suis preneur !!!)

Bonne soirée

Bonjour,

Citation:

si quelqu'un se sent le courage de m'expliquer le pourquoi du comment, je suis preneur !!!

Voici l'explication avec comme exemple la copie de la plage B4:F11 de la feuille nommée db vers la cellule A1 de la feuille nommée Recap
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub t() Dim shtSource As Worksheet Dim shtTarget As Worksheet With ThisWorkbook Set shtSource = .Worksheets("db") Set shtTarget = .Worksheets("Recap") End With ' Copie la plage B4:F11 de la feuille source With shtSource .Range(.Cells(4, 2), .Cells(11, 6)).Copy shtTarget.Range("A1") End With ' Set shtSource = Nothing: Set shtTarget = Nothing End Sub
Si l'on ne précise pas le parent de Cells(4, 2) et de Cells(11, 6) ET que la feuille active n'est pas la feuille nommée db les objets Cells auront implicitement comme parent la feuille active autrement dit en supposant que la feuille active soit la feuille cible et que nous aurions écrit
Code:

1 2 3 With shtSource .Range(Cells(4, 2), Cells(11, 6)).Copy shtTarget.Range("A1") End With
Cela revient à écrire ceci, et inévitablement c'est l'erreur
Code:

1 2 3 With shtSource .Range(ShtTarget.Cells(4, 2), ShtTarget.Cells(11, 6)).Copy shtTarget.Range("A1") End With
D'où l'importance de préciser la parentalité complète des objets pour lever toute ambiguïté

29/05/2020, 10h50
Patrice740
Bonjour le Fil,

Citation:

Envoyé par laurent_42

Voici mon code :

Code:

Range(Cells(2, 3), Cells(2, nb_var)).Copy Sheets(3).Range("B2")

Lorsque je lance la macro depuis la feuille 2, ça marche.
Pour éviter de devoir être nécessairement sur la feuille 2 ....

Pour compléter l'explication de Philippe (que je salue) et confirmer «l'importance de préciser la parentalité complète des objets pour lever toute ambiguïté» (Sic.)

Avec, pour exemple le premier code de ton post 1 :

Quand la feuille 2 est active, l'écriture simplifiée peut être la suivante (déconseillée) :

Code:

Range(Cells(2, 3), Cells(2, nb_var)).Copy Worksheets(3).Range("B2")

Le Range et les deux Cells dont la parentalité n'est pas précisée sont rattaché à la feuille active, c'est à dire la feuille 2.

Quand la feuille 3 est active, l'écriture simplifiée peut être la suivante (déconseillée) :

Code:

Worksheets(2).Range(Worksheets(2).Cells(2, 3), Worksheets(2).Cells(2, nb_var)).Copy Range("B2")

Le Range dont la parentalité n'est pas précisée est rattaché à la feuille active, c'est à dire la feuille 3.

La bonne syntaxe pour s'affranchir des feuilles actives est la suivante :

Code:

Worksheets(2).Range(Worksheets(2).Cells(2, 3), Worksheets(2).Cells(2, nb_var)).Copy Worksheets(3).Range("B2")

Qu'on peut simplifier en utilisant With et le point devant Range et Cells, comme l'indique Philippe :
Code:

1 2 3 With Worksheets(2) .Range(.Cells(2, 3), .Cells(2, nb_var)).Copy Worksheets(3).Range("B2") End With
Mais attention, tous les codes précédents travaillent avec le classeur actif.
Lorsque le classeur sur lequel on veut opérer n'est pas actif il faut préciser la parentalité jusqu'au classeur :
Code:

1 2 3 4 5 With Workbooks("MonClasseur") With .Worksheets(2) .Range(.Cells(2, 3), .Cells(2, nb_var)).Copy .Worksheets(3).Range("B2") End With End With
Attention, penser au point devant Worksheets(3)

Note : les objets Range et Cells n'appartiennent pas à une feuille Sheets(x), mais à une feuille de calcul Worksheets(x).

J'espère que ces explications te permettrons d'éclairer ta lanterne.
29/05/2020, 11h45
laurent_42

Bonjour,

Merci infiniment pour ces explications.

OK pour la définition de la parentalité et l'importance de cette définition, pas de problème avec ça. D'ailleurs ma première instruction était (mais effectivement je ne vous ai pas fait l'historique de tous mes tests. :oops:) :

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc)).Copy Sheets("Data_analyse").Range("B2")

(Je n'ai pas précisé le classeur puisque je lance la macro à partir du classeur sur lequel je travaille).

Mais cette instruction ne marche pas. Vue l'erreur générée et en cherchant à comprendre sa source, j'ai "essayé" de supprimer la parentalité de la source de la copie (donc j'ai supprimé le Sheets("Data_triee") et j'ai lancé pour les besoins de mon test la macro à partir de cette feuille Data_triee. Ca m'a permis de valider le reste de l'instruction. Mais dans le contexte de ma macro, il n'est juste pas possible de ne pas préciser cette parentalité, je suis absolument d'accord avec vous. C'est d'ailleurs pour cette raison que j'ai posé la question sur le forum.

Mais ce que je persiste à ne pas comprendre c'est pourquoi la définition de la parentalité du range ne fonctionne pas. Avec

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc))

est-il possible d'aller chercher les cells ailleurs que sur la feuille "Data_triee" ? Pour moi cette instruction ne pouvait pas être ambiguë. Pourquoi faut-il fixer la parentalité sur les objets cells et non pas sur la définition du range ?

Par contre de manière inattendue, je crois que cette discussion m'a permis de mieux appréhender la différence entre les collections sheets et worksheets, notamment grâce à ta remarque Patrice740

Citation:

Envoyé par Patrice740

Note : les objets Range et Cells n'appartiennent pas à une feuille Sheets(x), mais à une feuille de calcul Worksheets(x).

.
Le point important de l'explication dans la FAQ (https://excel.developpez.com/faq/?page=Feuille#Sheets) m'échappait. Je lisais sans y être attentif :
"La collection Worksheets représente l'ensemble des feuilles de calcul d'un classeur."
C'est déjà ça de gagner !!! :lol:

Merci en tous les cas pour le temps passé et vos explications fournies et détaillées.

Bonne journée
29/05/2020, 12h03
Patrice740

Citation:

Envoyé par laurent_42

...Mais dans le contexte de ma macro, il n'est juste pas possible de ne pas préciser cette parentalité.

Je ne connais aucun cas qui interdise de préciser la parentalité !
Pourrais tu préciser pourquoi tu ne peux pas le faire ?

Citation:

Envoyé par laurent_42

Mais ce que je persiste à ne pas comprendre c'est pourquoi la définition de la parentalité du range ne fonctionne pas. Avec

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc))

est-il possible d'aller chercher les cells ailleurs que sur la feuille "Data_triee"

Comme je te l'ai expliqué, avec cette syntaxe, non seulement c'est possible mais c'est toujours le cas :

Citation:

... les deux Cells dont la parentalité n'est pas précisée sont rattaché à la feuille active...

(dans mes explications, chaque mot est à son importance !)
Ça ne fonctionne que si le hasard est favorable, c'est à dire que la feuille active est "Data_triee".

C'est la raison pour laquelle je conseille de jamais utiliser les objets actifs (donc toujours préciser la parentalité) sauf dans les quelques très rares cas où c'est indispensable..

Ce qu'il faut bien comprendre c'est que quand on écrit une syntaxe simplifiée comme :

Code:

Range("A1") = "Test"

Le compilateur traduit toujours ça par la syntaxe complète qui est :

Code:

Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Test"

Et ta ligne de code devient :

Code:

Application.ActiveWorkbook.Worksheets("Data_triee").Range(Application.ActiveWorkbook.ActiveSheet.Cells(2, 3), Application.ActiveWorkbook.ActiveSheet.Cells(2, nb_var_inc))
29/05/2020, 12h09
Philippe Tulliez

Bonjour,

Citation:

Mais ce que je persiste à ne pas comprendre c'est pourquoi la définition de la parentalité du range ne fonctionne pas.

J'ai pourtant donné la réponse (Fil ) en mettant en rouge la feuille implicitement retenue par le VBA par rapport à la feuille active. Avec explications supplémentaires par Patrice :salut:
Je ne vois pas ce que l'on peut faire de plus

Citation:

Je n'ai pas précisé le classeur puisque je lance la macro à partir du classeur sur lequel je travaille

Alors le classeur parent de la feuille est ThisWorkbook.
Si le classeur parent est le classeur où se trouve le code VBA, utilise le CodeName de la feuille. C'est alors implicitement ThisWorkbook
Une programmation rigoureuse impose de préciser la parentalité totale d'un objet sauf bien entendu si tu aimes perdre ton temps à chercher plusieurs mois après la fin de ton développement pourquoi une application qui fonctionnait bogue subitement.
29/05/2020, 13h38
laurent_42

Arg !

Manifestement je n'arrive pas à m'expliquer correctement. Désolé ! J'essaie de reprendre vos 2 messages.

Citation:

Envoyé par Patrice740

Je ne connais aucun cas qui interdise de préciser la parentalité !
Pourrais tu préciser pourquoi tu ne peux pas le faire ?

Tous les mots sont aussi importants dans mon message. Je dis comme vous : "il n'est juste pas possible de ne pas préciser cette parentalité" (bon ok les doubles négations, c'est pas le plus clair, je vous l'accorde !!! :)). Ca veut bien dire que pour moi il est hors de question de ne pas préciser la parentalité. Je veux absolument le faire pour être sûr de bien travailler sur la bonne feuille.
Donc j'ai écrit :

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc)).Copy Sheets("Data_analyse").Range("B2")

pour préciser la parentalité du range et bien préciser la feuille sur laquelle je veux travailler.
Est-ce que vous êtes d'accord jusque là ?

Mais quand je fais ça, j'ai une erreur 1004. Donc pour répondre à ta question Patrice740 je ne peux pas écrire

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc)).Copy Sheets("Data_analyse").Range("B2")

parce que ça génère une erreur.
J'ai compris que ça génère une erreur parce que la parentalité de cell n'est pas définie.

Citation:

Envoyé par Patrice740

Comme je te l'ai expliqué, avec cette syntaxe, non seulement c'est possible mais c'est toujours le cas :

(dans mes explications, chaque mot est à son importance !)
Ça ne fonctionne que si le hasard est favorable, c'est à dire que la feuille active est "Data_triee".

C'est la raison pour laquelle je conseille de jamais utiliser les objets actifs (donc toujours préciser la parentalité) sauf dans les quelques très rares cas où c'est indispensable..

Je sais bien tout ça. Et je peux vous faire voir les quelques macros que j'ai faite pour vous démontrer que je suis absolument convaincu : je précise toujours la parentalité. Mais je te remercie d'insister : c'est effectivement un point capital. Mais promis j'ai compris ça.

Citation:

Envoyé par Patrice740

Ce qu'il faut bien comprendre c'est que quand on écrit une syntaxe simplifiée comme :

Code:

Range("A1") = "Test"

Le compilateur traduit toujours ça par la syntaxe complète qui est :

Code:

Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Test"

Et ta ligne de code devient :

Code:

Application.ActiveWorkbook.Worksheets("Data_triee").Range(Application.ActiveWorkbook.ActiveSheet.Cells(2, 3), Application.ActiveWorkbook.ActiveSheet.Cells(2, nb_var_inc))

Ca je l'ai très bien compris.

En répondant à Philippe, j'essaie de vous dire ce que je comprends pas.

A tout de suite.
29/05/2020, 14h03
laurent_42
Citation:

Envoyé par Philippe Tulliez

J'ai pourtant donné la réponse (Fil ) en mettant en rouge la feuille implicitement retenue par le VBA par rapport à la feuille active.

Je pense avoir compris ton explication. Je comprends très bien ton :
Code:

1 2 3 With shtSource .Range(ShtTarget.Cells(4, 2), ShtTarget.Cells(11, 6)).Copy shtTarget.Range("A1") End With
Mais ce que je ne comprends pas c'est où se situe le problème dans l'expression :

Code:

Sheets("Data_triee").Range(Cells(2, 3), Cells(2, nb_var_inc))

Comment avec ce code on pourrait comprendre qu'il faut aller chercher la cells(2,3) d'une autre feuille que sur la feuille data_triee ? C'est ça que je ne comprends pas. J'indique la parentalité avec mon sheets("Data_triee") non ? Il n'y a peut-être pas à comprendre, juste à accepter.
En fait ce qui m'échappe c'est ça

Citation:

Envoyé par Philippe Tulliez

les objets Cells auront implicitement comme parent la feuille active

Pour moi et c'est là mon erreur, avec le Sheets("Data_triee"), je pensais imposer la feuille de travail. Donc je ne pensais pas nécessaire de redéfinir la parentalité de cells puisque pour moi elle était déjà définie par la parentalité du range. Mais bon...

Je vais arrêter de vous faire perdre votre temps, de vous énerver (je sens que la moutarde vous monte au nez).

Je vais faire comme ça, ça marche, tant mieux. J'espère juste m'en souvenir la prochaine fois que j'en aurai besoin. C'est uniquement pour progresser et éviter des déconvenues futures que je cherche à éclaircir tous les points, surtout ceux qui me résistent parce que ce sont précisément sur ceux-ci que je vais refaire l'erreur la prochaine fois.

Citation:

Envoyé par Philippe Tulliez

Alors le classeur parent de la feuille est ThisWorkbook.
Si le classeur parent est le classeur où se trouve le code VBA, utilise le CodeName de la feuille. C'est alors implicitement ThisWorkbook

Alors ça par contre, je ne sais pas ce qu'est le CodeName de la feuille. Merci de me permettre de découvrir une nouvelle notion (ou en tous les cas son nom usuel... :-)

Citation:

Envoyé par Philippe Tulliez

Une programmation rigoureuse impose de préciser la parentalité totale d'un objet sauf bien entendu si tu aimes perdre ton temps à chercher plusieurs mois après la fin de ton développement pourquoi une application qui fonctionnait bogue subitement.

Hum... Merci pour la leçon. Mais juste dit comme ça, c'est un peu court. C'est moralisateur mais perso ça ne me permet pas de comprendre et de progresser.

Quoiqu'il en soit, merci pour le temps que vous m'avez consacré l'un et l'autre. J'espère pouvoir vous aider un jour (pas en VBA hein soyons sérieux) et ne plus vous déranger.

Bonne après-midi à tous
Excellent week-end
29/05/2020, 14h46
Patrice740
Pour faire court,
- Range() et Cells() sont des objets indépendants l'un de l'autre (c'est parce que j'ai du lait et un casserole que le lait est dans la casserole).
- Tous les objets "filles" sont rattachés à un parent dont la "mère supérieure" est l'objet Application
- Lorsque tu ne précises pas le parent, l'objet est toujours rattaché de façon implicite à la mère active.

Pour la dernière fois, je répète :
Quand tu écris Sheets("Data_triee".Range(Cells(... tu ne précises pas le parent de Cells,
ça revient à écrire Sheets("Data_triee".Range(Activesheet.Cells(...

Une règle de syntaxe doit être appliquée telle qu'elle est définie : le compilateur n'a pas de sentiment !

C'est pour simplifier ces écritures que les concepteurs du VBA ont prévu l'instruction With.
Citation:
Envoyé par laurent_42

Je pense avoir compris ton explication. Je comprends très bien ton :

Code:

1 2 3 With shtSource .Range(ShtTarget.Cells(4, 2), ShtTarget.Cells(11, 6)).Copy shtTarget.Range("A1") End With
As-tu bien compris pourquoi ce code ci-dessus ne peut pas fonctionner et renvoi une erreur ?
29/05/2020, 15h25
laurent_42

Citation:

Envoyé par Patrice740

Pour faire court,
- Range() et Cells() sont des objets indépendants l'un de l'autre (c'est parce que j'ai du lait et un casserole que le lait est dans la casserole).

Certes. Mais quand il est stocké, le lait est toujours dans un récipient. :) Je m'explique :
Mon erreur vient probablement de mon interprétation de Range et Cells : un Range est composé d'au moins une cellule. L'inverse n'étant pas vrai, j'ai imaginé une structure hiérarchique entre les 2. D'où la "croyance" que définir la parentalité de la cellule lorsque l'on a défini la parentalité du range était redondant, que la parentalité était surdéfinie.

Mais oui je pense que l'origine de tout cette discussion provient de cette erreur : Range et Cells sont indépendants. C'est vraiment ça qui me fait comprendre. :D:D:D Merci de l'avoir écrit noir sur blanc.

Citation:

Envoyé par Patrice740

le compilateur n'a pas de sentiment !

Ah... Monde cruel... Mes derniers espoirs foutus par terre. :lol:

Citation:

Envoyé par Patrice740

C'est pour simplifier ces écritures que les concepteurs du VBA ont prévu l'instruction With.

Et clairement c'est un super truc, très puissant, très pratique.

Citation:

Envoyé par Patrice740

As-tu bien compris pourquoi ce code ci-dessus ne peut pas fonctionner et renvoi une erreur ?

Oui. Pas convaincu d'avoir suffisamment compris pour intégrer et re-expliquer. J'espère juste que je l'ai suffisamment compris pour m'en souvenir la fois prochaine... :lol:

Merci d'avoir insisté ! :)

Bonne journée
Au plaisir,