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

Question

Ce script fonctionne bien lorsque je visualise la feuille "Temp". Mais lorsque je suis dans une autre feuille, la commande de copie échoue. Elle donne un Application-defined or object-defined error :

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

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

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 le parce que le Cell à l'intérieur du Range ne sont pas qualifiées, elles font référence à une feuille par défaut, qui peut ne pas être celle que vous souhaitez. Pour les modules standard, les ThisWorkbook les classes personnalisées et les modules de formulaire utilisateur, le défaut est le module ActiveSheet . Pour Worksheet le code derrière les modules, c'est cette feuille de travail.

Pour les modules autres que le code de la feuille de calcul derrière les modules, votre code dit en fait

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

Pour le code de la feuille de calcul derrière les modules, votre code dit en fait

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

Dans un cas comme dans l'autre, la solution est la même : qualifier complètement 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

Note : Lorsque vous utilisez .End(xlDown) il y a un risque de voir la portée s'étendre plus loin que prévu. Il est préférable d'utiliser .End(xlUp) si la mise en page de votre feuille le permet. Si ce n'est pas le cas, vérifiez que la cellule référencée et la cellule située en dessous ne contiennent pas d'erreurs. Empty d'abord.

Answer 2

J'ai moi-même rencontré un problème de ce type : J'essayais d'effectuer une recherche dans une feuille de calcul séparée pour voir si la couleur d'une cellule correspondait à la couleur d'une cellule dans une liste et renvoyer une valeur de type chaîne de caractères : si vous utilisez .Cells(row, column), vous n'avez besoin que de ceci : Sheets("nom de la feuille").Cells(row, column) pour faire référence à cette plage de cellules.

J'ai parcouru en boucle un bloc de 500 cellules et cela fonctionne étonnamment vite pour moi.

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

Answer 3

Cela fera l'affaire, je n'aime pas utiliser (xlDown) dans le 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 voulez simplement utiliser les Colonnes...

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