Module 2 - Moving Around in Excel
Lesson 2.1 - Basic Cell Selection
Topic 2.1.4 - Moving Between Workbooks

When you have multiple workbooks open you should take care not to make assumptions about which one will be affected when you run a subroutine.  On this page you'll learn how to write code to move between workbooks, making sure that you're always in the right place.

Multiple workbooks

When you have lots of workbooks open you need to know how to move between them.

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.

Preparing the Example

Start by downloading and extracting the set of files linked to in the Files Needed section above.  Open the file called Q1 Total.xlsm as shown below:

Open Q1 total

You should find four files in the extracted folder. Open the one shown here.

 

The workbook you have opened contains a single worksheet with a table into which we want to copy some values:

Summary workbook

The values we want to copy are stored in the other three workbooks in the folder.

 

Open the VBE and insert a new module into the project attached to the Q1 Total.xlsm workbook:

Insert module

Insert a module into this project.

 

Now open the other three workbooks from the folder you extracted:

Open other files

Open the three files shown highlighted here.

 

When you return to the VBE you'll find multiple projects listed in the Project Explorer:

Multiple projects

Each open workbook is listed in the Project Explorer. Our code will be stored in the Q1 Total.xlsm workbook but needs to refer to the other workbooks.

 

Moving to Another Workbook

Now that we have the files that we need open, we'll copy the total sales for January from the appropriate file.  Start by creating a new subroutine in the module that you created earlier:

New sub

Feel free to create a different name for the subroutine.

 

You can refer to an open workbook using its name.  Start by referring to the Workbooks collection:

Workbooks

Write the word workbooks followed by an open parenthesis.

 

Next, write the name of the workbook within a set of double quotes:

Name workbook

Write the name of the workbook as it is shown in the Project Explorer, including the filename extension.

 

Close the parentheses and then apply the Activate method to the workbook:

Activate

The Activate method will take you to the specified workbook when the code is executed.

 

When this code is executed, Excel will make the January workbook the active workbook:

January

The workbook will be activated on whichever worksheet was active the last time the workbook was active.

 

You may find that the January workbook already has the Total worksheet active but you can't guarantee that this will always be the case.  Add code which selects the Total worksheet, followed by cell B7 and copy this cell:

Copy cell

Add three lines of code as shown here. These lines will affect whichever workbook is active when the code is executed.

 

We now need to return to the Q1 Total.xlsm workbook before pasting the copied value.  Add a line of code to do this:

Return to Q1

The name of the workbook to activate is Q1 Total.xlsm.

 

As there is only one worksheet in this workbook we don't need to select it.  We can simply select the appropriate cell and paste the copied value into it:

Paste

Add two lines to select cell B3 and paste the value of the copied cell into it.

 

Execute the subroutine and check that you see the results as expected:

Result

The result of running this subroutine.

 

The ThisWorkbook Property

You can refer to the workbook in which your code is stored using the ThisWorkbook property.  Not only is this quicker than typing the name of the workbook, it also means that you don't need to edit your code if you save the workbook with a different name.  Create a new subroutine which activates the Feb Sales.xlsx workbook and copies cell B7 on the Total worksheet:

Copy from Feb

The beginning of this procedure will look very similar to the previous one.

 

In order to return to the Q1 Total.xlsm workbook, refer to the ThisWorkbook property:

Thisowrkbook

ThisWorkbook refers to the workbook in which the code you're writing is stored. In this case it's the Q1 Total.xlsm workbook.

 

Apply the Activate method to the workbook, then select cell B4 and paste the copied value into it:

complete procedure

The complete procedure should look like this.

 

Execute the subroutine and check the results in Excel:

Results

At this point you should have the totals for January and February copied into the table.

 

Tidying Up the Code

Each subroutine that we've used so far uses the same instructions to select and copy the value from the Total worksheet.  It makes sense to create a separate subroutine to hold this sequence of instructions and then call it from the other procedures.  Create a new subroutine to select and copy the total sales:

New sub

You can copy and paste the three instructions from one of the other subroutines you have already created.

 

Now alter the Copy_Jan_Sales and Copy_Feb_Sales subroutines so that they each make a call to the new subroutine:

Call sub

The two subroutines should now resemble this. The comments show you where to make changes to your code.

 

Try running each of the Copy_Jan_Sales and Copy_Feb_Sales subroutines to make sure that they still work.

Create one more subroutine called Create_Q1_Totals which calls the Copy_Jan_Sales and Copy_Feb_Sales subroutines:

New sub

This subroutine simply calls the other routines which copy the appropriate values.

 

Run the Create_Q1_Totals subroutine to check that it works.

To practise moving between workbooks, create a new subroutine which will copy the total sales for March into the summary workbook:

  1. Create a new subroutine and write an instruction to activate the Mar Sales.xlsx workbook:

Sub Copy_Mar_Sales()

 

Workbooks("Mar Sales.xlsx").Activate

 

End Sub

  1. Add an instruction to call the subroutine which selects the Total worksheet and selects and copies cell B7:

Sub Copy_Mar_Sales()

 

Workbooks("Mar Sales.xlsx").Activate

 

Select_And_Copy_Total

 

End Sub

  1. Use the ThisWorkbook property to return to the summary workbook:

Sub Copy_Mar_Sales()

 

Workbooks("Mar Sales.xlsx").Activate

 

Select_And_Copy_Total

 

ThisWorkbook.Activate

 

End Sub

  1. Add instructions to select and paste the copied value into cell B5:

Sub Copy_Mar_Sales()

 

Workbooks("Mar Sales.xlsx").Activate

 

Select_And_Copy_Total

 

ThisWorkbook.Activate

 

Range("B5").Select

ActiveCell.PasteSpecial xlPasteValues

 

End Sub

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

After running the procedure you should see all of the monthly results in the table.

 
  1. Alter the Create_Q1_Totals subroutine to make a call to the Copy_Mar_Sales subroutine:

Sub Create_Q1_Totals()

 

Copy_Jan_Sales

Copy_Feb_Sales

Copy_Mar_Sales

 

End Sub

  1. Run the Create_Q1_Totals subroutine to make sure that it still works.
  2. Save the Q1 Total.xlsm workbook and then close it and the three other workbooks.  If you like, you could write a simple subroutine to perform these actions:

Sub Save_And_Close_Files()

 

ThisWorkbook.Save

 

Workbooks("Jan Sales.xlsx").Close

Workbooks("Feb Sales.xlsx").Close

Workbooks("Mar Sales.xlsx").Close

 

ThisWorkbook.Close

 

End Sub

  1. You can run the subroutine to close all of the files you have been working on.  Beware that this includes the one in which your code is stored!
This page has 0 threads Add post