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:

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:

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 a module into this project.

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

Open the three files shown highlighted here.

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

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:

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:

Write the word workbooks followed by an open parenthesis.

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

The complete procedure should look like this.

Execute the subroutine and check the results in Excel:

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:

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:

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:

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:

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

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

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

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

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

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

7. Run the Create_Q1_Totals subroutine to make sure that it still works.
8. 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:

9. 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!