Table of Contents
VBA (Visual Basic for Applications) can be used to get the value from a cell in another sheet. To do this, one would need to reference the sheet and cell of interest and assign the value to a variable. For instance, to get the value from cell A1 on a sheet named Sheet2, one would use the code “Dim myVariable As Variant: myVariable = Sheets(“Sheet2”).Range(“A1”).Value”. This code will assign the value from cell A1 to the variable “myVariable”.
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:
