Excel VBA, obtenir une plage à partir d'une feuille inactive

Question

Ce script fonctionne correctement lorsque je consulte la feuille "Temp". Mais lorsque je suis dans une autre feuille, la commande de copie échoue. Cela donne une erreur Définie par l'application ou par l'objet:

Sheets("Temp").Range(Cells(1), Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

Je peux utiliser ce script à la place, mais alors j'ai des problèmes avec le collage :

Sheets("Temp").Columns(1).Copy
Sheets("Overview").Range("C40").PasteSpecial

Je ne veux pas activer la feuille "Temp" pour obtenir cela.

Que puis-je faire d'autre?

Answer 1

Votre problème est que, parce que les références de Cell à l'intérieur des plages de la Range ne sont pas qualifiées, elles font référence à une feuille par défaut, qui peut ne pas être la feuille que vous avez l'intention de viser. Pour les modules standard, le module ThisWorkbook, les classes personnalisées et les modules de formulaire utilisateur, la feuille par défaut est la feuille active (ActiveSheet). Pour les modules de code derrière les Worksheet, c'est cette feuille.

Pour les modules autres que les modules de code derrière les feuilles de calcul, votre code signifie en fait

Sheets("Temp").Range(ActiveSheet.Cells(1), ActiveSheet.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

Pour les modules de code derrière les feuilles de calcul, votre code signifie en fait

Sheets("Temp").Range(Me.Cells(1), Me.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial

Dans les deux cas, la solution est la même : qualifier entièrement les références de plage avec le classeur requis:

Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("Temp")
Set sh2 = ActiveWorkbook.Sheets("Overview")

With sh1
    .Range(.Cells(1,1), .Cells(1,1).End(xlDown)).Copy
End With
sh2.Range("C40").PasteSpecial

Remarque: Lors de l'utilisation de .End(xlDown), il y a un risque que cela entraîne une plage s'étendant plus loin que prévu. Il est préférable d'utiliser .End(xlUp) si la disposition de votre feuille le permet. Sinon, vérifiez d'abord la cellule référencée et la cellule en dessous pour vérifier si elles sont Empty.

Answer 2

J'ai moi-même rencontré un problème similaire : j'essayais de rechercher dans une feuille de calcul distincte si la couleur d'une cellule correspondait à la couleur d'une cellule dans une liste et de renvoyer une valeur de chaîne : si vous utilisez .Cells(ligne, colonne), vous avez juste besoin de ceci : Sheets("nomdelafeuille").Cells(ligne, colonne) pour référencer cette plage de cellules.

Je parcourais un bloc de 500 cellules et cela fonctionne étonnamment rapidement pour moi.

Je n'ai pas essayé cela avec .Copy, mais je suppose que cela fonctionnerait de la même manière.

Answer 3

Cela fonctionnera, je n'aime pas utiliser (xlDown) au cas où une cellule est vide.

Dim lRow As Long
lRow = Sheets("Temp").Cells(Cells.Rows.Count, "A").End(xlUp).Row

With Sheets("Temp")
     .Range("A1:A" & lRow).Copy Sheets("Overview").Range("C40")
End With

Ou si vous préférez utiliser simplement les colonnes...

Sheets("Temp").Columns(1).SpecialCells(xlCellTypeConstants).Copy Destination:=Sheets("Overview").Range("C40")