Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Collections and Loops

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:
1. Copy the row of data to which the cell belongs.
2. 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: