[excel] Get values from other sheet using VBA

I want to get values from other sheets.

I have some values in Excel (sheet2) for example:

    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.

I'm working with these values in sheet2 but I want to get the result in 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.

I'm using With Application.WorksheetFunction.

How can I set sheet2 as the active sheet?

Try the worksheet activate command before you need data from the sheet:

objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(2).Activate

Usually I use this code (into a VBA macro) for getting a cell's value from another cell's value from another sheet:

Range("Y3") = ActiveWorkbook.Worksheets("Reference").Range("X4")

The cell Y3 is into a sheet that I called it "Calculate" The cell X4 is into a sheet that I called it "Reference" The VBA macro has been run when the "Calculate" in active sheet.

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 F1 click and read about Worksheets collection, which contains Worksheet objects, which in turn has a Cells collection, holding Cell objects...

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.

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.

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