Module 2 - Moving Around in Excel
Lesson 2.1 - Basic Cell Selection
Topic 2.1.3 - Moving Between Worksheets

If you want to select cells in different worksheets, it's important to select the correct worksheet first.  This page shows you several techniques for moving between worksheets in a workbook.

Select sheet

To select a cell on a worksheet, you must first make sure that the sheet is selected.

 

Files Needed

You can click here to download the file needed for this page.

Completed Code

You can click here to download a file containing the completed code.

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:

Basic eg

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:

The active sheet`

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:

New sub

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:

Activesheet

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:

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:

Refer to 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 sheet name

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:

Select 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:

Modify cells

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:

Result

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:

Count sheets

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

 

Begin a new subroutine and refer to the Worksheets collection:

Refer to worksheets

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:

Third sheet

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:

Change cell

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:

Milan sheet

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:

Sheet code name

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:

Change codename

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:

New codename

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:

Use codename

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:

Remaining code

Select and then change the value of cell B2.

 

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

Results

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

Using a sheet codename is an easy and convenient way to refer to a worksheet but beware that this technique only works when the worksheet that you want to reference is in the same workbook as your code.

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:

Total sheet

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:

Sub Copy_Paris_Total()

 

Worksheets("Paris").Select

 

End Sub

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

Sub Copy_Paris_Total()

 

Worksheets("Paris").Select

 

Range("F9").Select

ActiveCell.Copy

 

End Sub

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

Sub Copy_Paris_Total()

 

Worksheets("Paris").Select

 

Range("F9").Select

ActiveCell.Copy

 

Worksheets("Total").Select

 

End Sub

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

Sub Copy_Paris_Total()

 

Worksheets("Paris").Select

 

Range("F9").Select

ActiveCell.Copy

 

Worksheets("Total").Select

 

Range("B3").Select

ActiveCell.PasteSpecial xlPasteValues

 

End Sub

  1. Run the subroutine and check the results:
Result

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):

Sub Copy_London_Total()

 

Worksheets(2).Select

 

End Sub

  1. Add instructions to select and copy cell F9:

Sub Copy_London_Total()

 

Worksheets(2).Select

 

Range("F9").Select

ActiveCell.Copy

 

End Sub

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

Sub Copy_London_Total()

 

Worksheets(2).Select

 

Range("F9").Select

ActiveCell.Copy

 

Worksheets(5).Select

 

End Sub

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

Sub Copy_London_Total()

 

Worksheets(2).Select

 

Range("F9").Select

ActiveCell.Copy

 

Worksheets(5).Select

 

Range("B4").Select

ActiveCell.PasteSpecial xlPasteValues

 

End Sub

  1. Run the subroutine and check the results in Excel:
London total

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:
Milan sheet

Click the worksheet to select it.

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

Change the (Name) property and press Enter.

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

Change the codename of this worksheet to TotalSheet.

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

Sub Copy_Milan_Total()

 

MilanSheet.Select

Range("F9").Select

ActiveCell.Copy

 

End Sub

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

Sub Copy_Milan_Total()

 

MilanSheet.Select

Range("F9").Select

ActiveCell.Copy

 

TotalSheet.Select

Range("B5").Select

ActiveCell.PasteSpecial xlPasteValues

 

End Sub

  1. Run the subroutine and check the results in Excel:
Results

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:

End result

The end result should resemble this.

 

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

This page has 0 threads Add post