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?
asked Nov 10, 2010 at 20:29
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]
answered Nov 10, 2010 at 20:35
Doc BrownDoc Brown
19.3k6 gold badges51 silver badges87 bronze badges
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...
answered Nov 10, 2010 at 20:35
jpinto3912jpinto3912
1,4652 gold badges11 silver badges19 bronze badges
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.
answered Jul 16, 2016 at 8:16
James HefferJames Heffer
6461 gold badge6 silver badges17 bronze badges
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
answered May 11, 2015 at 6:33
RanonKahnRanonKahn
7878 silver badges30 bronze badges
6
Try the worksheet activate command before you need data from the sheet:
objWorkbook.WorkSheets[1].Activate
objWorkbook.WorkSheets[2].Activate
DimaSan
11.7k11 gold badges65 silver badges74 bronze badges
answered Jun 20, 2017 at 13:20
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.
answered Aug 23, 2019 at 4:50
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.
answered Jul 24, 2020 at 20:31
KasraKasra
1054 silver badges13 bronze badges