Getting Values From Another Sheet In Excel Using VBA - Chartio

Excel is extremely powerful even using just the basic functionality of adding data to cells and spreadsheets, sorting and cultivating that data into a beautiful work of cellular delight. Yet Excel is capable of far more than the standard editing of cell contents allows, through the magic of Visual Basic for Applications scripts, or VBA.

We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet, but this is just the tip of the iceberg for what Excel and VBA can accomplish together.

Accessing the Visual Basic Editor

To begin, you’ll need to locate the Visual Basic Editor in Excel. Finding this will depend on what version of Excel you are running, but for most modern versions, the Visual Basic Editor can be found under the Developer tab of the menu ribbon.

Once located, simply click Macros, enter a macro name (we’ll use MySum for this example), then click create to open the editor and begin scripting.

The Script

Once inside the editor you’ll see the declaration of your MySum macro, which is just a standard vba function that we can edit to do our bidding.

Sub MySum() End Sub

For our example, perhaps we have a worksheet named Sales and we want to use this macro to total (sum) the first 25 values in column B.

Thus to begin our script, we need to select the appropriate worksheet named Sales, and then using that selected object, narrow our selection by grabbing the specific cells (known as a range of cells) that we want to total.

Sub MySum() Worksheets("Sales").Range("B1:B25") End Sub

Now we actually need to get the total or sum of all those values, so we wrap the previous object call in a WorksheetFunction of Sum, like so:

Sub MySum() WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25")) End Sub

Yet that’s not the final step. In our example, we want to extract this sum total from the Sales worksheet and then insert that total value into the current cell we have selected when running this macro.

To accomplish this, we’ll use the ActiveCell object, setting its Value to be equal to our summed total:

Sub MySum() ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25")) End Sub

Alright, now our macro is complete, so let’s save and go back to our actual spreadsheet to test it out.

Running the Macro

To ensure it is functional, select a different spreadsheet, then select a single cell where you want the sum to be inserted.

To run a saved macro, just go back to the Developer tab, select Macros again, and choose your MySum macro. The cell contents should now be replaced with the sum total of the Sales column B values we specified.