41

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?

Community
  • 1
  • 1
Apollon1954
  • 1,388
  • 4
  • 16
  • 33

7 Answers7

57

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)
Doc Brown
  • 19,739
  • 7
  • 52
  • 88
21

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

jpinto3912
  • 1,457
  • 2
  • 12
  • 19
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.

James Heffer
  • 686
  • 1
  • 6
  • 17
  • How is this relevant to the question? (Except referencing a different sheet, which was already covered.) Also `Value2` is a property of `Range` so I would recommend not using it as a variable name. – arcadeprecinct Jul 16 '16 at 13:27
  • 3
    **First**: Chill out, I was not trying to bash you. Your answer was up for review and I didn't flag it for deletion but asked you to clarify. I'm sorry if I offended you. **Second**: I reread the question and it says nothing about comparing values, and accessing different sheets is already covered in the accepted answer. **Third**: I know you declared `value2` but [`Value2` is a property of Range](https://msdn.microsoft.com/en-us/library/office/ff193553.aspx). It is generally a good idea to not reuse names to avoid confusion (even though you won't have troubles with this one). – arcadeprecinct Nov 15 '16 at 08:05
  • If you want we can continue this in the chat: http://chat.stackoverflow.com/rooms/111528/vba-lounge – arcadeprecinct Nov 15 '16 at 08:05
  • 1
    I'm most chilled dude, the reason I'm "comparing values", as you so nicely put it, is because, the question says, "I'm working with this values in sheet2 and i want to get the result in sheet1"... Now, I took "I want to get the result in sheet1", now to me that means, he wants a result that are on two sheets, but of course they should be the same? So that's my reason for validation. To ensure the result he's looking for on other sheets, matches the result he's looking for. It's simple validation bro... If I've understood this question wrong, then sorry... – James Heffer Nov 16 '16 at 10:11
1
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
Zoe
  • 27,060
  • 21
  • 118
  • 148
RanonKahn
  • 853
  • 10
  • 34
0

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

objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(2).Activate
DimaSan
  • 12,264
  • 11
  • 65
  • 75
Mike
  • 1
  • 2
0

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.

Joni Depp
  • 1
  • 2
0

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.

Kasra
  • 107
  • 4
  • 14