You can use the following methods in VBA to get the cell value in another sheet:
Method 1: Get Cell Value from Another Sheet
Sub GetCellAnotherSheet()
ActiveCell.Value = Worksheets("Sheet2").Range("A2")
End Sub
This particular macro will get the value of cell A2 in Sheet2 to and return it in the currently active cell.
Method 2: Get Result of Operation of Cell Values from Another Sheet
You can also perform some operation on cells in another sheet and return the result of the operation in the currently active cell.
For example, you can use the following syntax to sum the values in the range B2:B10 in Sheet2 and return the sum in the currently active cell:
Sub GetCellAnotherSheet()
ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sheet2").Range("B2:B10"))
End Sub
The following examples show how to use each method in practice.
Example 1: Get Cell Value from Another Sheet
Suppose we have the following sheet called Sheet2 that contains information about various basketball players:
Suppose we currently have cell A2 in Sheet1 selected as our active cell.
We can create the following macro to get the value in cell A2 of Sheet2 and return it in the currently active cell:
Sub GetCellAnotherSheet()
ActiveCell.Value = Worksheets("Sheet2").Range("A2")
End Sub
When we run this macro, we can see that the value in cell A2 of Sheet1 is now set to “Mavs”, which matches the value from cell A2 of Sheet2:
Example 2: Get Result of Operation of Cell Values from Another Sheet
Once again suppose we have the following sheet called Sheet2 that contains information about various basketball players:
We can use the following macro to sum the values in the points column of Sheet2 and return the result in the currently active cell, which happens to be cell A2 in Sheet1:
Sub GetCellAnotherSheet()
ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sheet2").Range("A2"))
End Sub
When we run this macro, we can see that the sum of values in the range A2:A10 of Sheet2 is now shown in cell A2 of Sheet1:
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Set Cell Value in Another Sheet
VBA: How to Select Range from Active Cell
VBA: How to Delete Rows Based on Cell Value
Looking to see if you have a code that will look at a value in a cell in one workbook say TOM JONES and find it in another workbook
Hi Ian…Certainly! Below is an example of VBA code that searches for a value in one workbook and finds it in another workbook. This example assumes you have two workbooks: “Workbook1.xlsx” and “Workbook2.xlsx”. The code looks for the value “TOM JONES” in “Workbook1.xlsx” and finds its location in “Workbook2.xlsx”.
### VBA Code to Search for a Value in Another Workbook
1. **Open Excel and Press `ALT + F11`** to open the VBA editor.
2. **Insert a new module** by right-clicking on any existing module or on the VBA project, then selecting `Insert > Module`.
3. **Copy and paste the following code** into the new module:
“`vba
Sub FindValueInAnotherWorkbook()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim searchValue As String
Dim foundCell As Range
‘ Define the search value
searchValue = “TOM JONES”
‘ Set the workbooks
Set wb1 = Workbooks(“Workbook1.xlsx”)
Set wb2 = Workbooks(“Workbook2.xlsx”)
‘ Set the worksheets
Set ws1 = wb1.Sheets(1) ‘ Assuming you want to search in the first sheet of Workbook1
Set ws2 = wb2.Sheets(1) ‘ Assuming you want to search in the first sheet of Workbook2
‘ Search for the value in Workbook2
Set foundCell = ws2.Cells.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCell Is Nothing Then
‘ If found, display a message with the location
MsgBox “Value ‘” & searchValue & “‘ found in Workbook2 at ” & foundCell.Address, vbInformation
Else
‘ If not found, display a message
MsgBox “Value ‘” & searchValue & “‘ not found in Workbook2.”, vbExclamation
End If
End Sub
“`
### Instructions to Run the Code
1. **Save and close the VBA editor**.
2. **Press `ALT + F8`** to open the macro dialog box in Excel.
3. **Select `FindValueInAnotherWorkbook`** and click `Run`.
### Notes
– **Workbook Names**: Make sure “Workbook1.xlsx” and “Workbook2.xlsx” are open and their names match exactly as specified in the code.
– **Worksheet References**: The code assumes you are searching in the first sheet of each workbook. You can change the sheet index or names as needed.
– **Search Value**: You can change the `searchValue` variable to any value you want to search for.
This code will search for “TOM JONES” in the specified worksheets and will display a message box indicating whether the value was found and its location. If the value is not found, it will notify you accordingly.