vba 在不首先选择的情况下读取/写入其他工作表中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4614343/
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
Reading/Writing Data In Other Sheets Without Selecting First
提问by Cunning
I've been having a bit of an issue with reading and writing data from other sheets.
我在从其他工作表读取和写入数据时遇到了一些问题。
Whilst I can read and write single cells fine using:
虽然我可以使用以下方法读取和写入单个单元格：
sheets(sheet Name).cells(x,y).value
工作表（工作表名称）。单元格（x，y）。值
using:
使用：
sheets(sheet Name).range(cells(x,y),cells(a,b)).value
工作表（工作表名称）。范围（单元格（x，y），单元格（a，b））。值
does not appear to work.
似乎不起作用。
Whilst this is easily worked around by simply selecting the sheet it does have a bit of an overhead and feels quite inelligent.
虽然这很容易通过简单地选择工作表来解决，但它确实有一点开销并且感觉很不智能。
The main purpose I require this for is reading and writing arrays to inactive worksheets, if anyone has an alternative i'd be exceptionaly gratefull.
我需要这样做的主要目的是读取和写入非活动工作表的数组，如果有人有其他选择，我将非常感激。
Cheers
干杯
采纳答案by Alex P
Using the cells method within a range object to refer to another worksheet requires you to activate that worksheet first I think (see section 5 in this from msdn)
使用范围对象中的单元格方法来引用另一个工作表需要您首先激活该工作表我认为（请参阅msdn 中的第 5 节）
Sub ReferToCells()
Dim arrData() As Variant, i As Long
Sheets("Sheet2").Activate
arrData = Range(Cells(1, 1), Cells(2, 1)).Value
For i = 1 To UBound(arrData)
Debug.Print arrData(i, 1)
Next i
End Sub
回答by Felix Bang
If you want to avoid using Select or Activate could you not instead do?:
如果您想避免使用 Select 或 Activate，您可以不这样做吗？：
With Sheets("Sheet Name")
arrData = .Range(.Cells(1,1), .Cells(2,1)).Value
End With
As far as I know this should work.
据我所知，这应该有效。
回答by Savakis
Dim ws As Worksheet
Set ws= Worksheets("MySheet")
With ws
.Range(ws.Cells(3, 1), ws.Cells(3, 14)).ClearContents
End With
End If
This works really well without activating sheet
这在不激活工作表的情况下非常有效
