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