Moving Around in Excel
Exercise 2.02

Exercise 2.02

The aim of this exercise is to create lists of football teams by copying team names between different worksheets.

Files Needed

Click here to download the file needed for this exercise.

Instructions

  1. Extract and open the English Football.xlsm workbook.  There are four worksheets whose name begins with 2018.  Your job is to write code which populates the list of team names in column A on each of these four worksheets:
2018 sheets

Each of the four 2018 worksheets contains a button with a subroutine attached to it.

  1. There are four worksheets whose name begins with 2017.  These sheets contain the names of the teams that you need to copy:
2017 Teams

Each of the 2017 worksheets contains the names of the teams to copy.  Some teams will be promoted or relegated to a different league in 2018.

  1. Open the VBE and find the Get_2018_Prem_Teams subroutine in Module1:
Code

Each subroutine in the module contains comments which tell you which team names to copy from the 2017 worksheets.

  1. Using the comments provided, write code in each of the four subroutines to copy the correct teams from each 2017 worksheet into the correct 2018 worksheet.  As a reminder, the basic code to copy and then paste the value of a cell is shown below:

Range("A1").Copy

 

Range("A2").PasteSpecial xlPasteValues

  1. After writing the code, click the button on each of the 2018 worksheets and check that you have copied the correct teams into each sheet.
  2. The answer file linked to below uses a variety of techniques to achieve the aim of the exercise.  Compare your code with the answer file.
  3. Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.

This page has 0 threads Add post