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.
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. |
The final code will look something like this:
Worksheets("Sheet2").Range("B1").Value
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
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
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.
Just uncomment one of the lines and run the macro to test it out.