You can switch between worksheets using one of several different techniques.  To begin demonstrating this, extract and open the file linked to in the Files Needed section above:

The sample workbook contains four worksheets, each of which contains details of drinks sold in a branch of a chain of coffee shops.

Working on the Active Worksheet

When you refer to a cell in your code, Excel assumes the cell is on the worksheet which is currently active when the code is executed.  As shown in the screenshot below, you should find that the Paris worksheet is active when you open the sample workbook:

You can see the tab of the active sheet highlighted in a different colour.

We'd like to change the title in cell B2 to match the name of the worksheet.  In the VBE, insert a new module and create a new subroutine as shown below:

Create a new subroutine as shown.

Add some code to select cell B2 and then change the value of the selected cell to match the name of the selected worksheet:

You can use the ActiveSheet property to return a reference to the worksheet that is currently active when the code is executed.

Run the subroutine and check the results:

You should find that the value of B2 has changed only on the sheet that was selected when you executed the code.

Selecting a Sheet by Name

One way to move to another worksheet is to reference it by name and apply the Select method to it.  Create a new subroutine which will select the London worksheet:

Begin the first instruction by referring to the Worksheets collection followed by an open parenthesis.

To specify which worksheet you want to reference, you can enter its name within a set of double-quotes:

Enter the name of the worksheet within a set of double-quotes. The name of the sheet isn't case-sensitive but it's good practice to match the name as it appears on the worksheet tab in Excel.

You can then apply methods and properties to the worksheet object.  In this example we'll apply the Select method to make London the active sheet:

Apply the Select method to the worksheet object.

Once the London sheet has been selected, you can refer to and modify properties of cells in the usual way:

We've used the same code to select and alter the value of a cell as in the first example.

Execute the procedure and check the result in Excel:

You should see the London sheet has been selected and the value of cell B2 has been changed.

Selecting a Sheet by Position

You can also refer to a worksheet using a number which indicates its position in the set of worksheet tabs in the workbook.  In the next example, we want to change something on the Milan worksheet:

Counting from left to right, Milan is the third worksheet.

Begin a new subroutine and refer to the Worksheets collection:

Begin the instruction in the same way as in the previous example.

Rather than entering the name of a worksheet, you can simply enter the number which corresponds to its position:

Milan is the third worksheet so enter the number 3 and close the parentheses. You can then apply the Select method to the worksheet.

The remaining code is the same as we used in the previous two examples:

Feel free to copy and paste this code instead of writing it again!

When you run the subroutine you should find that the third worksheet becomes selected and the value of cell B2 will change:

This is what you should see after running the subroutine.

Using Code Names

For the final and most exotic of the four locations, we'll select the worksheet using a codename.  All worksheets begin life with a default codename which you can see in the Project Explorer window:

The code name of the Hull worksheet is currently Sheet4.

You can use the original codename of a worksheet but it will make your code easier to understand if you give a more descriptive name.  You can change the codename of a worksheet using the Properties window:

Select the worksheet in the Project Explorer and then edit its (Name) property in the Properties window. Here we're calling the worksheet HullSheet.

Press Enter once you've finished typing in a new codename for the worksheet:

The new codename of the worksheet will be displayed in the Project Explorer.

Once you've changed the codename you can use it in a subroutine as shown below:

You can write the codename of the worksheet and apply methods and properties to it directly.  You'll even see the IntelliSense list appear when you refer to a worksheet using this technique!

The remaining code to select and modify a cell is the same as we've used in the previous examples on this page:

Select and then change the value of cell B2.

When you run the subroutine you should the results shown below:

The Hull worksheet will be selected and cell B2's value will match the name displayed on the sheet tab.

Click here to download a file containing the sample data.  Extract and open this file to see an extended version of the file you've been working on in this section:

This version of the workbook contains an extra worksheet in which we'd like to display the total sales from each branch of the shop.

Your task is to copy the total sales from each of the other four worksheets into the Total sheet.

Selecting a Sheet by Name

To practise selecting worksheets by name:

1. Create a subroutine and add an instruction to select the Paris worksheet:

2. Add two more instructions to select and copy cell F9:

3. Add an instruction to select the Total worksheet using the sheet name:

4. Add two final instructions to select and paste the copied value into cell B3:

5. Run the subroutine and check the results:

You should find that the Total worksheet contains the total sales from the Paris sheet.

Selecting a Sheet by Position

To practise selecting worksheets by position:

1. Create a new subroutine and add an instruction to select the London worksheet by its position (i.e. 2):

2. Add instructions to select and copy cell F9:

3. Add an instruction to select the Total worksheet by its position (i.e. 5):

4. Add two final instructions to select and paste the copied value into cell B4:

5. Run the subroutine and check the results in Excel:

You should find the total sales from the London sheet in the Total sheet.

Using Code Names

1. Select the Milan worksheet in the Project Explorer window:

Click the worksheet to select it.

2. In the Properties window, change the codename of the worksheet to MilanSheet:

Change the (Name) property and press Enter.

3. Repeat this process to change the codename of the Total worksheet to TotalSheet:

Change the codename of this worksheet to TotalSheet.

4. Create a subroutine and add code to select the Milan worksheet using its codename, then select and copy cell F9:

5. Add instructions to select the Total worksheet using its codename, then select and paste the copied value into cell B5:

6. Run the subroutine and check the results in Excel:

This is what you should see after running the subroutine.

To Finish

To finish this example, create a subroutine to copy the total from the Hull worksheet into the Total sheet using whichever technique you prefer:

The end result should resemble this.

Save and close the workbook.  You can click here to download the finished version of this workbook.