Module 2 - Moving Around in Excel
Lesson 2.1 - Basic Cell Selection
Topic 2.1.5 - Moving vs. Referencing

So far in this lesson we've looked at how to move around in Excel by either selecting or activating ranges, worksheets and workbooks.  This part of the lesson shows you how to manipulate those objects without needing to select or activate them first.

Manipulating objects without selecting them can make your code more efficient but at the cost of making it more difficult to see what's happening when you debug it.

Files Needed

You can click here to download the set of files needed for this page.

Completed Code

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

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 file

Open this file to get started.

 

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

Total sheet

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:

New sub

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:

Select then change

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:

Single instruction

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:

Add formula

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

 

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

Results

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:

Total region

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:

Select sheet

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:

COpy no select

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:

Paste into Total

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:

Pasted value

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:

Assign value

Cell B4 will hold the total from the London worksheet.

 

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

Refer to London

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:

Copy other sheets

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:

Final result

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 file

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:

Destination

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 other workbook

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:

refer to thisworkbook

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:

Result

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 Feb Mar

Open the two workbooks shown highlighted here.

 
  1. 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:

Sub Copy_Feb_And_Mar_Totals()

 

End Sub

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

Sub Copy_Feb_And_Mar_Totals()

 

Workbooks("Feb Sales.xlsx").Worksheets("Total").Range("B7").Copy

 

End Sub

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

Sub Copy_Feb_And_Mar_Totals()

 

Workbooks("Feb Sales.xlsx").Worksheets("Total").Range("B7").Copy

 

Workbooks("Q1 Total.xlsx").Worksheets("Total").Range("B4").PasteSpecial xlPasteValues

 

End Sub

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

Sub Copy_Feb_And_Mar_Totals()

 

Workbooks("Feb Sales.xlsx").Worksheets("Total").Range("B7").Copy

 

Workbooks("Q1 Total.xlsx").Worksheets("Total").Range("B4").PasteSpecial xlPasteValues

 

Workbooks("Q1 Total.xlsx").Worksheets("Total").Range("B5").Value = _

Workbooks("Mar Sales.xlsx").Worksheets("Total").Range("B7").Value

 

End Sub

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

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

 
  1. Save and close the workbooks.
This page has 1 thread Add post
13 May 20 at 17:14

Hiya! Loving the course and trying to put it to use already.

I'm trying to use the technique in the Sub Copy_Paris_Total() with my own data and I'm having some trouble. Instead of using the worksheet name, I'm using the sheet codenames. So I simply have:

Sheet4.Range("C14", Range("C14").End(xlDown)).Copy

Sheet1.Range("N1").Paste

However, unless I have selected the sheet being referenced by the code, I get an error: Select method of Range class failed. Is this because I've used the codenames?

14 May 20 at 06:53

Hi Emma, happy to hear that you're enjoying the course!

The error you're seeing is because this line:

Sheet4.Range("C14", Range("C14").End(xlDown)).Copy

Has two references to a Range object.  You've qualified the first reference with the sheet name, but not the second.  Try this instead:

Sheet4.Range("C14", Sheet4.Range("C14").End(xlDown)).Copy

I hope that helps!

14 May 20 at 09:13

Thanks Andrew, that definitely fixed that problem but I soon came across another - it refuses to paste into the Sheet1 range saying the method doesn't work with that object...

So I had a look on Microsoft's site and can see that the Paste method is available for the Worksheets object, but not the Sheets object. Helpful!

Just thought I'd mention it in case anyone else came across this problem. I've always preferred to use the codename because I don't trust other users, haha!

Andrew G  
14 May 20 at 10:35

Hi Emma, yes I hadn't spotted that part of your question, thanks, and good use of the Help system!

You can apply the PasteSpecial method to a Range object so, in the case of your code:

Sheet1.Range("N1").PasteSpecial

I hope that helps!

Andy B  
14 May 20 at 09:47

Thanks Emma - glad you're enjoying the course!