Start by extracting the set of files linked to in the Files Needed section above.  When you've done this, open the file called Jan Sales.xlsm:

Open this file to get started.

You should find that you have the Total worksheet selected with cell A1 selected, as shown below:

This should be what you see when you open the file.

Open the VBE and insert a module into the project.

Cells on the Active Worksheet

For the first example, we'll add a title to cell A7 on the Total worksheet.  Begin by creating a new subroutine and add an instruction to make sure that the Total worksheet is the active sheet:

The new subroutine should look like this.

In previous parts of this lesson, to change the contents of a cell we've selected the cell first and then referred to the Value property of the ActiveCell:

Previously, we've selected a cell before changing its value.

In VBA, there's no need to select a cell in order to change one of its properties.  We can replace the two instructions with a single line of code:

This instruction alters the value of cell A7 without selecting it first.

We can do the same thing to add a formula to cell B7:

You can use the Value property to assign a formula to a cell.

You can run the subroutine and check the results in Excel:

After running the subroutine you should see the value of both cells has changed but neither cell has been selected.

This shows that when you refer to the Range property, VBA assumes that the cell you have specified is on the currently active worksheet.  What if you want to refer to a cell on another worksheet?

Cells on Other Worksheets

Next we'll copy the total sales from each of the regional worksheets onto the Total sheet:

The total for each region is in cell F9.

Create a new subroutine to do this.  In previous parts of this lesson we've copied data from other worksheets by selecting the worksheet first:

Previously, we've selected a worksheet before referencing a cell.

You don't need to select a worksheet in order to reference cells on that sheet.  We can replace the two instructions shown above with a single instruction:

This instruction will copy cell F9 from the Paris worksheet, regardless of whether the Paris sheet is selected.

You can use the same technique to paste the copied value onto the Total sheet, regardless of which worksheet is selected:

This routine will work no matter which worksheet is selected when you choose to run it.

Run the subroutine and check the results in Excel:

The PasteSpecial method automatically selects the cell to which you apply it.

As we're only copying values from the regional worksheets, we don't actually need to perform a separate copy and paste.  Create a new subroutine which will read the values from the other regional sheets into the Total worksheet.  Start by writing an instruction to assign a value to cell B4 on the Total sheet:

Cell B4 will hold the total from the London worksheet.

You can complete the instruction by referring to cell F9 on the London worksheet:

This single instruction will set the value of cell B4 on the Total worksheet to match that of cell F9 on the London worksheet.

You can use the same technique to copy the totals from the Milan and Hull worksheets:

The three instructions are very similar, apart from the cell references and worksheet names.

You can run the subroutine and check the results in Excel:

After running the subroutine, all of the regional totals will appear in the Total sheet.

Cells in Other Workbooks

You can take this idea one step further and refer to cells in other open workbooks.  Start by opening the Q1 Total.xlsx workbook from the set of files that you downloaded earlier:

Open the selected file.

In earlier parts of this lesson we activated a workbook before selecting and copying a cell, but there's no need to do this to get a value from one workbook to another.  To demonstrate this, we'll copy the total sales from the Jan Sales.xlsm workbook to the Q1 Total.xlsx workbook:

We'll place the total in cell B3 in the Total worksheet.

Using the same module that you've been working on so far, create a new subroutine and begin writing an instruction to alter the value of the appropriate cell:

Refer to a specific range on a specific worksheet in a specific workbook!

You can complete the instruction by referring to the cell which contains the total sales for January:

You can refer to cell B7 on the Total worksheet in ThisWorkbook.

Run the subroutine and check that the result appears in the Q1 Total.xlsx workbook:

The result should appear as shown here.

To practise referring to cells in other worksheets and workbooks:

1. Open the Feb Sales.xlsx and Mar Sales.xlsx workbooks from the folder you downloaded earlier:

Open the two workbooks shown highlighted here.

2. Using the same module you created earlier, create a new subroutine which will copy the totals for February and March into the Q1 Total.xlsx workbook:

3. Add an instruction to copy the total sales from the Feb Sales.xlsx workbook:

4. Add an instruction to paste the copied value into the Q1 Total.xlsx workbook:

5. Add an instruction to assign the value of the total sales for March to the appropriate cell in the Q1 Total.xlsx workbook:

6. Run the subroutine and check that the answers appear in the correct place:

You should see these answers appear in the Q1 Total.xlsx workbook.

7. Save and close the workbooks.