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)