Excel VBA，从非活动工作表获取范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8047943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA, getting range from an inactive sheet
提问by Aziz
This script works fine when I'm viewing the "Temp" sheet. But when I'm in another sheet then the copy command fails.. It gives a "Application-defined or object-defined error"
当我查看“Temp”表时，此脚本工作正常。但是当我在另一个工作表中时，复制命令失败..它给出了“应用程序定义或对象定义的错误”
Sheets("Temp").Range(Cells(1), Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
I can use this script instead, but then I have problems with pasting it
我可以改用这个脚本，但是我在粘贴它时遇到了问题
Sheets("Temp").Columns(1).Copy
Sheets("Overview").Range("C40").PasteSpecial
- I dont want to activate the "Temp" sheet to get this
- 我不想激活“临时”表来获取此信息
What else can I do
我还可以做些什么
回答by chris neilsen
Your issue is that the because the Cellreferences inside the Range's are unqualified, they refer to a default sheet, which may not be the sheet you intend.
For standard modules, the ThisWorkbookmodule, custom classes and user form modules, the defeault is the ActiveSheet. For Worksheetcode behind modules, it's that worksheet.
您的问题是因为'sCell内的引用Range是不合格的，所以它们引用了默认工作表，这可能不是您想要的工作表。对于标准模块、ThisWorkbook模块、自定义类和用户表单模块，默认为ActiveSheet. 对于Worksheet模块背后的代码，就是那个工作表。
For modules other than worksheet code behind modules, your code is actually saying
对于模块背后的工作表代码以外的模块，您的代码实际上是在说
Sheets("Temp").Range(ActiveSheet.Cells(1), ActiveSheet.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
For worksheet code behind modules, your code is actually saying
对于模块背后的工作表代码，您的代码实际上是在说
Sheets("Temp").Range(Me.Cells(1), Me.Cells(1).End(xlDown)).Copy
Sheets("Overview").Range("C40").PasteSpecial
In either case, the solution is the same: fully qualify the range references with the required workbook:
在任何一种情况下，解决方案都是相同的：使用所需的工作簿完全限定范围引用：
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:When using .End(xlDown)there is a danger that this will result in a range extending further than you expect. It's better to use .End(xlUp)if your sheet layout allows. If not, check the referenced cell and the cell below for Emptyfirst.
注意：使用时.End(xlDown)存在导致范围超出预期的危险。.End(xlUp)如果您的工作表布局允许，最好使用。如果没有，请先检查引用的单元格和下面的单元格Empty。
回答by Anonymous
I encountered a problem like this myself: I was trying to search through a separate worksheet to see if the color of a cell matched the color of a cell in a list and return a string value: if you are using .Cells(row, column), you only need this: Sheets("sheetname").Cells(row, column) to reference that range of cells.
我自己遇到了这样的问题：我试图搜索一个单独的工作表，以查看单元格的颜色是否与列表中单元格的颜色匹配并返回一个字符串值：如果您使用 .Cells(row, column )，你只需要这个： Sheets("sheetname").Cells(row, column) 来引用那个范围的单元格。
I was looping through a block of 500 cells and it works surprisingly quickly for me.
我正在循环遍历 500 个单元格，它对我来说工作得非常快。
I have not tried this with .Copy, but I would assume it would work the same way.
我没有用 .Copy 尝试过这个，但我认为它会以同样的方式工作。
回答by GMalc
This will do, I don't like to use (xlDown) in case a cell is empty.
这样做，我不喜欢使用 (xlDown) 以防单元格为空。
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
Or if you want to just use Columns...
或者，如果您只想使用列...
Sheets("Temp").Columns(1).SpecialCells(xlCellTypeConstants).Copy Destination:=Sheets("Overview").Range("C40")
