Exercise 7.02

The aim of this exercise is to divide a list of films into different worksheets depending on which genre the film belongs to.

Files Needed

Click here to download the file needed for this exercise.

Instructions

1. Extract and open the Movies.xlsm workbook:

The Movies worksheet contains a list of films that we want to copy into different worksheets according the value in the Genre column.

2. Open the VBE and find and run the subroutine called Split_List_By_Genre.  Find the line which causes a run-time error:

This line will cause a run-time error because the worksheet corresponding to the name of the genre does not exist.

3. Reset the procedure then add a custom error handler to the subroutine which does the following:

• Creates a new worksheet.
• Assigns the name of the genre to the worksheet.
• Copies the column headings from the Movies worksheet into the new worksheet.
• Moves the new worksheet to the right hand side of the worksheet tabs.
• Resumes the subroutine at the line which caused the run-time error.

4. Run the subroutine and check that you end up with a separate worksheet for each genre:

Each new worksheet should contain films of a single genre.

5. Optionally, you could write a procedure to loop over the collection of worksheets and use the AutoFit method to change the column widths on each sheet.
6. Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.