The aim of this exercise is to divide a list of films into different worksheets depending on which genre the film belongs to.
Click here to download the file needed for this exercise.
- 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.
- 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.
- 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.
Don't forget to add an Exit Sub statement above the error handling section!
- 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.
- 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.
- Save and close the workbook.
Click here to download a file containing a suggested answer.