Select Data from Separate Worksheets with Macros VBA in Excel

Add to Favorites
Author:

Select data from other worksheets with Macros and VBA without navigating to those worksheets or activating them.

This method allows the user to remain on one single worksheet while the macro goes through the workbook to get data from where it needs to get it.

Syntax

Worksheets(Sheet name or Index number)

Argument

Description

Sheet name or Index number

Here, you put the name of the worksheet from which you want to get the data, surrounded by double quotation marks or you put the index number, which is helpful when doing things like looping through the worksheets in a workbook.

Worksheets Usage Example - Reference Sheet by Name

The final code will look something like this:

Worksheets("Sheet2").Range("B1").Value

e013402e85981c5d84d130d1fa90aa6b.jpg

We use Worksheets("Sheet Name") to tell the Macro from which sheet we want to get the data. Then we just type a period and make a regular cell or range reference - if you don't understand how to reference cells, look at this tutorial here: Get Data from the Worksheet - Cell References 

Worksheets Usage Example - Reference Sheet by Index Number

You can also reference the worksheets using their index number, which is useful when you need to loop through worksheets in the workbook.

To reference Sheet2, which is the second worksheet, using its index number I would do this:

Worksheets(2).Range("B1").Value

6d1682eb932b0a99ef95911262d9d636.jpg

Notes

This is not a difficult concept but it is important to memorize.

You may have also seen people use Sheets instead of Worksheets and, for most cases, that won't cause any issues, but for simply getting values from other worksheets, it is usually best to use the Worksheets method.

Make sure to download the sample Excel file attached to this tutorial to get the Macro code and play around with it. I added MsgBox to the code so you could see the values to make sure it works when you run the macro.

1bdeaffb7e069701205291875938259f.jpg

Just uncomment one of the lines and run the macro to test it out.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Copy and Paste Data using Macro VBA in Excel
Tutorial: How to copy and paste data using a Macro in Excel. I'll show you multiple ways to do this,...
Get the Last Row using VBA in Excel
Tutorial: (file used in the video above) How to find the last row of data using a Macro/VBA in Exce...
Update, Change, and Manage the Data Used in a Chart in Excel
Tutorial: In this tutorial I am going to show you how to update, change and manage the data used by ...
Excel VBA to Select Data from Other Worksheets - VBA Quickie 1
Tutorial: This Excel VBA Quickie shows you how to reference cells on separate worksheets and put th...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Copy and Paste Data using Macro VBA in Excel
Tutorial: How to copy and paste data using a Macro in Excel. I'll show you multiple ways to do this,...
Get the Last Row using VBA in Excel
Tutorial: (file used in the video above) How to find the last row of data using a Macro/VBA in Exce...
Excel Forum