Exercise 6.02
The aim of this exercise is to split a list of football matches in the 2018
FIFA World Cup into separate worksheets according to which group each match
belongs to.
Files Needed
Click here to download the
file needed for this exercise.
Instructions
- Extract and open the World Cup 2018.xlsm
workbook and look at the Fixtures worksheet:

This worksheet contains a list of matches and the name of the group to which the match belongs.
- Look at the Groups worksheet:

This worksheet contains the names of the eight groups.
- In the VBE, insert a new module and create a
subroutine called Create_Group_Sheets.
- Create a For Each loop which loops through
the list of group names on the Groups
worksheet.
- For each cell that the loop processes:
- Add a worksheet to the
Worksheets collection.
- Change the name of the new
worksheet to match the value of the cell that is
being processed.
- Move the new worksheet so
that it becomes the right-most worksheet in the
workbook.
- Copy the column headings in
cells A1:G1 of the Fixtures worksheet into the top row of
the new worksheet.
- Run the subroutine and check that you have a new worksheet
for each group:

Your workbook should now resemble this.
If you attempt to run the Create_Group_Sheets subroutine
again it will cause a run-time error due to duplicating worksheet names.
- Create a new subroutine called
Delete_Group_Sheets.
- Write a For Each loop which loops through
the Worksheets collection of the workbook.
- Add an If statement within the loop which
checks that the name of the worksheet being processed is not
equal to "Fixtures" and is not equal to
"Groups".
- Within the If statement, add an instruction
to delete the worksheet currently being processed.
You can prevent a message appearing each time a worksheet is deleted by
adding Application.DisplayAlerts = False before attempting to
delete a sheet.
- Save the workbook and then run the
Delete_Group_Sheets subroutine:

After running the subroutine you should have only the
Fixtures and Groups worksheets in the workbook.
- Create a new subroutine called
Split_Fixture_List.
- Add instructions to call the Delete_Group_Sheets
followed by the Create_Group_Sheets
subroutines.
- Create a For Each loop which loops through
cells A2:A49 on the Fixtures
worksheet.
- For each cell that is processed by the loop:
- Copy the row of data to which
the cell belongs.
- Paste the copied data at the
bottom of the list on the worksheet whose name
matches the value of the cell being processed.
- Run the subroutine and check that each worksheet receives
the correct list of fixtures:

Each of the group worksheets should contain six fixtures.
- Create a new subroutine called
Change_Column_Widths.
- Write a For Each loop which processed the
Worksheets collection in the workbook.
- Add an If statement within the loop which
checks that the name of the worksheet being processed is not
equal to "Fixtures" and is not equal to
"Groups".
- Within the If statement, add an instruction
which applies the AutoFit method to the columns
of the entire region of data beginning in cell A1
on the worksheet being processed.
- At the bottom of the Split_Fixture_List
subroutine, add an instruction which calls the
Change_Column_Widths procedure.
- Run the Split_Fixture_List subroutine and
check the results in Excel:

The new results should be more readable than the previous ones.
- Save and close the workbook.
Answer Files
Click here to
download a file containing a suggested answer.