使用 VBA 从其他工作表获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4148765/
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
Get values from other sheet using VBA
提问by Apollon1954
I want to get values from other sheets.
我想从其他工作表中获取值。
I have some values in Excel (sheet2) for example:
例如，我在 Excel (sheet2) 中有一些值：
A B C D
- - - -
1 | 2 5 9 12
2 | 5 8 4 5
3 | 3 1 2 6
I sum each column in row 4.
我对第 4 行中的每一列求和。
I'm working with these values in sheet2 but I want to get the result in sheet1.
我正在 sheet2 中使用这些值，但我想在 sheet1 中获得结果。
When using my code in sheet2 I get the correct answer but when I try to use it in a different sheet I get the result of the values corresponding to the current sheet cells and not to sheet2.
在 sheet2 中使用我的代码时，我得到了正确的答案，但是当我尝试在不同的工作表中使用它时，我得到了与当前工作表单元格相对应的值的结果，而不是与 sheet2 相对应的值。
I'm using With Application.WorksheetFunction.
我正在使用With Application.WorksheetFunction.
How can I set sheet2 as the active sheet?
如何将 sheet2 设置为活动工作表？
回答by Doc Brown
Try
尝试
ThisWorkbook.Sheets("name of sheet 2").Range("A1")
to access a range in sheet 2 independently of where your code is or which sheet is currently active. To make sheet 2 the active sheet, try
访问工作表 2 中的范围，而不管您的代码在哪里或哪个工作表当前处于活动状态。要使工作表 2 成为活动工作表，请尝试
ThisWorkbook.Sheets("name of sheet 2").Activate
If you just need the sum of a row in a different sheet, there is no need for using VBA at all. Enter a formula like this in sheet 1:
如果您只需要不同工作表中一行的总和，则根本不需要使用 VBA。在工作表 1 中输入这样的公式：
=SUM([Name-Of-Sheet2]!A1:D1)
回答by jpinto3912
That will be (for you very specific example)
那将是（对于你非常具体的例子）
ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value=someval
OR
或者
someVal=ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value
So get a F1click and read about Worksheets collection, which contains Worksheet objects, which in turn has a Cells collection, holding Cell objects...
因此，单击F1并阅读有关 Worksheets 集合的信息，其中包含 Worksheet 对象，而 Worksheet 对象又具有一个 Cells 集合，其中包含 Cell 对象...
回答by James Heffer
Sub TEST()
Dim value1 As String
Dim value2 As String
value1 = ThisWorkbook.Sheets(1).Range("A1").Value 'value from sheet1
value2 = ThisWorkbook.Sheets(2).Range("A1").Value 'value from sheet2
If value1 = value2 Then ThisWorkbook.Sheets(2).Range("L1").Value = value1 'or 2
End Sub
This will compare two sheets cells values and if they match place the value on sheet 2 in column L.
这将比较两个工作表单元格值，如果它们匹配，则将值放在 L 列的工作表 2 上。
回答by Mike
Try the worksheet activate command before you need data from the sheet:
在需要工作表中的数据之前尝试工作表激活命令：
objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(2).Activate
回答by Joni Depp
Maybe you can use the script i am using to retrieve a certain cell value from another sheet back to a specific sheet.
也许您可以使用我正在使用的脚本将某个单元格值从另一个工作表检索回特定工作表。
Sub reviewRow()
Application.ScreenUpdating = False
Results = MsgBox("Do you want to View selected row?", vbYesNo, "")
If Results = vbYes And Range("C10") > 1 Then
i = Range("C10") //this is where i put the row number that i want to retrieve or review that can be changed as needed
Worksheets("Sheet1").Range("C6") = Worksheets("Sheet2").Range("C" & i) //sheet names can be changed as necessary
End if
Application.ScreenUpdating = True
End Sub
You can make a form using this and personalize it as needed.
您可以使用它制作表格并根据需要对其进行个性化。
回答by RanonKahn
SomeVal=ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value
did not work. However the following code only worked for me.
不工作。但是下面的代码只对我有用。
SomeVal = ThisWorkbook.Sheets(2).cells(aRow,aCol).Value
