Get values from other sheet using VBA

Question

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?

Answer 1

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

 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:

=SUM([Name-Of-Sheet2]!A1:D1)

Answer 2

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...

Answer 3

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.

Answer 4

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

Answer 5

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

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

Answer 6

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.

Answer 7

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.